SQL - 将多行中的数据转换为单行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16056529/
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
SQL - Take data from multiple rows into single row
提问by dawsonz
I have a table that looks similar to this here:
我有一张类似于这里的表格：
SetId AppCode AppEventId EventId FieldId ValueData
2012/2013 1000 361616 16 1 UNI
2012/2013 1000 361616 16 2 Isolation
2012/2013 1000 361616 16 3 DN
2012/2013 1050 378194 16 1 BUL
2012/2013 1050 378194 16 2 Isolation
2012/2013 1050 378194 16 3 RD
I would like to able to combine all that data when they have the same AppCode.
当它们具有相同的 AppCode 时，我希望能够组合所有这些数据。
Which would look like this:
看起来像这样：
SetId AppCode AppEventId EventId ValueData1 ValueData2 ValueData3
2012/2013 1000 361616 16 UNI Isolation DN
2012/2013 1050 378194 16 BUL Isolation RD
回答by bvr
Try this
尝试这个
SELECT SetId, AppCode, AppEventId, EventId
,max(CASE WHEN FieldId = 1 THEN ValueData END) AS ValueData1
,max(CASE WHEN FieldId = 2 THEN ValueData END) AS ValueData2
,max(CASE WHEN FieldId = 3 THEN ValueData END) AS ValueData3
FROM Table_Name
GROUP BY SetId,AppCode,AppEventId,EventId
回答by Taryn
This can be done by applying the PIVOTfunction which was made available starting in SQL Server 2005.
这可以通过应用从 SQL Server 2005 开始提供的PIVOT函数来完成。
If you have a known or set number of values that you want to transform into columns, then ou can hard-code the query:
如果您有已知或设定数量的要转换为列的值，那么您可以对查询进行硬编码：
select setid, appcode, appeventid,
eventid,
ValueData1, ValueData2, ValueData3
from
(
select setid, appcode, appeventid,
eventid,
'ValueData'+cast(FieldId as varchar(10)) FieldId,
ValueData
from yt
) d
pivot
(
max(ValueData)
for FieldId in (ValueData1, ValueData2, ValueData3)
) piv;
See SQL Fiddle with Demo.
But if you have an unknown number of values, then you will need to use dynamic SQL:
但是如果您有未知数量的值，那么您将需要使用动态 SQL：
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('ValueData'+cast(FieldId as varchar(10)))
from yt
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT setid, appcode, appeventid,
eventid,' + @cols + '
from
(
select setid, appcode, appeventid,
eventid,
''ValueData''+cast(FieldId as varchar(10)) FieldId,
ValueData
from yt
) x
pivot
(
max(ValueData)
for FieldId in (' + @cols + ')
) p '
execute(@query);
See SQL Fiddle with Demo. Both queries give the same result:
请参阅SQL Fiddle with Demo。两个查询都给出相同的结果：
| SETID | APPCODE | APPEVENTID | EVENTID | VALUEDATA1 | VALUEDATA2 | VALUEDATA3 |
-------------------------------------------------------------------------------------
| 2012/2013 | 1000 | 361616 | 16 | UNI | Isolation | DN |
| 2012/2013 | 1050 | 378194 | 16 | BUL | Isolation | RD |
回答by Devart
UPDATE:
更新：
Improve answer -
改进答案-
DECLARE @temp TABLE
(
SetId VARCHAR(9)
, AppCode CHAR(4)
, AppEventId INT
, EventId INT
, FieldId TINYINT
, ValueData VARCHAR(50)
)
INSERT INTO @temp (SetId, AppCode, AppEventId, EventId, FieldId, ValueData)
VALUES
('2012/2013', '1000', 361616, 16, 1, 'UNI'),
('2012/2013', '1000', 361616, 16, 2, 'Isolation'),
('2012/2013', '1000', 361616, 16, 3, 'DN'),
('2012/2013', '1050', 378194, 16, 1, 'BUL'),
('2012/2013', '1050', 378194, 16, 2, 'Isolation'),
('2012/2013', '1050', 378194, 16, 3, 'RD')
;WITH tbl AS
(
SELECT
PK = t.SetId + t.AppCode + CAST(AppEventId AS VARCHAR(10)) + CAST(EventId AS VARCHAR(5))
, t.SetId
, t.AppCode
, t.AppEventId
, t.EventId
, t.FieldId
, t.ValueData
FROM @temp t
)
SELECT DISTINCT
t.SetId
, t.AppCode
, t.AppEventId
, t.EventId
, t2.ValueData
, t3.ValueData
, t4.ValueData
FROM tbl t
JOIN tbl t2 ON t.PK = t2.PK AND t2.FieldId = 1
JOIN tbl t3 ON t.PK = t3.PK AND t3.FieldId = 2
JOIN tbl t4 ON t.PK = t4.PK AND t4.FieldId = 3
Not elegant, but it works -
不优雅，但它有效-
DECLARE @temp TABLE
(
SetId VARCHAR(9)
, AppCode CHAR(4)
, AppEventId INT
, EventId INT
, FieldId TINYINT
, ValueData VARCHAR(50)
)
INSERT INTO @temp (SetId, AppCode, AppEventId, EventId, FieldId, ValueData)
VALUES
('2012/2013', '1000', 361616, 16, 1, 'UNI'),
('2012/2013', '1000', 361616, 16, 2, 'Isolation'),
('2012/2013', '1000', 361616, 16, 3, 'DN'),
('2012/2013', '1050', 378194, 16, 1, 'BUL'),
('2012/2013', '1050', 378194, 16, 2, 'Isolation'),
('2012/2013', '1050', 378194, 16, 3, 'RD')
SELECT t.SetId
, t.AppCode
, t.AppEventId
, t.EventId
, t2.ValueData
, t3.ValueData
, t4.ValueData
FROM (
SELECT
t.SetId
, t.AppCode
, t.AppEventId
, t.EventId
, FieldId1 = MAX(CASE WHEN t.FieldId = 1 THEN t.FieldId END)
, FieldId2 = MAX(CASE WHEN t.FieldId = 2 THEN t.FieldId END)
, FieldId3 = MAX(CASE WHEN t.FieldId = 3 THEN t.FieldId END)
FROM @temp t
GROUP BY
t.SetId
, t.AppCode
, t.AppEventId
, t.EventId
) t
JOIN @temp t2 ON t.SetId = t2.SetId
AND t.AppCode = t2.AppCode
AND t.AppEventId = t2.AppEventId
AND t.EventId = t2.EventId
AND t2.FieldId = 1
JOIN @temp t3 ON t.SetId = t3.SetId
AND t.AppCode = t3.AppCode
AND t.AppEventId = t3.AppEventId
AND t.EventId = t3.EventId
AND t3.FieldId = 2
JOIN @temp t4 ON t.SetId = t4.SetId
AND t.AppCode = t4.AppCode
AND t.AppEventId = t4.AppEventId
AND t.EventId = t4.EventId
AND t4.FieldId = 3
