CODEWITHSUNDEEP
sql-server-2005
KIA
KIA

Reputation: 21

Cross Tab or Pivot query in SQL 2005

Can someone please help me with a cross tab/pivot query in SQL 2005

Given Data looks like

EmpId   OrgId  DayCt  Cost

1       20     15     100

2       20     36     300

3       40     25     200

4       40     10     50

Result to be like:

EmpId       OrgId 20          OrgId 40  

         DayCt  Cost          DayCt  Cost


1         15     100

2         36     300

3                             25     200

4                             10     50

EmpId in 1st Col and then Org Ids in the next col. But under every OrgId, I want DayCt & Cost also to be included as sub columns. Not sure if this is doable. Please help.

Upvotes: 2

Views: 245

Answers (1)

Martin Smith
Martin Smith

Reputation: 453327

There is no such thing as sub columns this seems like something that should be done in your application/reporting tool.

This is about the closest you can get in SQL

;WITH T(EmpId,OrgId,DayCt,Cost) AS
(
select 1, 20,  15,  100 UNION ALL
select 2, 20,  36,  300 UNION ALL
select 3, 40,  25,  200 UNION ALL
select 4, 40,  10,  50
)
SELECT EmpId,
       MAX(CASE WHEN OrgId =20 THEN DayCt END) AS [OrgId 20 DayCt],
       MAX(CASE WHEN OrgId =20 THEN Cost END) AS [OrgId 20 Cost],
       MAX(CASE WHEN OrgId =40 THEN DayCt END) AS [OrgId 40 DayCt],
       MAX(CASE WHEN OrgId =40 THEN Cost END) AS [OrgId 40 Cost]
FROM T
GROUP BY EmpId

Returns

EmpId       OrgId 20 DayCt OrgId 20 Cost OrgId 40 DayCt OrgId 40 Cost
----------- -------------- ------------- -------------- -------------
1           15             100           NULL           NULL
2           36             300           NULL           NULL
3           NULL           NULL          25             200
4           NULL           NULL          10             50

Upvotes: 1

Related Questions