SQL query to pivot descriptive fields

Question

I have the following input and expected output for a table. I tried using self joins but it didn't work. Is there a way I can do this using the PIVOT function?

Expected output

Thanks in advance

Answer 1

This solution is only applicable for static number of rows per country. First use ROW_NUMBER() with PARTITION BY for retrieving country wise serialize cities. Then use this as subquery and use MAX() with case condition of outer sub query for retrieving final result.

-- SQL SERVER (v2017)
SELECT t.country
     , MAX(CASE WHEN t.row_num = 1 THEN city END) city1
     , MAX(CASE WHEN t.row_num = 2 THEN city END) city2
     , MAX(CASE WHEN t.row_num = 3 THEN city END) city3
FROM (SELECT *
           , ROW_NUMBER() OVER (PARTITION BY country ORDER BY country) row_num
      FROM test) t
GROUP BY t.country 

Please use this url https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=8812f794d4c9ea64bf99e9640800da77

Answer 2

Possible approaches are conditional aggregtaion and PIVOT (even if the number of cities for each country is different). You simply need to number the rows and group them appropriately :

Table:

SELECT *
INTO Data
FROM (VALUES
   ('US', 'Dallas'),
   ('US', 'Atlanta'),
   ('US', 'Los Angeles'),
   ('US', 'Chicago'),
   ('UK', 'London'),
   ('UK', 'Manchester'),
   ('UK', 'Birmingham')
) v (COUNTRY, CITY)

Statement with conditional aggregation:

SELECT 
   COUNTRY,
   CITY1 = MAX(CASE WHEN RN % 3 = 0 THEN CITY END),
   CITY2 = MAX(CASE WHEN RN % 3 = 1 THEN CITY END),
   CITY3 = MAX(CASE WHEN RN % 3 = 2 THEN CITY END)
FROM (
   SELECT *, ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY CITY) - 1 AS RN
   FROM Data
) t 
GROUP BY COUNTRY, RN / 3

Statement with PIVOT:

SELECT COUNTRY, CITY1, CITY2, CITY3
FROM (
   SELECT 
      (ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY CITY) - 1) / 3 AS ID,
      *, 
      CASE (ROW_NUMBER() OVER (PARTITION BY COUNTRY ORDER BY CITY) - 1) % 3
         WHEN 0 THEN 'CITY1'
         WHEN 1 THEN 'CITY2'
         WHEN 2 THEN 'CITY3'
      END AS CITYNAME
   FROM Data
) t
PIVOT (  
   MAX(CITY)  
   FOR CITYNAME IN ([CITY1], [CITY2], [CITY3])  
) p

Result:

COUNTRY CITY1      CITY2   CITY3
-------------------------------------
UK      Birmingham London  Manchester
US      Atlanta    Chicago Dallas
US      Los Angeles