日期列上的 SQL 数据透视表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13977241/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Pivot on dates column?
提问by Simon Unsworth
I'm fairly new to SQL but believe me I have searched for help before posting this.
我对 SQL 相当陌生，但相信我，我在发布之前已经搜索过帮助。
I have a query which returns a list of people assigned to jobs, also the jobs have varying length and people who are assigned to those jobs are working different lengths.
我有一个查询，它返回分配给工作的人员列表，工作的长度也各不相同，分配给这些工作的人员的工作长度也不同。
What I am trying to do is convert what is a list of similar records with the only variable changing is the date, and some how pivot this data so that the dates become column headings and the rows represent a BOOL yes/no.
我想要做的是转换什么是类似记录的列表，唯一的变量是日期，以及一些如何旋转这些数据，以便日期成为列标题，而行表示 BOOL 是/否。
This is the data I'm getting back currently. JSON encoded
这是我目前正在取回的数据。JSON 编码
{"results":[{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-27"},{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-26"},{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-25"},{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-24"}]}
{"results":[{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-27"},{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-26"},{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-25"},{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","date":"2013-03-24"}]}
and what I would like to get back is:
我想回来的是：
{"results":[{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","2013-03-27":"YES","2013-03-26":"YES","2013-03-25":"YES","2013-03-24":"YES"}]}
{"results":[{"role":"Vision Supervisor","familyname":"Unsworth","givenname":"Simon","skill":"10","level":"Telegenic Staff","id":"664","2013-03-27":"YES","2013-03-26":"YES","2013-03-25":"YES","2013-03-24":"YES"}]}
I'm sure this is some kind of PIVOT query but I cant get it to work.
我确定这是某种 PIVOT 查询，但我无法让它工作。
Thanks
谢谢
回答by Taryn
If you are going to be running this query in SQL Server, then you can use the PIVOTfunction:
如果要在 SQL Server 中运行此查询，则可以使用以下PIVOT函数：
select *
from
(
select role, familyname, givenname, skill,
level, id, date, 'Y' flag
from yourtable
) src
pivot
(
max(flag)
for date in ([2013-03-27], [2013-03-26],
[2013-03-25], [2013-03-24])
) piv
Or you can use an aggregate function and a CASEstatement:
或者您可以使用聚合函数和CASE语句：
select role, familyname, givenname, skill,
level, id,
max(case when date = '2013-03-27' then flag end) '2013-03-27',
max(case when date = '2013-03-26' then flag end) '2013-03-26',
max(case when date = '2013-03-25' then flag end) '2013-03-25',
max(case when date = '2013-03-24' then flag end) '2013-03-24'
from
(
select role, familyname, givenname, skill,
level, id, date, 'Y' flag
from yourtable
) src
group by role, familyname, givenname, skill,
level, id
Both give the result:
两者都给出了结果：
| ROLE | FAMILYNAME | GIVENNAME | SKILL | LEVEL | ID | 2013-03-27 | 2013-03-26 | 2013-03-25 | 2013-03-24 |
----------------------------------------------------------------------------------------------------------------------------------
| Vision Supervisor | Unsworth | Simon | 10 | Telegenic Staff | 664 | Y | Y | Y | Y |
The above works great if you know the values to transpose, but you if you don't then you can use dynamic sql similar to this:
如果您知道要转置的值，则上述方法效果很好，但如果您不知道，则可以使用类似于以下内容的动态 sql：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(convert(char(10), date, 120))
from yourtable
group by date
order by date desc
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT role, familyname, givenname, skill,
level, id,' + @cols + ' from
(
select role, familyname, givenname, skill,
level, id, date, ''Y'' flag
from yourtable
) x
pivot
(
max(flag)
for date in (' + @cols + ')
) p '
execute(@query)
