CrossTabs SQL Server

Question

Hello Everyone, I am wondering how can I create Cross Tabs queries with SQL Server 2008. I have fields for Job Numbers and Employees and I want to show that how many hours an Employee has worked on the specific job.

For sample, this is what I want. link text

I tried PIVOT but that didn't work for. Later I want to export this data to EXCEL with C#.

UPDATE:

Okay here is the query.

SELECT Ename, JobNum, LaborHrs
FROM CombinedLabor

which will return the list of employee name, with job nums and labor hrs.

The existing table is as follows:

ENAME   JOBNUM    LABORHRS
abc      N6880       8.0
abc      N6880       2.5  
xyz      N7860       9.5
...      ...         ...

So I want something like this

        N6880 N7860 ... Total Hrs
abc     10.5   0.0     ... 10.5
xyz      0.0   9.5      ... 9.5

Answer 1

Cross tab queries in SQL Server are done using PIVOT. If you couldn't get PIVOT to work, post here the query you tried and the error you got.

Answer 2

Okay here is the query.

SELECT Ename, JobNum,LaborHrs FROM CombinedLabor which will return the list of employee name, with job nums and labor hrs.

The existing table is as follows:

ENAME   JOBNUM    LABORHRS
abc      N6880       8.0
abc      N6880       2.5  
xyz      N7860       9.5
...      ...         ...

So I want something like this

        N6880 N7860 ... Total Hrs
abc     10.5   0.0     ... 10.5
xyz     0.0   9.5      ... 9.5

Answer 3

DECLARE @CombinedLabor TABLE
  ( 
   ENAME varchar(50)
  ,JOBNUM varchar(20)
  ,LABORHRS decimal(10,2)
  )

INSERT  INTO @CombinedLabor
        ( ENAME, JOBNUM, LABORHRS )
VALUES  ( 'abc', 'N6880', 8.0 )
,       ( 'abc', 'N6880', 2.5 )
,       ( 'abc', 'N6881', 5.2 )
,       ( 'xyz', 'N7860', 9.5 ) ;  

SELECT
   ENAME
  ,SUM(CASE JOBNUM WHEN 'N6880' THEN LABORHRS ELSE 0 END) AS [N6880]
  ,SUM(CASE JOBNUM WHEN 'N6881' THEN LABORHRS ELSE 0 END) AS [N6881]
  ,SUM(CASE JOBNUM WHEN 'N7860' THEN LABORHRS ELSE 0 END) AS [N7860]
  ,SUM(LABORHRS) AS [PersonTotal]
FROM   @CombinedLabor
GROUP BY ENAME

And the result

Answer 4

I would suggest you try doing your cross tab manipulation in C#:

http://code.google.com/p/pivot-tools/

I suspect you'll find it easier than fiddling with a dynamic pivot. (This is my project, so let me know if you have any questions.)

---

Before doing that, you might want to retrieve "Total Hrs" in advance, like so:

SELECT Ename, JobNum, SUM(LaborHrs) LaborHrs, TotalHrs
FROM CombinedLabor
INNER JOIN (
SELECT Ename, SUM(LaborHrs) TotalHrs
FROM CombinedLabor
GROUP BY EName
) Totals
ON CombinedLabor.Ename = Totals.Ename
GROUP BY CombinedLabor.Ename, JobNum, TotalHrs

(A suggestion: Different employees could have the same name, so you should have an id column to join on.)