question

robbieperotti avatar image
robbieperotti asked

Transpose SQL Data from List to a row

I have Data Country | Company NG | Lagos NG | Abuja NG | Port Harcourt GH | Accra GH | Medina ZM | Lusaka ZM | Kitwe I am able to return; NG | GH | ZM Lagos | Accra | Lusaka Abuja | Medina |Kitwe Port Harcourt I Need to return; NG | Lagos | Abuja | Port Harcourt GH | Accra | Medina ZM | Lusaka| Kitwe Could someone give me some guidance please?
tsqlpivot
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
JohnM avatar image
JohnM answered
You can use STUFF to get the result set you need. CREATE TABLE #x (country CHAR(2),company VARCHAR(25)) INSERT #x (country, company) SELECT 'NG', 'Lagos' UNION SELECT 'NG', 'Abuja' UNION SELECT 'NG', 'Port Harcourt' UNION SELECT 'GH', 'Accra' UNION SELECT 'GH', 'Medina' UNION SELECT 'ZM', 'Lusaka' UNION SELECT 'ZM', 'Kitwe' SELECT DISTINCT country , STUFF( (SELECT ',' + company FROM #x x WHERE x.country = z.country FOR XML PATH('')),1,1,'') AS y FROM #x z Reference on STUFF: https://docs.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql Hope that helps!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.