Transpose Rows to columns

Database

Transpose Rows to columns

Hi,

I'm having data like this

daypart_desc     Net   date            start_time       season_year  month    qtr  pgm_name

** M-Su 6A - 2A NBC 2013-06-29 2013-06-24 40 2012 2012 Jun  2013 2Q CROSSING LINES-SAT

** M-Su 6A - 2A NBC 2013-06-29 2013-06-24 40 2012 2012 Jun  2013 2Q DO NO HARM

** M-Su 7P - 2A NBC 2013-06-29 2013-06-24 40 2012 2012 Jun  2013 2Q CROSSING LINES-SAT

etc, I need to have 'M-Su 6A - 2A' as one column, '** M-Su 7P - 2A ' should be an another column, and it's not defined. It'll change according to user's input.

My output should be

Net   date            start_time       season_year  month    qtr pgm_name                      ** M-Su 6A - 2A   ** M-Su 7P - 2A

 NBC 2013-06-29 2013-06-24 40 2012 2012 Jun  2013 2Q CROSSING LINES-SAT              1                           1

 NBC 2013-06-29 2013-06-24 40 2012 2012 Jun  2013 2Q DO NO HARM                            1                            0

Please help me on this, regardless of the logic i use, i should arrive this.I can have temp table, what ever it is, should make this work.

1 REPLY
N/A

Re: Transpose Rows to columns

Something similar to this should work for you based on your info:

SEL net,DATE,start_time,season_year,MONTH,qtr,prm_name,

SUM(CASE WHEN daypart_desc = 'M-SU 6A' THEN 1 ELSE 0 END) M-SU6A2A,

SUM(CASE WHEN daypart_desc = 'M-SU 7P' THEN 1 ELSE 0 END) M-SU7P2A

FROM Yourtab GROUP BY 1,2,3,4,5,6,7;

Rglass