CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
user3729390
user3729390

Reputation: 21

Multiple rows to columns (pivot)

I Have a table like below,

ClassId     ClassStDate             Gender Rate1       Rate2       Rate3       Rate4
1           2014-08-01          M      30          40          50          60
1           2014-08-01          F      26          36          46          56

I want my query to output like below (multiple columns instead of multiple rows)

classid     ClassStDate     Rate1_M     Rate1_F     Rate2_M     Rate2_F     Rate3_M     Rate3_F     Rate4_M     Rate4_F
1           2014-08-01      30          26          40          36          50          46          60          56

I can achieve the desired result with multiple pivots in a multiple CTEs and joining them together. http://sqlfiddle.com/#!3/efef0/2

Is there anyway to achieve this with a single pivot?

Upvotes: 1

Views: 366

Answers (2)

Anon
Anon

Reputation: 10908

WITH t AS (
  SELECT
    ClassId,
    ClassStDate,
    RateNum+'_'+Gender AS RateNumGender,
    Rate
  FROM classes
  UNPIVOT(Rate FOR RateNum IN (Rate1,Rate2,Rate3,Rate4)) p
)
SELECT *
FROM t
PIVOT(SUM(Rate) FOR RateNumGender IN (Rate1_M,Rate1_F,Rate2_M,Rate2_F,Rate3_M,Rate3_F,Rate4_M,Rate4_F) )p

Fiddle

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

There's nothing stopping you from doing an old school MAX/CASE

SELECT 

   classid     ,
   ClassStDate,
   MAX(case Gender when  'M' THEN Rate1  ELSE NULL END) Rate1_M  ,
   MAX(case Gender when  'F' THEN Rate1  ELSE NULL END) Rate1_F  ,
   MAX(case Gender when  'M' THEN Rate2  ELSE NULL END) Rate2_M  ,
   MAX(case Gender when  'F' THEN Rate2  ELSE NULL END) Rate2_F  ,
   MAX(case Gender when  'M' THEN Rate3  ELSE NULL END) Rate3_M  ,
   MAX(case Gender when  'F' THEN Rate3  ELSE NULL END) Rate3_F  ,
   MAX(case Gender when  'M' THEN Rate4  ELSE NULL END) Rate4_M  ,
   MAX(case Gender when  'F' THEN Rate4  ELSE NULL END) Rate4_F  

FROM 

   classes
GROUP BY   
   classid     ,
   ClassStDate

Fiddle

Upvotes: 1

Related Questions