SQL 来自select sql的动态列名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34989510/
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
Dynamically column names from select sql
提问by Pochen
I have the following data output from my database
我的数据库有以下数据输出
Observation 1 aug -2015
Improvement suggestion 1 dec -2015
Observation 1 dec -2015
Accident 2 jan -2016
Non Conformity 5 jan -2016
Observation 5 jan -2016
I've tried to figure out how to use PIVOT-table to make this work but cannot make it work. The date is dynamic depending on a date in the database. The output I am looking for is like below. Can someone please point me into right direction?
我试图弄清楚如何使用 PIVOT-table 来完成这项工作，但无法使其工作。日期是动态的，取决于数据库中的日期。我正在寻找的输出如下所示。有人可以指出我正确的方向吗？
Look at the fiddle what I've tried so far I know it is using SUM right now, and that is not correct, but I cannot figure out what to use. http://sqlfiddle.com/#!3/0bd0c/4
看看我到目前为止尝试过的小提琴，我知道它现在正在使用 SUM，这是不正确的，但我不知道要使用什么。http://sqlfiddle.com/#!3/0bd0c/4
PS. The data and the image are not related, so don't be fooled by the image. It is just an example
附注。数据和图片没有关系，不要被图片骗了。这只是一个例子
回答by Mihail Stancescu
Is this what you were looking for:
这是您要找的：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(ColumnName)
from tempData
group by ColumnName, name
FOR XML PATH(''), Type
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = N'SELECT Name, ' + @cols + N' from
(
select Name, value, ColumnName
from tempData
) x
pivot
(
SUM(value)
for ColumnName in (' + @cols + N')
) p '
exec sp_executesql @query;
Changing this in your fiddle return the rows as you need it.
在您的小提琴中更改此设置会根据需要返回行。
回答by Shnugo
Try it like this:
像这样尝试：
DECLARE @cols AS NVARCHAR(MAX)=
STUFF(
(
SELECT DISTINCT ',[' + ColumnName + ']'
FROM tempData
FOR XML PATH('')
)
,1,1,'');
DECLARE @SqlCmd VARCHAR(MAX)=
'SELECT p.*
FROM
(
SELECT *
FROM tempData
) AS tbl
PIVOT
(
SUM(Value) FOR ColumnName IN(' + @cols +')
) AS p';
EXEC(@SqlCmd);
