Convert the data, split rows into columns in SQL

Question

Could you please help me in displaying the table output in below format.

Table data

Required output

please include the DBMS and also, are these the only 5 countries in countryname or could be more? — Jayvee, May 17 '21 at 16:50
Hi Jayvee, I am using SQL server 2014, and the countries could be more or less. — MRR, May 17 '21 at 16:53
Thank you Jayvee and Larnu. Your inputs helped me to achieve the required output. — MRR, May 17 '21 at 17:59

score 1 · Answer 1 · answered May 17 '21 at 17:11

1

If you have the exhaustive list of countries you have to include all of them in the query, if the list is dynamic then it's a problem and the only way would be by dynamic sql. Please try this and let us know:

SELECT
  ObjectName,
  CZ,HQ,RO,BR,SK
FROM (
  SELECT * FROM yourtable
) AS X
PIVOT
(
  SUM(counts)
  FOR CountryName
  IN (
    [CZ],[HQ],[RO],[BR],[SK]
  )
) AS PivotX

answered May 17 '21 at 17:11

Jayvee

10,670
3
29
40

Thanks Jayvee, it is working. But I am looking for a dynamic query. – MRR May 17 '21 at 17:22

MRR · Accepted Answer · 2021-05-18T15:37:44.040

1

DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.CountryName) FROM #temptest c FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')

set @query = 'SELECT ObjectName, ' + @cols + ' from ( select ObjectName,CountryName,counts from #temptest ) x pivot ( max(counts) for CountryName in (' + @cols + ') ) p '

execute(@query)

edited May 18 '21 at 15:37

answered May 17 '21 at 18:01

MRR

83
3
9

Convert the data, split rows into columns in SQL

2 Answers2