Transpose rows to columns

1

Hello

I have a table with a series of 4 data points for each country, I have been asked to add each data point as a column so that there is only one row per country. I have researched a little online and I have tried to use the PIVOT function but I have had a few issues with this.

I believe there are too many rows to list the contents and do a union.

Am I going on the right track or is there something easier I should be trying?

My script so far:

"SELECT * FROM
(
SELECT country_code, un_country_property_id, value
FROM dbo.un_country_property_value) A
PIVOT
(MAX(value) for un_country_property_id IN ([Country Code], [Currency Code],
[Dialling Code], [Market])) AS B"

Errors:
Error converting data type nvarchar to int.
The incorrect value "Country Code" is supplied in the PIVOT operator.

Thanks for your help in advance!

2

Try a cross-tab instead. I'm not exactly sure what the code values are, but this should get you very close at least:

SELECT country_code, 
    MAX(CASE WHEN un_country_property_id = 'Country Code' THEN value END) AS Country_Code,
    MAX(CASE WHEN un_country_property_id = 'Currency Code' THEN value END) AS Currency_Code,
    MAX(CASE WHEN un_country_property_id = 'Dialing Code' THEN value END) AS Dialing_Code,
    MAX(CASE WHEN un_country_property_id = 'Market' THEN value END) AS Market
FROM dbo.un_country_property_value
GROUP BY country_code
3

Thanks Scott, that has worked really well!

My only problem now (and this is something I often have) is that I also want to join this to several other tables to give region and sub-region but I don't want to group by those fields. The below is my code, how would I add "CR.value AS Region, CSR.value AS [Sub-region]" to it without grouping? I tried to put the below into a different select statement to make it a temp table however I can't seem to join the dbo.un_country_region and dbo.un_country_sub_region tables to it.

Thanks so much for your thoughts!

SELECT C.country_code, CC.value As Country,
MAX(CASE WHEN CPV.un_country_property_id = 4 THEN CPV.value END) AS Country_Code,
MAX(CASE WHEN un_country_property_id = 3 THEN CPV.value END) AS Currency_Code,
MAX(CASE WHEN un_country_property_id = 2 THEN CPV.value END) AS Dialing_Code,
MAX(CASE WHEN un_country_property_id = 1 THEN CPV.value END) AS Market
FROM dbo.un_country_property_value CPV
LEFT JOIN dbo.un_country_country CC ON CC.code =CPV.country_code
LEFT JOIN dbo.un_country C ON CC.code=C.country_code
LEFT JOIN dbo.un_country_property CP ON CP.id=CPV.un_country_property_id
LEFT JOIN dbo.un_country_region CR ON CR.code=C.region_code
LEFT JOIN dbo.un_country_sub_region CSR ON CSR.code=C.sub_region_code
GROUP BY CC.value, C.country_code

4

I can't tell. Some names, like Country_Code, overlap, and it's not clear how you want to lookup the other codes.

But in general you make the MAX() query a derived table, then use an outer query to select from it and join to the other tables.

1 Like
5

Thanks Scott, you've been more than helpful as always :wink: