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

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

Answer 2

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