SQL查询 - 透视分组或任何概念来获得我的结果表
问题描述:
我有一个名为#MyTable的表。SQL查询 - 透视分组或任何概念来获得我的结果表
MyTable的
ID Column Value
1 x1_abc 11
1 x1_def 23
1 x2_abc 9
1 x2_def 10
2 x1_abc 1
2 x1_def 55
2 x2_abc 42
2 x2_def 44
3 x1_abc 32
3 x1_def 33
3 x2_def 88
3 x2_def 90
我的输出应该是这样的
ID Column x1 x2
1 abc 11 9
1 def 23 10
2 abc 1 42
2 def 55 44
3 abc 32 88
3 def 33 90
不知道这是这是可能在SQL查询。我尝试使用SQL Pivot,但没有锻炼。请给出建议编写查询以获得此结果。
答
它看起来像你可能能够做一个PIVOT来创建你正在寻找的表,见下文。
SELECT ID, Column1, ISNULL([x1],0) AS 'x1', ISNULL([x2],0) AS 'x2'
FROM
(SELECT ID, RIGHT(Column1,3) AS Column1, LEFT(Column1,2) AS 'x', Value
FROM #start) AS d
PIVOT
(SUM(Value) FOR x IN ([x1],[x2])
) AS piv
我创建了一个临时表来用来测试它,它工作。见下面的临时表。
CREATE TABLE #start (ID int, Column1 nvarchar(50), Value int);
INSERT INTO #start
VALUES (1, 'x1_abc', 11),
(1, 'x1_def', 23),
(1, 'x2_abc', 9),
(1, 'x2_def', 10),
(2, 'x1_abc', 1),
(2, 'x1_def', 55),
(2, 'x2_abc', 42),
(2, 'x2_def', 44),
(3, 'x1_abc', 32),
(3, 'x1_def', 33),
(3, 'x2_def', 88),
(3, 'x2_def', 90)
这里是我使用PIOVT收到了成效:
ID Column1 x1 x2
1 abc 11 9
2 abc 1 42
3 abc 32 0
1 def 23 10
2 def 55 44
3 def 33 178
我希望这是有帮助的。随时提出任何问题。
答
以防万一你需要动态。
例
Declare @SQL varchar(max) = '
Select *
From (
Select ID
,[Column] = substring([column],charindex(''_'',[column])+1,50)
,Item = left([column],charindex(''_'',[column])-1)
,Value
From YourTable
) Src
Pivot (max([Value]) For [Item] in (' + Stuff((Select Distinct ','+QuoteName(left([column],charindex('_',[column])-1))
From YourTable
Order By 1
For XML Path('')),1,1,'') + ')) p
Order By 1,2'
Exec(@SQL);
返回
ID Column x1 x2
1 abc 11 9
1 def 23 10
2 abc 1 42
2 def 55 44
3 abc 32 88
3 def 33 90
生成的SQL看起来这
Select *
From (
Select ID
,[Column] = substring([column],charindex('_',[column])+1,50)
,Item = left([column],charindex('_',[column])-1)
,Value
From YourTable
) Src
Pivot (max([Value]) For [Item] in ([x1],[x2])) p
Order By 1,2
这是一个可怕的数据模型。你真的陷入了困境吗? :-( – Strawberry
是的@Strawberry –
肯定是不好的模式,Column应该是两个单独的列,一个用于'x1','x2'值,另一个用于'abc','def'值。 –