Forum Stats

  • 3,854,206 Users
  • 2,264,340 Discussions
  • 7,905,609 Comments

Discussions

Categories

Pivot Unpivot Requirement

Iniyavan
Iniyavan Member Posts: 157
edited Jul 16, 2018 6:34AM in SQL & PL/SQL

Hi friends,

Could you please help me to complete this query?

I have a table like this:

(I know it is not a good design. :-))

Table NameColumn NameRow Serial Number Value
Table_OneColumn_One1101
Table_OneColumn_Two1Arun
Table_OneColumn_Three101-Jan-2018
Table_OneColumn_Four1Red
Table_OneColumn_One2102
Table_OneColumn_Two2Bala
Table_OneColumn_Three202-Jan-2018
Table_OneColumn_Four2Blue
Table_TwoColumn_One1201
Table_TwoColumn_Two1Chitra
Table_TwoColumn_One2202
Table_TwoColumn_Two2David
Table_TwoColumn_One3203
Table_TwoColumn_Two3Elango

And the requirement is to write a query to get Table Name as input and return output like...

For "Table_One" input, the output should be

Serial NoColumn_OneColumn_TwoColumn_ThreeColumn_Four
1101Arun01-Jan-2018Red
2102Bala02-Jan-2018Blue

For "Table_Two" input, the output should be

Serial NoColumn_OneColumn_Two
1201Chitra
2202David
3203Elango

SQL query or any PL/SQL approach is fine.

Thanks in advance!

Tagged:
· Share on TwitterShare on Facebook

Best Answer

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jul 16, 2018 5:05AM Answer ✓

    SQL> variable tn varchar2(32);

    SQL> exec :tn := 'Table_One';

    PL/SQL procedure successfully completed.

    SQL> with t (table_name, col, sn, val) as (

    select 'Table_One',    'Column_One',    1,    '101'            from dual union all

    select 'Table_One',    'Column_Two',    1,    'Arun'           from dual union all

    select 'Table_One',    'Column_Three',    1,    '01-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    1,    'Red'          from dual union all

    select 'Table_One',    'Column_One',    2,    '102'            from dual union all

    select 'Table_One',    'Column_Two',    2,    'Bala'           from dual union all

    select 'Table_One',    'Column_Three',    2,    '02-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    2,    'Blue'         from dual union all

    select 'Table_Two',    'Column_One',    1,    '201'            from dual union all

    select 'Table_Two',    'Column_Two',    1,    'Chitra'         from dual union all

    select 'Table_Two',    'Column_One',    2,    '202'            from dual union all

    select 'Table_Two',    'Column_Two',    2,    'David'          from dual union all

    select 'Table_Two',    'Column_One',    3,    '203'            from dual union all

    select 'Table_Two',    'Column_Two',    3,    'Elango'         from dual

    )

    select * from (select * from t where table_name = :tn)

    pivot

    (

      min(val) for (col) in (

                               'Column_One' as col_1

                             , 'Column_Two' as col_2

                             , 'Column_Three' as col_3

                             , 'Column_Four' as col_4

                            )

    )

    order by sn;

    TABLE_NAM         SN COL_1       COL_2       COL_3       COL_4    

    --------- ---------- ----------- ----------- ----------- -----------

    Table_One          1 101         Arun        01-Jan-2018 Red       

    Table_One          2 102         Bala        02-Jan-2018 Blue      

    SQL> variable tn varchar2(32);

    SQL> exec :tn := 'Table_Two';

    PL/SQL procedure successfully completed.

    SQL> with t (table_name, col, sn, val) as (

    select 'Table_One',    'Column_One',    1,    '101'            from dual union all

    select 'Table_One',    'Column_Two',    1,    'Arun'           from dual union all

    select 'Table_One',    'Column_Three',    1,    '01-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    1,    'Red'          from dual union all

    select 'Table_One',    'Column_One',    2,    '102'            from dual union all

    select 'Table_One',    'Column_Two',    2,    'Bala'           from dual union all

    select 'Table_One',    'Column_Three',    2,    '02-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    2,    'Blue'         from dual union all

    select 'Table_Two',    'Column_One',    1,    '201'            from dual union all

    select 'Table_Two',    'Column_Two',    1,    'Chitra'         from dual union all

    select 'Table_Two',    'Column_One',    2,    '202'            from dual union all

    select 'Table_Two',    'Column_Two',    2,    'David'          from dual union all

    select 'Table_Two',    'Column_One',    3,    '203'            from dual union all

    select 'Table_Two',    'Column_Two',    3,    'Elango'         from dual

    )

    select * from (select * from t where table_name = :tn)

    pivot

    (

      min(val) for (col) in (

                               'Column_One' as col_1

                             , 'Column_Two' as col_2

                             , 'Column_Three' as col_3

                             , 'Column_Four' as col_4

                            )

    )

    order by sn;

    TABLE_NAM         SN COL_1       COL_2       COL_3       COL_4    

    --------- ---------- ----------- ----------- ----------- -----------

    Table_Two          1 201         Chitra                            

    Table_Two          2 202         David                             

    Table_Two          3 203         Elango

    · Share on TwitterShare on Facebook

Answers

  • mNem
    mNem Member Posts: 1,380 Gold Trophy
    edited Jul 16, 2018 5:05AM Answer ✓

    SQL> variable tn varchar2(32);

    SQL> exec :tn := 'Table_One';

    PL/SQL procedure successfully completed.

    SQL> with t (table_name, col, sn, val) as (

    select 'Table_One',    'Column_One',    1,    '101'            from dual union all

    select 'Table_One',    'Column_Two',    1,    'Arun'           from dual union all

    select 'Table_One',    'Column_Three',    1,    '01-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    1,    'Red'          from dual union all

    select 'Table_One',    'Column_One',    2,    '102'            from dual union all

    select 'Table_One',    'Column_Two',    2,    'Bala'           from dual union all

    select 'Table_One',    'Column_Three',    2,    '02-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    2,    'Blue'         from dual union all

    select 'Table_Two',    'Column_One',    1,    '201'            from dual union all

    select 'Table_Two',    'Column_Two',    1,    'Chitra'         from dual union all

    select 'Table_Two',    'Column_One',    2,    '202'            from dual union all

    select 'Table_Two',    'Column_Two',    2,    'David'          from dual union all

    select 'Table_Two',    'Column_One',    3,    '203'            from dual union all

    select 'Table_Two',    'Column_Two',    3,    'Elango'         from dual

    )

    select * from (select * from t where table_name = :tn)

    pivot

    (

      min(val) for (col) in (

                               'Column_One' as col_1

                             , 'Column_Two' as col_2

                             , 'Column_Three' as col_3

                             , 'Column_Four' as col_4

                            )

    )

    order by sn;

    TABLE_NAM         SN COL_1       COL_2       COL_3       COL_4    

    --------- ---------- ----------- ----------- ----------- -----------

    Table_One          1 101         Arun        01-Jan-2018 Red       

    Table_One          2 102         Bala        02-Jan-2018 Blue      

    SQL> variable tn varchar2(32);

    SQL> exec :tn := 'Table_Two';

    PL/SQL procedure successfully completed.

    SQL> with t (table_name, col, sn, val) as (

    select 'Table_One',    'Column_One',    1,    '101'            from dual union all

    select 'Table_One',    'Column_Two',    1,    'Arun'           from dual union all

    select 'Table_One',    'Column_Three',    1,    '01-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    1,    'Red'          from dual union all

    select 'Table_One',    'Column_One',    2,    '102'            from dual union all

    select 'Table_One',    'Column_Two',    2,    'Bala'           from dual union all

    select 'Table_One',    'Column_Three',    2,    '02-Jan-2018'  from dual union all

    select 'Table_One',    'Column_Four',    2,    'Blue'         from dual union all

    select 'Table_Two',    'Column_One',    1,    '201'            from dual union all

    select 'Table_Two',    'Column_Two',    1,    'Chitra'         from dual union all

    select 'Table_Two',    'Column_One',    2,    '202'            from dual union all

    select 'Table_Two',    'Column_Two',    2,    'David'          from dual union all

    select 'Table_Two',    'Column_One',    3,    '203'            from dual union all

    select 'Table_Two',    'Column_Two',    3,    'Elango'         from dual

    )

    select * from (select * from t where table_name = :tn)

    pivot

    (

      min(val) for (col) in (

                               'Column_One' as col_1

                             , 'Column_Two' as col_2

                             , 'Column_Three' as col_3

                             , 'Column_Four' as col_4

                            )

    )

    order by sn;

    TABLE_NAM         SN COL_1       COL_2       COL_3       COL_4    

    --------- ---------- ----------- ----------- ----------- -----------

    Table_Two          1 201         Chitra                            

    Table_Two          2 202         David                             

    Table_Two          3 203         Elango

    · Share on TwitterShare on Facebook
  • Iniyavan
    Iniyavan Member Posts: 157
    edited Jul 16, 2018 6:33AM

    Thanks, mNem.

    I have modified your query to fit for my requirement.

    Thanks.

    · Share on TwitterShare on Facebook
This discussion has been closed.