SQL How to transform ROWS to COLUMNS and COLUMNS TO ROWS? db2

Question

I have an SQL query which works fine in DB2 My result is

SERVICE   IN   OUT   INPROGRESS

ADSL      1     5      10
VOIP      15    12     11
IPTV      20    14     17

Now I want to transform it to be like this:

CLASS       ADSL   VOIP   IPTV

IN           1     5      10
OUT          15    12     11
INPROGRESS   20    14     17

Although it seems long my SQL is very simple but I never had transformed this. If someone knows I will be thankful.

My SQL is

select  distinct 'ADSL' as SERVICE,

(select count(*) as In from ticket 
where 
(class='C1'  and
(servicesinfault='25'))),

(select count(*) as Out from ticket
where 
(class='C2' and
(servicesinfault='25'))),


(select count(*) as In_progress from ticket
where 
(class='C3' and
(servicesinfault='25')))

from ticket where servicesinfault = '25'

union all

select  distinct 'VoIP',

(select count(*) from ticket 
where
(class='C1'  and
(servicesinfault='26'))),

(select count(*) from ticket
where
(class='C2'  and
(servicesinfault='26'))),


(select count(*) from ticket
where 
(class='C3'  and
(servicesinfault='26')))

from ticket where servicesinfault = '26'

union all

select  distinct 'IPTV',

(select count(*) from ticket 

where 
(class='C1'  and
(ticket.servicesinfault='27'))),

(select count(*) from ticket
where 
(class='C2'  and
(servicesinfault='27'))),

(select count(*) from ticket
where 
(class='C3'  and
(servicesinfault='27')))

from ticket where servicesinfault = '27'

Which RBDMS (SQL-Server, Oracle, Mysql etc.)? There are many duplicates of this question for most of them. — Bridge, Jun 01 '12 at 11:04
There isn't a DB2 Pivot equivalent that I know, I'd suggest taking the approach suggested in the link in Ben's answer below. — Bridge, Jun 01 '12 at 11:19

tibtof · Accepted Answer · 2012-06-01T11:50:40.927

Just as a remark, your result should like:

CLASS       ADSL   VOIP   IPTV

IN           1     15     20
OUT          5     12     14
INPROGRESS   10    11     17

The pivoted version should be something like:

select distinct 'In' as CLASS,

                (select count(*) as 'ADSL'
                   from ticket
                  where (class = 'C1' and (servicesinfault = '25'))),

                (select count(*) as 'VoIP'
                   from ticket
                  where (class = 'C1' and (servicesinfault = '26'))),

                (select count(*) as 'IPTV'
                   from ticket
                  where (class = 'C1' and (servicesinfault = '27')))

  from ticket
 where class = 'C1'

union all

select distinct 'Out',

                (select count(*)
                   from ticket
                  where (class = 'C2' and (servicesinfault = '25'))),

                (select count(*)
                   from ticket
                  where (class = 'C2' and (servicesinfault = '26'))),

                (select count(*)
                   from ticket
                  where (class = 'C2' and (servicesinfault = '27')))

  from ticket
 where class = 'C2'

union all

select distinct 'InProgress',

                (select count(*)
                   from ticket

                  where (class = 'C3' and (ticket.servicesinfault = '25'))),

                (select count(*)
                   from ticket
                  where (class = 'C3' and (servicesinfault = '26'))),

                (select count(*)
                   from ticket
                  where (class = 'C3' and (servicesinfault = '27')))

  from ticket
 where class = 'C3'

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

The word you are looking for is "PIVOT".

If your DBMS doesn't offer one, you can use the "poor man's pivot".

Look at usr's answer on this question:

Poor Man's SQL Pivot. List Questions as Columns and Answers per User in one row

edited May 23 '17 at 11:56

Community

1
1

answered Jun 01 '12 at 11:15

Ben

34,935
6
74
113

1

@Dejan Take a look at the link which offers a way without using `PIVOT`. He posted it before you clarified which RBDMS you were using ;-) – Bridge Jun 01 '12 at 11:27
1

@Dejan, not last time I checked, but the latest version may. – Ben Jun 01 '12 at 11:42

SQL How to transform ROWS to COLUMNS and COLUMNS TO ROWS? db2

2 Answers2