Forum Stats

  • 3,853,198 Users
  • 2,264,190 Discussions
  • 7,905,285 Comments

Discussions

Ambiguity in the usage of select statement in "PIVOT" clause - PART II

Vysakh Suresh - 3035408
Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
edited Dec 22, 2015 10:45PM in SQL & PL/SQL

Dear All,

Kindly advise a solution for receiving the following output using the PIVOT clause.


Created with reference to :

NB : Kindly take to consideration that the tables shown here are sample data tables. Real life table contains nearly 100 menus under the MENU table and thousands of users, each having access to various menus, under USERS table.


MENU

 

MENU_ID

MENU_NAME

A

1

B

2

C

3

D

4

E

5

F

6

G

7

H

8

I

9

J

10

K

11

L

12

M

13

N

14

O

15

P

16

Q

17

R

18

S

19

T

20

U

21

V

22

W

23

X

24

Y

25

Z

26


USER1

 

USER_ID

USER_NAME

MENU_ID

U1

USER 1

A

U1

USER 1

F

U2

USER 2

A

U2

USER 2

F

U3

USER 3

B

U3

USER 3

G

U4

USER 4

B

U4

USER 4

G

U5

USER 5

C

U5

USER 5

H

U6

USER 6

C

U7

USER 7

D

U8

USER 8

D

U9

USER 9

E

U10

USER 10

E


OUTPUT

  <span style="color: #000000; font-family: Times New Roman; font-

Tagged:
AndrewSayerVysakh Suresh - 3035408

Best Answer

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond
    edited Dec 22, 2015 6:17AM Answer ✓

    The FAQ answers this:

    However, you still seem to be completely missing the point.

    SQL Projection requires that the column names of the resulting SQL statement are known BEFORE any data is fetched.  You cannot have a single SQL statement that can give an unknown number of columns based on the data, because at the point that the SQL Projection is determined, no data has been fetched.

    The only way to do a dynamic number of columns is to generate dynamic SQL statements, and then the only way to handle the results of dynamic SQL statement is with dynamic code... and then you end up going down the route of writing appallingly bad code that is full of bugs and slow to perform and will likely fail, as well as it being hard to maintain.

    Dynamic pivoting is something best suited to report writing tools which, by their nature, query the data first, then format that data based on what has been fetched.  They are the best tool for the job.

    Vysakh Suresh - 3035408
«1

Answers

  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Dec 22, 2015 2:42AM

    Your other post suggests you are aware of the syntax so what have you got so far? What's preventing it from being correct?

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Dec 22, 2015 2:42AM

    If you want your output as a relational structure then you need to explicitly define column in the IN clause of your PIVOT. If you can live with an XML output then you can go for a sub query in the IN clause of the PIVOT statement. And you cant get a Flag like Y or N in a pivot it must be a aggregate function. In your case its COUNT. You could write a query on top of it to convert it to Y/N flag.

    Here is a basic query

    select * 
      from user1
     pivot (count(*) for menu_id in ('A' as A,'B' as B,'C' as C,'D' as D,'E' as E,'F' as F,'G' as G,'H' as H,'I' as I,'J' as J,'K' as K,'L' as L,'M' as M,'N' as N,'O' as O,'P' as P,'Q' as Q,'R' as R,'S' as S,'T' as T,'U' as U,'V' as V,'W' as W,'X' as X,'Y' as Y,'Z' as Z))
    
    Vysakh Suresh - 3035408
  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited Dec 22, 2015 3:19AM

    Dear Karthik / Andrew,


    Thanks for your advise..


    Actually tried the Y/N flag advised by Karthik as shown in the code below,


    select * from

    (

         select a.user_id "USER_ID",a.user_name "USER_NAME",

         b.menu_id "MENU_ID", 'y' as "MENU_PRESENT_YN"

         from user1 a, menu b where a.menu_id=b.menu_id

    )

    pivot

    (

    MAX("MENU_PRESENT_YN") FOR "MENU_ID" IN

    (

    'A' as A,'B' as B,'C' as C,'D' as D,'E' as E,'F' as F,'G' as G,'H' as H,

    'I' as I,'J' as J,'K' as K,'L' as L,'M' as M,'N' as N,'O' as O,'P' as P,

    'Q' as Q,'R' as R,'S' as S,'T' as T,'U' as U,'V' as V,'W' as W,'X' as X,

    'Y' as Y,'Z' as Z

    )

    );


    The output I received was as below,

                     

    pastedImage_27.png


    Even though we forget the fact that 'N' is not coming.. there are 2 issues more for us rite,

    Issue 1) We need a solution by which the stress of writing the complete static data,

    IN

    (

    'A' as A,'B' as B,'C' as C,'D' as D,'E' as E,'F' as F,'G' as G,'H' as H,

    'I' as I,'J' as J,'K' as K,'L' as L,'M' as M,'N' as N,'O' as O,'P' as P,

    'Q' as Q,'R' as R,'S' as S,'T' as T,'U' as U,'V' as V,'W' as W,'X' as X,

    'Y' as Y,'Z' as Z

    )

    can be avoided.


    Issue 2) We only require the menu_ids present in the user1 to come as columns in the output.


    Will we have any other alternative than to use the PIVOT clause..


    Kindly advise.

    Thanks and Regards,

    Vysakh Suresh

  • Karthick2003
    Karthick2003 Member Posts: 13,711 Bronze Badge
    edited Dec 22, 2015 3:46AM

    For both your question, first you need to digest the fact that the projection of SELECT need to be defined during parsing of the SQL. Only option you have is to dynamically construct the SELECT and execute it. I don't think that would be a good idea.

    Vysakh Suresh - 3035408
  • AndrewSayer
    AndrewSayer Member Posts: 13,007 Gold Crown
    edited Dec 22, 2015 3:48AM

    First step is to pivot by the menu_name rather than menu_id.

    select * from

    (

        select a.user_id "USER_ID",a.user_name "USER_NAME",

        b.menu_name , 'y' as "MENU_PRESENT_YN"

        from user1 a, menu b where a.menu_id=b.menu_id

    )

    pivot

    (

    MAX("MENU_PRESENT_YN") FOR "MENU_NAME" IN

    (

    1,2,3,4,5,6,7,8 -- for briefness

    )

    )

    USER_ID  USER_NAME  1 2 3 4 5 6 7 8

    _________ __________ _ _ _ _ _ _ _ _

    U10      USER10            y

    U6        USER6          y

    U1        USER1      y        y

    U8        USER8            y

    U4        USER4        y        y

    U9        USER9              y

    U2        USER2      y        y

    U3        USER3        y        y

    U5        USER5          y        y

    U7        USER7            y

    Next we need to fill out all the 'N's. You could NVL each column after the pivot, but I like to use the partition outer join (partition outer join in oracle 10g )

    select * from

    (

        select a.user_id "USER_ID",a.user_name "USER_NAME",

        b.menu_name, NVL2(b.menu_id,'Y','N') as "MENU_PRESENT_YN"

        from user1 a

        left join menu b partition by (menu_name)

          on a.menu_id=b.menu_id

    )

    pivot

    (

    MAX("MENU_PRESENT_YN") FOR menu_name IN

    (1,2,3,4,5,6,7,8

    )

    )

    USER_ID  USER_NAME  1 2 3 4 5 6 7 8

    _________ __________ _ _ _ _ _ _ _ _

    U1        USER1      Y N N N N Y N N

    U10      USER10    N N N N Y N N N

    U2        USER2      Y N N N N Y N N

    U3        USER3      N Y N N N N Y N

    U4        USER4      N Y N N N N Y N

    U5        USER5      N N Y N N N N Y

    U6        USER6      N N Y N N N N N

    U7        USER7      N N N Y N N N N

    U8        USER8      N N N Y N N N N

    U9        USER9      N N N N Y N N N

    The dynamic nature is a little tricky as it depends how you are calling the sql. If you use sqlplus you could write the list into a substitution variable and then put that into the query:

    col menu_names new_value menu_names

    select listagg(''''||menu_name||''' "'||menu_name||'"',',') within group (order by menu_name) menu_names from (select distinct menu_name from menu);

    MENU_NAMES

    ________________________________________________________________________________________________________

    '1' "1",'10' "10",'11' "11",'12' "12",'13' "13",'14' "14",'15' "15",'16' "16",'17' "17",'18' "18",'19' "...

    select * from

    (

        select a.user_id "USER_ID",a.user_name "USER_NAME",

        b.menu_name, NVL2(b.menu_id,'Y','N') as "MENU_PRESENT_YN"

        from user1 a

        left join menu b partition by (menu_name)

          on a.menu_id=b.menu_id

    )

    pivot

    (

    MAX("MENU_PRESENT_YN") FOR menu_name IN

    (&menu_names

    )

    )

    USER_ID  USER_NAME  1 1 1 1 1 1 1 1 1 1 1 2 2 2 2 2 2 2 2 3 4 5 6 7 8 9

    _________ __________ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

    U1        USER1      Y N N N N N N N N N N N N N N N N N N N N N Y N N N

    U10      USER10    N N N N N N N N N N N N N N N N N N N N N Y N N N N

    U2        USER2      Y N N N N N N N N N N N N N N N N N N N N N Y N N N

    U3        USER3      N N N N N N N N N N N Y N N N N N N N N N N N Y N N

    U4        USER4      N N N N N N N N N N N Y N N N N N N N N N N N Y N N

    U5        USER5      N N N N N N N N N N N N N N N N N N N Y N N N N Y N

    U6        USER6      N N N N N N N N N N N N N N N N N N N Y N N N N N N

    U7        USER7      N N N N N N N N N N N N N N N N N N N N Y N N N N N

    U8        USER8      N N N N N N N N N N N N N N N N N N N N Y N N N N N

    U9        USER9      N N N N N N N N N N N N N N N N N N N N N Y N N N N

    Vysakh Suresh - 3035408
  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond
    edited Dec 22, 2015 3:49AM

    To add to Karthick's reply about SQL Projection... please read:

    Vysakh Suresh - 3035408
  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited Dec 22, 2015 5:44AM

    Dear Karthik, Andrew and Blu Shadow,

    Thank you very much for the advises..

    I had one more [email protected] would you please advise me on "how to convert rows into columns in sql without using pivot" and "not having the need to enter the column names manually".. will this be possible under Oracle?

    Thanks and Regards,

    Vysakh Suresh

  • BluShadow
    BluShadow Member, Moderator Posts: 42,326 Red Diamond
    edited Dec 22, 2015 6:17AM Answer ✓

    The FAQ answers this:

    However, you still seem to be completely missing the point.

    SQL Projection requires that the column names of the resulting SQL statement are known BEFORE any data is fetched.  You cannot have a single SQL statement that can give an unknown number of columns based on the data, because at the point that the SQL Projection is determined, no data has been fetched.

    The only way to do a dynamic number of columns is to generate dynamic SQL statements, and then the only way to handle the results of dynamic SQL statement is with dynamic code... and then you end up going down the route of writing appallingly bad code that is full of bugs and slow to perform and will likely fail, as well as it being hard to maintain.

    Dynamic pivoting is something best suited to report writing tools which, by their nature, query the data first, then format that data based on what has been fetched.  They are the best tool for the job.

    Vysakh Suresh - 3035408
  • Vysakh Suresh - 3035408
    Vysakh Suresh - 3035408 Member Posts: 1,240 Gold Badge
    edited Dec 22, 2015 6:42AM

    true it is Blu Shadow..

    worked around at last with cross tab reports under Crystal Reports tool.. it worked!! I'm getting the data now inputting the query,

    select a.user_id "USER_ID",a.user_name "USER_NAME",

      b.menu_id "MENU_ID", 'y' as "MENU_PRESENT_YN"

      from user1 a, menu b where a.menu_id=b.menu_id


    and assigning the column "USER_ID" & "USER_NAME" vertically, "MENU_ID" horizontally and the "MENU_PRESENT_YN" as content in the area.

    Thanks and Regards,

    Vysakh Suresh

  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Dec 22, 2015 8:47AM

    Just playing around


    with

    menu as

    (select 'A' menu_id,'1' menu_name from dual union all

    select 'B','2' from dual union all

    select 'C','3' from dual union all

    select 'D','4' from dual union all

    select 'E','5' from dual union all

    select 'F','6' from dual union all

    select 'G','7' from dual union all

    select 'H','8' from dual union all

    select 'I','9' from dual union all

    select 'J','10' from dual union all

    select 'K','11' from dual union all

    select 'L','12' from dual union all

    select 'M','13' from dual union all

    select 'N','14' from dual union all

    select 'O','15' from dual union all

    select 'P','16' from dual union all

    select 'Q','17' from dual union all

    select 'R','18' from dual union all

    select 'S','19' from dual union all

    select 'T','20' from dual union all

    select 'U','21' from dual union all

    select 'V','22' from dual union all

    select 'W','23' from dual union all

    select 'X','24' from dual union all

    select 'Y','25' from dual union all

    select 'Z','26' from dual

    ),

    user1 as

    (select 'U1' user_id,'USER1' user_name,'A' menu_id from dual union all

    select 'U1','USER1','F' from dual union all

    select 'U2','USER2','A' from dual union all

    select 'U2','USER2','F' from dual union all

    select 'U3','USER3','B' from dual union all

    select 'U3','USER3','G' from dual union all

    select 'U4','USER4','B' from dual union all

    select 'U4','USER4','G' from dual union all

    select 'U5','USER5','C' from dual union all

    select 'U5','USER5','H' from dual union all

    select 'U6','USER6','C' from dual union all

    select 'U7','USER7','D' from dual union all

    select 'U8','USER8','D' from dual union all

    select 'U9','USER9','E' from dual union all

    select 'U10','USER10','E' from dual

    ),

    flagger(user_id,user_name,menu_list,pos,pos_list,step) as

    (select u.user_id,u.user_name,null menu_list,0,listagg(to_char(instr(m.menu_list,u.menu_id)) || ',') within group (order by instr(m.menu_list,u.menu_id)) pos,0

       from user1 u,

            (select listagg(menu_id,',') within group (order by menu_id) menu_list

               from (select menu_id,menu_name,1 rw

                       from menu

                    )

              group by rw

            ) m

      group by u.user_id,u.user_name

    union all

    select user_id,user_name,

            menu_list || case when substr(pos_list,1,instr(pos_list,',') - 1) = 1

                              then 'Y,'

                              else rpad('N,',2 * (to_number(substr(pos_list,1,instr(pos_list,',') - 1)) - pos - 1),'N,') || 'Y,'

                         end,

            to_number(substr(pos_list,1,instr(pos_list,',') - 1)),

            substr(pos_list,instr(pos_list,',') + 1),

            step + 1

       from flagger

      where pos_list is not null

    )

    select user_id,user_name,menu_flags

      from (select user_id,user_name,rtrim(rpad(menu_list,list_length,'N,'),',') menu_flags

              from (select user_id,user_name,menu_list,list_length,

                    row_number() over (partition by user_id,user_name order by length(menu_list) desc nulls last) rn

                      from flagger f,

                           (select 2 * count(*) list_length

                              from menu

                           )

                   )

             where rn = 1

            union all

            select null,'HEADER',listagg(menu_id,',') within group (order by menu_id) menu_list

              from (select menu_id,menu_name,1 rw

                      from menu

                   )

             group by rw

           )

    order by to_number(substr(user_id,2)) nulls first

    USER_IDUSER_NAMEMENU_FLAGS
    -HEADERA,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z
    U1USER1Y,N,N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
    U2USER2Y,N,N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
    U3USER3N,N,Y,N,N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N
    U4USER4N,N,Y,N,N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N
    U5USER5N,N,N,N,Y,N,N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N
    U6USER6N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
    U7USER7N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
    U8USER8N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
    U9USER9N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N
    U10USER10N,N,N,N,N,N,N,N,Y,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N,N

    Regards

    Etbin

    AndrewSayerVysakh Suresh - 3035408Vysakh Suresh - 3035408
This discussion has been closed.