SQL query to pivot descriptive fields-CodePudding

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

CodePudding user response：

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

CodePudding user response：

Possible approaches are conditional aggrregtaion and PIVOT. You need to number the rows and group them appropriately if you have different count of cities for each country:

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