I would like to create a query from a single table with the following columns.
我想使用以下列从单个表创建查询。
SEQNO is a unique key
SEQNO是一个唯一的密钥
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:-
查询应以SUBJOBID和日期范围的格式输出: -
**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 ?
我一直在为此进行数据透视查询。但我似乎无处可去。有人可以帮忙吗?
1 个解决方案
#1
This actually can be done pretty easily with a PIVOT function. Since the other answer doesn't show the code associated with how to perform it, here are two ways to PIVOT the data.
这实际上可以通过PIVOT功能轻松完成。由于其他答案未显示与如何执行相关的代码,因此以下是两种PIVOT数据的方法。
First is with a Static Pivot. A static pivot is when you know the data ahead of time to turn 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
请参阅SQL Fiddle with Demo
The second way is by using a Dynamic PIVOT to identify at run-time the values to turn to columns.
第二种方法是使用Dynamic PIVOT在运行时识别要转换为列的值。
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)
请参阅SQL Fiddle with Demo
Both will produce the same results. The Dynamic works great when you do not know the values ahead of time to convert to columns.
两者都会产生相同的结果。当您不知道提前转换为列的值时,Dynamic会很有效。
#1
This actually can be done pretty easily with a PIVOT function. Since the other answer doesn't show the code associated with how to perform it, here are two ways to PIVOT the data.
这实际上可以通过PIVOT功能轻松完成。由于其他答案未显示与如何执行相关的代码,因此以下是两种PIVOT数据的方法。
First is with a Static Pivot. A static pivot is when you know the data ahead of time to turn 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
请参阅SQL Fiddle with Demo
The second way is by using a Dynamic PIVOT to identify at run-time the values to turn to columns.
第二种方法是使用Dynamic PIVOT在运行时识别要转换为列的值。
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)
请参阅SQL Fiddle with Demo
Both will produce the same results. The Dynamic works great when you do not know the values ahead of time to convert to columns.
两者都会产生相同的结果。当您不知道提前转换为列的值时,Dynamic会很有效。