Pivot Table – How to Handle Multiple Dynamic Rows

pivot

How do i get this?

to look like this:

my create and insert code :

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:

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

Best Answer

Simply get MAX value of Towns and Cities grouped by Country.

SELECT Country, 
       MAX(town1) Town1, MAX(City1) City1, 
       MAX(town2) Town2, MAX(City2) City2, 
       MAX(town3) Town3, MAX(City3) City3, 
       MAX(town4) Town4, MAX(City4) City4, 
       MAX(town5) Town5, MAX(City5) 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
GO

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 

Warning: Null value is eliminated by an aggregate or other SET operation.

dbfiddle here

Related Solutions

How to Pivot Rows into Multiple Columns in Oracle

There are a few ways that you can perform this data transformation. You have access to the PIVOT function then that will be the easiest, but if not then you can use an aggregate function and a CASE.

Aggregate /Case version:

select personid,
  max(case when optionid = 'A' then 1 else 0 end) OptionA,
  max(case when optionid = 'B' then 1 else 0 end) OptionB,
  max(case when optionid = 'C' then 1 else 0 end) OptionC
from PersonOptions
group by personid
order by personid;

See SQL Fiddle with Demo

Static Pivot:

select *
from
(
  select personid, optionid
  from PersonOptions
) src
pivot
(
  count(optionid)
  for optionid in ('A' as OptionA, 'B' OptionB, 'C' OptionC)
) piv
order by personid

See SQL Fiddle with Demo

Dynamic Version:

The two versions above work great if you have a known number of values, but if your values are unknown, then you will want to implement dynamic sql and in Oracle you can use a procedure:

CREATE OR REPLACE procedure dynamic_pivot_po(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select personid ';

    begin
        for x in (select distinct OptionID from PersonOptions order by 1)
        loop
            sql_query := sql_query ||
                ' , min(case when OptionID = '''||x.OptionID||''' then 1 else null end) as Option_'||x.OptionID;

                dbms_output.put_line(sql_query);
        end loop;

        sql_query := sql_query || ' from PersonOptions group by personid order by personid';
        dbms_output.put_line(sql_query);

        open p_cursor for sql_query;
    end;
/

Then you return the results, you will use:

variable x refcursor
exec dynamic_pivot_po(:x)
print x

The results are the same with all versions:

| PERSONID | OPTIONA | OPTIONB | OPTIONC |
------------------------------------------
|        1 |       1 |       1 |       0 |
|        2 |       0 |       0 |       1 |
|        3 |       0 |       1 |       0 |
|        4 |       1 |       0 |       1 |

MySQL – Pivot Table with Dynamic Rows

You could do something along the following lines:

select 
    s1.grade, 
    s1.firstname as s1_fname, 
    s2.firstname as s2_fname, 
    s3.firstname as s3_fname,
    s4.firstname as s4_fname
from #grades as s1
-- Self join the table for second student
left outer join grades as s2
    on s1.grade = s2.grade 
    and s2.student_num = 2
-- Self join the table for third student
left outer join grades as s3
    on s1.grade = s3.grade 
    and s3.student_num = 3
-- Self join the table for forth student
left outer join grades as s4
    on s1.grade = s4.grade 
    and s4.student_num = 4
where s1.student_num = 1
order by s1.grade;

This joins the table to itself once for each set of columns (s1, s2, etc.). You could write some dynamic SQL to generate this based on the maximum student_num or just run select max(student_num) and make sure you have enough columns to catch all the values. (If you don't you'll need to add another join like the others).

Edit: This assumes that the grade and student_num columns combine to create a unique key. If you have duplicates (e.g. two students with grade 1, student_num 1), then you'll get duplicate rows in your results.

Best Answer

Related Solutions

How to Pivot Rows into Multiple Columns in Oracle

MySQL – Pivot Table with Dynamic Rows

Related Question