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?
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!
18 People are following this question.
