How to roll up data from one table with SQL Server 2005
I would like to create a query from one table with the following columns.
SEQNO - 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 request should be output in this format using SUBJOBID and 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'm going to run summary queries for this. But I don't seem to go anywhere. Can anyone please help?
This can be done quite easily with a function PIVOT
. Since the other answer doesn't show the code associated with executing it, here are two ways for the PIVOT
data.
First is the Static Bar . A static pivot is when you know the data ahead of time to go into columns.
select *
from
(
select name, id, convert(char(5), dt, 101) dt, jobid, amount
from test
) x
pivot
(
sum(amount)
for dt in ([01/02], [02/02], [03/02], [04/05], [05/05])
)p
order by jobid, name
The second way is to use Dynamic PIVOT to identify at run time the values to refer to the columns.
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(5), dt, 101))
from test
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT name, id, ' + @cols + ', jobid from
(
select name, id, convert(char(5), dt, 101) dt, jobid, amount
from test
) x
pivot
(
sum(amount)
for dt in (' + @cols + ')
) p
order by jobid, name'
execute(@query)
Both will give the same results. Dynamic works great when you don't know in advance the values to convert to columns.
a source to share