sqlsql-serverpivottransformstuff

Convert the data, split rows into columns in SQL

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

Table data enter image description here

Required output enter image description here

Solution

  • 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)