CrossTabs SQL Server

问题:

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.

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

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

EDIT

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

回答1:

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.)

回答2:

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
    ,isnull([N6880], 0) [N6880]
    ,isnull([N6881], 0) [N6881]
    ,isnull([N7860], 0) [N7860]
    ,TotalHrs
FROM (
    SELECT ENAME
        ,JOBNUM
        ,LABORHRS
        ,sum(LABORHRS) OVER (PARTITION BY ENAME) TotalHrs
    FROM @CombinedLabor
    ) a
PIVOT(sum(LABORHRS) FOR JOBNUM IN (
            [N6880]
            ,[N6881]
            ,[N7860]
            )) AS PivotTable
ORDER BY ENAME

回答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