I am using a Microsoft Server-2008 and I have a table like this:
我使用的是Microsoft Server-2008,我有一个这样的表:
Name | Houre | Value
Hst1 | 4 | 45
Hst1 | 5 | 12
Hst1 | 6 | 88
Hst2 | 4 | 82
Hst2 | 5 | 7
Hst2 | 6 | 12
And I want to get something like this:
我希望得到这样的东西:
Name | 4 | 5 | 6
Hst1 | 45 | 12 | 88
Hst2 | 82 | 7 | 12
Can you help me?
你能帮助我吗?
2 个解决方案
#1
0
Depending if you only have 4,5,6
you can do a conditional agregation, other wise you need a dynamic PIVOT
如果您只有4,5,6,您可以进行条件聚合,否则您需要动态PIVOT
SELECT Name,
MAX(CASE WHEN Houre = 4 THEN Value ELSE 0 END) as [4],
MAX(CASE WHEN Houre = 5 THEN Value ELSE 0 END) as [5],
MAX(CASE WHEN Houre = 6 THEN Value ELSE 0 END) as [6]
FROM YourTable
GROUP BY Name
#2
0
Use PIVOT
!
SELECT *
FROM (
VALUES ('Hst1', 4, 45),
('Hst1', 5, 12),
('Hst1', 6, 88),
('Hst2', 4, 82),
('Hst2', 5, 7),
('Hst2', 6, 12)
) t(name, hour, value)
PIVOT (
max(value) FOR hour IN ([4], [5], [6])
) t
#1
0
Depending if you only have 4,5,6
you can do a conditional agregation, other wise you need a dynamic PIVOT
如果您只有4,5,6,您可以进行条件聚合,否则您需要动态PIVOT
SELECT Name,
MAX(CASE WHEN Houre = 4 THEN Value ELSE 0 END) as [4],
MAX(CASE WHEN Houre = 5 THEN Value ELSE 0 END) as [5],
MAX(CASE WHEN Houre = 6 THEN Value ELSE 0 END) as [6]
FROM YourTable
GROUP BY Name
#2
0
Use PIVOT
!
SELECT *
FROM (
VALUES ('Hst1', 4, 45),
('Hst1', 5, 12),
('Hst1', 6, 88),
('Hst2', 4, 82),
('Hst2', 5, 7),
('Hst2', 6, 12)
) t(name, hour, value)
PIVOT (
max(value) FOR hour IN ([4], [5], [6])
) t