How to Pivot data from one table with SQL server 2005

Question

Hello,

I would like to create a query from a single table with the following columns.

SEQNO is a unique key

Name   ID   Amount   Date          JOBID       SEQNO
Mark    9    200     1/2/09         1001         1
Peter   3    300     1/2/09         1001         2
Steve   1    200     2/2/09         1001         3
Mark    9    200     3/2/09         1001         4
Peter   3    300     4/2/09         1001         5
Steve   1    200     5/2/09         1001         6
Hally   1    200     5/2/09         1002         7

The query should output in this format by SUBJOBID and a date range:-

**NAME      ID      1/2       2/2     3/2     4/2     5/2      JOBID**<br>
Mark        9       200       NULL    200     NULL    NULL     1001   
Peter       3       300       NULL    NULL    300     NULL     1001   
Steve       1       NULL      200     NULL    NULL    200      1001

I have been going over pivot queries for this. But I don't seem to get anywhere. Could some one help ?

Answer 1

I am having a problem identical to yours. Let me know how you resolved this.

Answer 2

The PIVOT command sure sounds cool, huh? In reality, it is a total pain in the keester to use and is not all that flexible in that you have to know in advance what all the values are going to be in the column that you want to pivot on.

The good news, is that I wrote a stored procedure called pivot_query that generates and runs the PIVOT statement on your data. So using your example data:

Id         Name                 Amount     MyDate     JobId      SeqNo      
---------- -------------------- ---------- ---------- ---------- ---------- 
9          Mark                 200        1/2/09     1001       1          
3          Peter                300        1/2/09     1001       2          
1          Steve                200        2/2/09     1001       3          
9          Mark                 200        3/2/09     1001       4          
3          Peter                300        4/2/09     1001       5          
1          Steve                200        5/2/09     1001       6          
1          Hally                200        5/2/09     1002       7          

exec dbo.pivot_query @mySql, 'Name,Id', 'MyDate','sum(Amount)' ...

Name                 Id         1/2/09     2/2/09     3/2/09     4/2/09     5/2/09     
-------------------- ---------- ---------- ---------- ---------- ---------- ---------- 
Hally                1          NULL       NULL       NULL       NULL       200        
Mark                 9          200        NULL       200        NULL       NULL       
Peter                3          300        NULL       NULL       300        NULL       
Steve                1          NULL       200        NULL       NULL       200

The source code for the procedure is here. The header comment shows the syntax, and after running it you can do a select * from ##log_table to see some log output from it.

Hope that helps you out!

Full text of the "intended" post is here. SO has a problem posting it ...

Ron