SQL 没有聚合函数的 TSQL Pivot
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1343145/
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
TSQL Pivot without aggregate function
提问by ctrlShiftBryan
I have a table like this...
我有一张这样的桌子...
CustomerID DBColumnName Data
--------------------------------------
1 FirstName Joe
1 MiddleName S
1 LastName Smith
1 Date 12/12/2009
2 FirstName Sam
2 MiddleName S
2 LastName Freddrick
2 Date 1/12/2009
3 FirstName Jaime
3 MiddleName S
3 LastName Carol
3 Date 12/1/2009
And I want this...
而我想要这个...
Is this possible using PIVOT?
这可以使用 PIVOT 吗？
CustomerID FirstName MiddleName LastName Date
----------------------------------------------------------------------
1 Joe S Smith 12/12/2009
2 Sam S Freddrick 1/12/2009
3 Jaime S Carol 12/1/2009
采纳答案by gbn
You can use the MAX aggregate, it would still work. MAX of one value = that value..
您可以使用 MAX 聚合，它仍然可以工作。一个值的最大值 = 该值..
In this case, you could also self join 5 times on customerid, filter by dbColumnName per table reference. It may work out better.
在这种情况下，您还可以在 customerid 上自行加入 5 次，按每个表引用按 dbColumnName 过滤。效果可能会更好。
回答by Charles Bretana
yes, but why !!??
是的，但是为什么 ！！？？
Select CustomerID,
Min(Case DBColumnName When 'FirstName' Then Data End) FirstName,
Min(Case DBColumnName When 'MiddleName' Then Data End) MiddleName,
Min(Case DBColumnName When 'LastName' Then Data End) LastName,
Min(Case DBColumnName When 'Date' Then Data End) Date
From table
Group By CustomerId
回答by mr_eclair
WITH pivot_data AS
(
SELECT customerid, -- Grouping Column
dbcolumnname, -- Spreading Column
data -- Aggregate Column
FROM pivot2
)
SELECT customerid, [firstname], [middlename], [lastname]
FROM pivot_data
PIVOT (max(data) FOR dbcolumnname IN ([firstname],[middlename],[lastname])) AS p;
回答by shahkalpesh
SELECT
main.CustomerID,
f.Data AS FirstName,
m.Data AS MiddleName,
l.Data AS LastName,
d.Data AS Date
FROM table main
INNER JOIN table f on f.CustomerID = main.CustomerID
INNER JOIN table m on m.CustomerID = main.CustomerID
INNER JOIN table l on l.CustomerID = main.CustomerID
INNER JOIN table d on d.CustomerID = main.CustomerID
WHERE f.DBColumnName = 'FirstName'
AND m.DBColumnName = 'MiddleName'
AND l.DBColumnName = 'LastName'
AND d.DBColumnName = 'Date'
Edit: I have written this without an editor & have not run the SQL. I hope, you get the idea.
编辑：我在没有编辑器的情况下编写了这个，并且没有运行 SQL。我希望，你明白了。
回答by ctrlShiftBryan
Ok, sorry for the poor question. gbn got me on the right track. This is what I was looking for in an answer.
好的，抱歉这个可怜的问题。gbn 让我走上正轨。这就是我在答案中寻找的内容。
SELECT [FirstName], [MiddleName], [LastName], [Date]
FROM #temp
PIVOT
( MIN([Data])
FOR [DBColumnName] IN ([FirstName], [MiddleName], [LastName], [Date])
)AS p
Then I had to use a while statement and build the above statement as a varchar and use dynmaic sql.
然后我不得不使用while语句并将上述语句构建为varchar并使用dynmaic sql。
Using something like this
使用这样的东西
SET @fullsql = @fullsql + 'SELECT ' + REPLACE(REPLACE(@fulltext,'(',''),')','')
SET @fullsql = @fullsql + 'FROM #temp '
SET @fullsql = @fullsql + 'PIVOT'
SET @fullsql = @fullsql + '('
SET @fullsql = @fullsql + ' MIN([Data])'
SET @fullsql = @fullsql + ' FOR [DBColumnName] IN '+@fulltext
SET @fullsql = @fullsql + ')'
SET @fullsql = @fullsql + 'AS p'
EXEC (@fullsql)
Having a to build @fulltext using a while loop and select the distinct column names out of the table. Thanks for the answers.
使用 while 循环构建 @fulltext 并从表中选择不同的列名。感谢您的回答。
回答by bielawski
The OP didn't actually need to pivot without agregation but for those of you coming here to know how see:
OP 实际上不需要在没有聚合的情况下进行旋转，但对于那些来到这里的人来说，如何看：
The answer to that question involves a situation where pivot without aggregation is needed so an example of doing it is part of the solution.
该问题的答案涉及需要没有聚合的枢轴的情况，因此这样做的示例是解决方案的一部分。
回答by user3538033
Try this:
尝试这个：
SELECT CUSTOMER_ID, MAX(FIRSTNAME) AS FIRSTNAME, MAX(LASTNAME) AS LASTNAME ...
FROM
(
SELECT CUSTOMER_ID,
CASE WHEN DBCOLUMNNAME='FirstName' then DATA ELSE NULL END AS FIRSTNAME,
CASE WHEN DBCOLUMNNAME='LastName' then DATA ELSE NULL END AS LASTNAME,
... and so on ...
GROUP BY CUSTOMER_ID
) TEMP
GROUP BY CUSTOMER_ID
回答by Randy Boamah
This should work:
这应该有效：
select * from (select [CustomerID] ,[Demographic] ,[Data]
from [dbo].[pivot]
) as Ter
pivot (max(Data) for Demographic in (FirstName, MiddleName, LastName, [Date]))as bro
回答by user7237698
Here is a great way to build dynamic fields for a pivot query:
这是为数据透视查询构建动态字段的好方法：
--summarize values to a tmp table
--将值汇总到 tmp 表
declare @STR varchar(1000)
SELECT @STr = COALESCE(@STr +', ', '')
+ QUOTENAME(DateRange)
from (select distinct DateRange, ID from ##pivot)d order by ID
---see the fields generated
---查看生成的字段
print @STr
exec(' .... pivot code ...
pivot (avg(SalesAmt) for DateRange IN (' + @Str +')) AS P
order by Decile')
