CODEWITHSUNDEEP
sqlpivotunpivot
user3530952
user3530952

Reputation: 21

Pivot on multiple dynamic rows

How do i get this set of data that i created using pivot.

Country town1       City1       town2       City2       town3   City3       town4   City4       town5   City5
India   NULL        NULL        NULL        NULL        NULL    NULL        townin  India Town  NULL    NULL
India   NULL        NULL        NULL        NULL        Del     New Delhi   NULL    NULL        NULL    NULL
India   NULL        NULL        Bang        Bangalore   NULL    NULL        NULL    NULL        NULL    NULL
India   hyder       Hyderabad   NULL        NULL        NULL    NULL        NULL    NULL        NULL    NULL
UK      NULL        NULL        NULL        NULL        United  Manchester  NULL    NULL        NULL    NULL
UK      NULL        NULL        sunderland  Birmingham  NULL    NULL        NULL    NULL        NULL    NULL
UK      chelsea     London      NULL        NULL        NULL    NULL        NULL    NULL        NULL    NULL
USA     NULL        NULL        NULL        NULL        Dally   Dallas      NULL    NULL        NULL    NULL
USA     NULL        NULL        houst       Houston     NULL    NULL        NULL    NULL        NULL    NULL
USA     New Jersy   New York    NULL        NULL        NULL    NULL        NULL    NULL        NULL    NULL

to look like this like this data which is what is required for me to display:

Country town1       City1       town2       City2       town3   City3       town4   City4       town5   City5
India   hyder       Hyderabad   Bang        Bangalore   Del     New Delhi   townin  India Town  NULL    NULL
UK      chelsea     London      sunderland  Birmingham  United  Manchester  NULL    NULL        NULL    NULL
USA     New Jersy   New York    houst       Houston     Dally   Dallas      NULL    NULL        NULL    NULL

my create and insert code below was used :

Create Table Countries
(
    Country nvarchar(50),
    Town nvarchar(50),
    City nvarchar(50)
)
GO

Insert into Countries values ('USA','New Jersy','New York')
Insert into Countries values ('USA','houst','Houston')
Insert into Countries values ('USA','Dally','Dallas')

Insert into Countries values ('India','hyder','Hyderabad')
Insert into Countries values ('India','Bang','Bangalore')
Insert into Countries values ('India','Del','New Delhi')
Insert into Countries values ('India','townin','India Town')

Insert into Countries values ('UK','chelsea','London')
Insert into Countries values ('UK','sunderland','Birmingham')
Insert into Countries values ('UK','United','Manchester')

Current pivot code based on a few blogs and videos:

Select Country, town1, City1, town2, City2, town3, City3, town4, City4, town5, City5
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence,
Town, 'Town'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence2 
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3, City4, City5) 
) Piv
pivot
(
max(Town)
for ColumnSequence2 in (town1, town2, town3, town4, town5) 
) Piv
group by Country, town1, City1, town2, City2, town3, City3, town4, City4, 
town5, City5

Upvotes: 2

Views: 50

Answers (3)

Fahmi
Fahmi

Reputation: 37473

Try this code:

with cte as
(Select Country, min(town1) as town1, min(City1) as City1, min(town2)as town2, min(City2)as City2, min(town3)as town3, 
min(City3) as City3, min(town4) as town4, min(City4) as City4, min(town5) as town5, min(City5) as City5
From
(
Select Country, City, 'City'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence,
Town, 'Town'+ cast(row_number() over(partition by Country order by Country) as varchar(10)) ColumnSequence2 
from Countries
) Temp
pivot
(
max(City)
for ColumnSequence in (City1, City2, City3, City4, City5) 
) Piv
pivot
(
max(Town)
for ColumnSequence2 in (town1, town2, town3, town4, town5) 
) Piv
group by Country)
Select t.Country, COALESCE(t.town1,d.town1), COALESCE(t.City1,d.city1), COALESCE(t.town2,d.town2), 
COALESCE(t.City2,d.city2), COALESCE(t.town3,d.town3), COALESCE(t.City3,d.city3), COALESCE(t.town4,d.town4), 
COALESCE(t.City4,d.city4), COALESCE(t.town5,d.town5), COALESCE(t.City5,d.city5)
from cte d iNNER JOIN CTE t ON d.country = t.country

Upvotes: 0

SqlZim
SqlZim

Reputation: 38033

using row_number() and conditional aggregation to pivot your data: 

select 
    Country
  , Town_01 = max(case when rn = 1 then Town end)
  , City_01 = max(case when rn = 1 then City end)
  , Town_02 = max(case when rn = 2 then Town end)
  , City_02 = max(case when rn = 2 then City end)
  , Town_03 = max(case when rn = 3 then Town end)
  , City_03 = max(case when rn = 3 then City end)
  , Town_04 = max(case when rn = 4 then Town end)
  , City_04 = max(case when rn = 4 then City end)
from (
  select *
    , rn = row_number() over (partition by Country order by Town) 
  from Countries
  ) as t
group by Country

rextester demo: http://rextester.com/GYV10206

returns: 

+---------+---------+-----------+------------+------------+-----------+------------+---------+------------+
| Country | Town_01 |  City_01  |  Town_02   |  City_02   |  Town_03  |  City_03   | Town_04 |  City_04   |
+---------+---------+-----------+------------+------------+-----------+------------+---------+------------+
| India   | Bang    | Bangalore | Del        | New Delhi  | hyder     | Hyderabad  | townin  | India Town |
| UK      | chelsea | London    | sunderland | Birmingham | United    | Manchester | NULL    | NULL       |
| USA     | Dally   | Dallas    | houst      | Houston    | New Jersy | New York   | NULL    | NULL       |
+---------+---------+-----------+------------+------------+-----------+------------+---------+------------+

Upvotes: 1

Jacob H
Jacob H

Reputation: 2505

COALESCE is the function you are looking for. It will return the first non-null value. More info here:

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql

Upvotes: 0

Related Questions