SQL Pivot具有多个值 [英] SQL Pivot with multiple values
本文介绍了SQL Pivot具有多个值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
你好,我有一个像这样的表:
Hello I have a table like so:
Building Categories Properties
-----------------------------------------
Building250 Structure Steel
Building250 Security Access Card
Building250 Security Security Guard
Building250 Security Fire Alarm System
Building250 Security Sprinklered
Building250 Security Smoke or heat detectors
Building46 Structure Brick
Building46 Structure Steel
Building46 Walls Steel Stud
Building46 Walls Masonry
Building46 Washroom OwnSpace
Building46 Washroom Common
Building46 Security Access Card
Building46 Security Burglar Alarm
我需要像这样旋转它:
Building Structure Security Walls Washroom
----------------------------------------------------------------------------
Building250 Steel Access Card
Security Guard
Fire Alarm System
Sprinklered
heat detectors
Building46 Brick Access Card Steel Stud OwnSpace
Steel Burglar Alarm Masonry Common
Sprinklered
我尝试了SQL Pivot,但是由于它需要聚合,因此对于给定的类别,它仅返回1个属性.还有另一种方法吗?
I tried SQL Pivot, but since it requires an aggregation, it returns only 1 property for a given category. Is there another way?
这是我的数据透视SQL:
Here's my Pivot SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Category)
FROM Buildings
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT Building, ' + @cols + ' from
(
SELECT Building, Category, Property, from Buildings
) x
pivot
(
min(Property)
for Category in (' + @cols + ')
) p '
execute(@query)
推荐答案
如前所述,Pivot可以进行聚合,尽管有一些变通办法,但您可以完成所需的工作而无需进行诸如以下的操作.您也可以使用与制作动态数据透视图相同的方法来使其动态化.
As you noted Pivot can do aggregation and while there are workarounds possible you can accomplish what you need without pivot with something like the following. You could also make it dynamic using the same method you would make a dynamic pivot.
Select B.Building, Str.Properties Structure,
Sec.Properties Security, Walls.Properties Walls, Wash.Properties Washroom
From (Select Distinct Building From Table) B
Left Join Table Str On Str.Building = B.Building And Categories = 'Structure'
Left Join Table Sec On Sec.Building = B.Building And Categories = 'Security'
Left Join Table Walls On Walls.Building = B.Building And Categories = 'Walls'
Left Join Table Wash On Wash.Building = B.Building And Categories = 'Washroom'
这篇关于SQL Pivot具有多个值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文