Help, Pivot SQL

[JeanneZ]

I am doing a report for a book store. We have data like:


STORE_ID SUBJECT SUB_DETAIL NUMBERS
1001 CD CD_TITLE1 2
1001 CD CD_TITLE2 4
1001 CD CD_TITLE3 1
1001 DVD DVD_TITLE1 7
1001 DVD DVD_TITLE2 5
1001 DVD DVD_TITLE3 3
1001 DVD DVD_TITLE4 2
1001 BOOK BOOK_TITLE1 6
1001 BOOK BOOK_TITLE2 2
1001 OTHERS OTHERS1 9
1001 OTHERS OTHERS2 1
1001 OTHERS OTHERS3 8
1001 OTHERS OTHERS4 2
1001 OTHERS OTHERS5 3

We want to display the data like:


STORE_ID CD CD_NUM DVD DVD_NUM BOOK BOOK_NUM OTHERS OTHERS_NUM
1001 CD_TITLE1 2 DVD_TITLE1 7 BOOK_TITLE1 6 OTHERS1 9
1001 CD_TITLE2 4 DVD_TITLE2 5 BOOK_TITLE2 2 OTHERS2 1
1001 CD_TITLE3 1 DVD_TITLE3 3 OTHERS3 8
1001 DVD_TITLE4 2 OTHERS4 2
1001 OTHERS5 3

Thanks a lot for any help!

Jeanne

 

[SantaMufasa]

Jeanne,

If I had this assignment, I would use PL/SQL arrays (one each for each product class) to lay out and print the report. Are you familiar with use of PL/SQL arrays? If not, is there someone amongst your colleagues that could lay out a PL/SQL program block for you to do as you need?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Dagon]

I'm not sure how you are tying "CD_TITLE1" and "DVD_TITLE1" together other than the fact that they have a "1" at the end. I presume that, in reality, they have proper names and don't conveniently end in 1,2,3 etc. If that is the case, you probably need something like a sequence number column:

STORE_ID SUBJECT SUB_DETAIL NUMBERS STOCK_SEQ
1001 CD CD_TITLE1 2 1
1001 CD CD_TITLE2 4 2
1001 CD CD_TITLE3 1 3
1001 DVD DVD_TITLE1 7 1
1001 DVD DVD_TITLE2 5 2
1001 DVD DVD_TITLE3 3 3
1001 DVD DVD_TITLE4 2 4
1001 BOOK BOOK_TITLE1 6 1
1001 BOOK BOOK_TITLE2 2 2
1001 OTHERS OTHERS1 9 1
1001 OTHERS OTHERS2 1 2
1001 OTHERS OTHERS3 8 3
1001 OTHERS OTHERS4 2 4
1001 OTHERS OTHERS5 3 5

Using that, you could then write a query such as:

SELECT store_id,
stock_seq,
max(case when subject = 'CD' then sub_detail else null end) as CD,
max(case when subject = 'CD' then NUMBERS else null end) as CD_NUM,
max(case when subject = 'DVD' then sub_detail else null end) as DVD,
max(case when subject = 'DVD' then NUMBERS else null end) as CD_NUM,
max(case when subject = 'BOOK' then sub_detail else null end) as BOOK,
max(case when subject = 'BOOK' then NUMBERS else null end) as BOOK_NUM,
max(case when subject = 'OTHERS' then sub_detail else null end) as OTHERS,
max(case when subject = 'OTHERS' then NUMBERS else null end) as OTHERS_NUM
FROM STORE
GROUP BY
stock_seq,
substr(sub_detail,length(sub_detail),1)

 

[JeanneZ]

Thanks a lot for your help. It works now.

 

[JeanneZ]

Dagon,

I am sorry. The table data I list is a sample. In real table, the title1, title2, ... are real title name. So there is no 1, 2, 3, ... So the SQL you gave to me will not work.

Please help!

Thanks.

Jeanne

 

[Dagon]

No, I said it probably wouldn't. That's why I suggested introducing a new column (although I forgot to change my SQL properly).

If you've got a version of Oracle which supports OLAP functions, you could create the column I suggested in the SQL using rownumber:

SELECT store_id,
order_seq,
max(case when subject = 'CD' then sub_detail else null end) as CD,
max(case when subject = 'CD' then NUMBERS else null end) as CD_NUM,
max(case when subject = 'DVD' then sub_detail else null end) as DVD,
max(case when subject = 'DVD' then NUMBERS else null end) as CD_NUM,
max(case when subject = 'BOOK' then sub_detail else null end) as BOOK,
max(case when subject = 'BOOK' then NUMBERS else null end) as BOOK_NUM,
max(case when subject = 'OTHERS' then sub_detail else null end) as OTHERS,
max(case when subject = 'OTHERS' then NUMBERS else null end) as OTHERS_NUM
FROM
(select s.*,
row_number() over (partition by store_id order by sub_detail) as order_seq
from store s
where subject = 'CD'
union all
select s.*,
row_number() over (partition by store_id order by sub_detail) as order_seq
from store s
where subject = 'DVD'
union all
select s.*,
row_number() over (partition by store_id order by sub_detail) as order_seq
from store s
where subject = 'BOOK'
union all
select s.*,
row_number() over (partition by store_id order by sub_detail) as order_seq
from store s
where subject = 'OTHERS') x
GROUP BY
store_id,
order_seq

 

[stefanhei]

A generic solution could look like this:

SELECT coalesc(x.store_id,y.store_id),
       cd, cd_num, dvd, dvd_num, 
       book, book_num, others, others_num
  FROM (SELECT coalesc(t1.store_id,t2.store_id) store_id,
               t1.sub_detail cd, t1.numbers cd_num,
               t2.sub_detail dvd, t2.numbers dvd_num,
               rownum r
          FROM (SELECT rownum rn, t.* 
                  FROM store
                 WHERE subject = 'CD') t1
          FULL OUTER JOIN
               (SELECT rownum rn, t.*
                  FROM store
                 WHERE subject = 'DVD') t2
            ON t1.rn = t2.rn 
           AND t1.store_id = t2.store_id) x
  FULL OUTER JOIN
       (SELECT coalesc(t1.store_id,t2.store_id) store_id,
               t1.sub_detail book, t1.numbers book_num,
               t2.sub_detail other, t2.numbers other_num,
               rownum r
          FROM (SELECT rownum rn, t.* 
                  FROM store
                 WHERE subject = 'BOOK') t1
          FULL OUTER JOIN
               (SELECT rownum rn, t.*
                  FROM store
                 WHERE subject = 'OTHER') t2
            ON t1.rn = t2.rn 
           AND t1.store_id = t2.store_id) y
    ON x.r = y.r 
   AND x.store_id = y.store_id

Stefan

 

[Dagon]

You could use rownum in my solution as well:

SELECT store_id,
order_seq,
max(case when subject = 'CD' then sub_detail else null end) as CD,
max(case when subject = 'CD' then NUMBERS else null end) as CD_NUM,
max(case when subject = 'DVD' then sub_detail else null end) as DVD,
max(case when subject = 'DVD' then NUMBERS else null end) as CD_NUM,
max(case when subject = 'BOOK' then sub_detail else null end) as BOOK,
max(case when subject = 'BOOK' then NUMBERS else null end) as BOOK_NUM,
max(case when subject = 'OTHERS' then sub_detail else null end) as OTHERS,
max(case when subject = 'OTHERS' then NUMBERS else null end) as OTHERS_NUM
FROM
(select s.*,
rownum as order_seq
from store s
where subject = 'CD'
union all
select s.*,
rownum as order_seq
from store s
where subject = 'DVD'
union all
select s.*,
rownum as order_seq
from store s
where subject = 'BOOK'
union all
select s.*,
rownum as order_seq
from store s
where subject = 'OTHERS') x
GROUP BY
store_id,
order_seq

 

[stefanhei]

Dagon, your first solution looks elegant and I would like to add the last tinkering on it:

SELECT store_id,
       order_seq, 
      max(case when subject = 'CD' then sub_detail else null end) as CD,
      max(case when subject = 'CD' then NUMBERS else null end) as CD_NUM,
      max(case when subject = 'DVD' then sub_detail else null end) as DVD,
      max(case when subject = 'DVD' then NUMBERS else null end) as CD_NUM,
      max(case when subject = 'BOOK' then sub_detail else null end) as BOOK,
      max(case when subject = 'BOOK' then NUMBERS else null end) as BOOK_NUM,
      max(case when subject = 'OTHERS' then sub_detail else null end) as OTHERS,
      max(case when subject = 'OTHERS' then NUMBERS else null end) as OTHERS_NUM
FROM 
(select s.*,
        row_number() over (partition by store_id, subject order by sub_detail) as order_seq
        from store s)

should do the trick too

Stefan