根据列值将 SQL 选择数据集多行合并为一行

Question

我有一个SQL查询，将来自多个数据库表的数据合并到单个数据集中，例如：

SELECT 
CS.Id As ID,
CP.Country As Country,
S.Title As Site,
CONCAT(V.Title,' ',M.Model,' ',M.Version) As Machine,
CP.Title As Protocol,
CS.Title As Sequence,
IQS.SequenceType AS Type,
IQS.ImageQuality AS IQ,
_IQBeforeOpt =
CASE IQS.SequenceType
WHEN 1 THEN IQS.ImageQuality
END,
_IQDuringOpt = 
CASE IQS.SequenceType
WHEN 2 THEN IQS.ImageQuality
END,
_IQAfterOpt = 
CASE IQS.SequenceType
WHEN 3 THEN IQS.ImageQuality
END,
((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY))) AS SeqPI,
_SeqPIBeforeOpt = 
CASE IQS.SequenceType
WHEN 1 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END,
_SeqPIDuringOpt =
CASE IQS.SequenceType
WHEN 2 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END,
_SeqPIAfterOpt =
CASE IQS.SequenceType
WHEN 3 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END       
FROM ClientSequence CS
JOIN ImageQualitySequencePool IQS ON CS.Id = IQS.SequenceId
JOIN ClientProtocol CP ON CS.ClientProtocolId = CP.Id
JOIN Team S ON CP.TeamID = S.Id
JOIN Machine M On CP.MachineId = M.Id
JOIN Vendor V ON M.VendorId = V.Id
ORDER BY CP.Country,S.Title,CP.MachineId,CP.Title,CS.Title,IQS.SequenceType

上面的查询用于合并来自多个表(主表是ClientSequence和ImageQualitySequencePool)的数据，以构建我要用于报告的数据集。

以下是一些指示性值：

ID  Country    Site    Machine   Protocol   Sequence          Type  IQ   _IQBeforeOpt   _IQDuringOpt   _IQAfterOpt  SeqPI   _SeqPIBeforeOpt   _SeqPIDuringOpt    _SeqPIAfterOpt
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
41  Country_1  Site_1  Mach_1    ProtocolA  Ax_PD_FSE_FS      1     2    2              NULL            NULL         0.805   0.805             NULL               NULL
86  Country_1  Site_1  Mach_1    ProtocolA  Ax_PD_FSE_FS      2     4    NULL           4               NULL         1.38    NULL              1.38               NULL
91  Country_1  Site_1  Mach_1    ProtocolA  Ax_PD_FSE_FS      3     3    NULL           NULL            3            1.03    NULL              NULL               1.03
76  Country_1  Site_1  Mach_1    ProtocolB  Cor_PD_FSE_FS     1     3    3              NULL            NULL         0.83    0.83              NULL               NULL
88  Country_1  Site_1  Mach_1    ProtocolB  Cor_PD_FSE_FS     2     4    NULL           4               NULL         1.62    NULL              1.62               NULL
92  Country_1  Site_1  Mach_1    ProtocolB  Cor_PD_FSE_FS     3     3    NULL           NULL            3            1.21    NULL              NULL               1.21
91  Country_2  Site_3  Mach_3    ProtocolA  Seg_SF_FSE_FS     1     3    1              NULL            NULL         1.41    1.41              NULL               NULL
94  Country_2  Site_3  Mach_3    ProtocolA  Seg_SF_FSE_FS     2     2    NULL           2               NULL         1.28    NULL              1.28               NULL
72  Country_2  Site_3  Mach_3    ProtocolC  Ger_SE_FSE_FS     1     3    3              NULL            NULL         0.83    0.83              NULL               NULL

因此，"国家、站点、机器、协议、序列"的每个组合都可以是类型：1、2 或 3 含义：

• 类型 1：优化前的序列
• 类型 2：期间的序列 优化
• 类型 3：优化后的序列

正如您在查询中看到的那样，我最初在数据库中存储了一些IQ和SeqPI的值，并且我根据序列的类型动态填充字段(_IQBeforeOpt，_IQDuringOpt，_IQAfterOpt)和(_SeqPIBeforeOpt，_SeqPIDuringOpt，_SeqPIAfterOpt)。

一些"序列"具有对应于所有 3 种类型的记录，而其他一些则没有。

我想要的是将"国家、站点、机器、协议、序列"特定组合的所有记录合并到一行中。所以最后我想有一个查询来生成一个数据集，如下所示：

Country    Site    Machine   Protocol    Sequence       Type     _IQBeforeOpt   _IQDuringOpt   _IQAfterOpt  _SeqPIBeforeOpt   _SeqPIDuringOpt    _SeqPIAfterOpt
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Country_1  Site_1  Mach_1    ProtocolA   Ax_PD_FSE_FS   1,2,3    2              4             3            0.805              1.38               1.03
Country_1  Site_1  Mach_1    ProtocolB   Cor_PD_FSE_FS  1,2,3    3              4             3            0.83               1.62               1.21
Country_2  Site_3  Mach_3    ProtocolA   Seg_SF_FSE_FS  1,2      3              2             NULL         1.41               1.28               NULL
Country_2  Site_3  Mach_3    ProtocolC   Ger_SE_FSE_FS  1        3              NULL          NULL         0.83               NULL               NULL

Answer 1

尝试以下查询：

SELECT
CP.Country As Country,
S.Title As Site,
CONCAT(V.Title,' ',M.Model,' ',M.Version) As Machine,
CP.Title As Protocol,
CS.Title As Sequence,
STUFF(CONCAT(MAX(IIF(IQS.SequenceType = 1, ',1', '')), MAX(IIF(IQS.SequenceType = 2, ',2', '')), MAX(IIF(IQS.SequenceType = 3, ',3', ''))),1,1,'') AS Type,
_IQBeforeOpt =
MAX(CASE IQS.SequenceType
WHEN 1 THEN IQS.ImageQuality
END),
_IQDuringOpt = 
MAX(CASE IQS.SequenceType
WHEN 2 THEN IQS.ImageQuality
END),
_IQAfterOpt = 
MAX(CASE IQS.SequenceType
WHEN 3 THEN IQS.ImageQuality
END),
_SeqPIBeforeOpt = 
MAX(CASE IQS.SequenceType
WHEN 1 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END),
_SeqPIDuringOpt =
MAX(CASE IQS.SequenceType
WHEN 2 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END),
_SeqPIAfterOpt =
MAX(CASE IQS.SequenceType
WHEN 3 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END)      
FROM 
ClientSequence CS
JOIN ImageQualitySequencePool IQS ON CS.Id = IQS.SequenceId
JOIN ClientProtocol CP ON CS.ClientProtocolId = CP.Id
JOIN Team S ON CP.TeamID = S.Id
JOIN Machine M On CP.MachineId = M.Id
JOIN Vendor V ON M.VendorId = V.Id
GROUP BY CP.Country, S.Title, CONCAT(V.Title,' ',M.Model,' ',M.Version), CP.Title, CS.Title

Answer 2

窗口函数是聚合以下内容的有效方法： 过条款

使用 OVER 子句，您可以定义自己的聚合函数。

如果失败了，您可以使用CTE破解它 与 CTE 的串联

Answer 3

你能试试这个吗？

SELECT 
CS.Id As ID,
CP.Country As Country,
S.Title As Site,
CONCAT(V.Title,' ',M.Model,' ',M.Version) As Machine,
CP.Title As Protocol,
CS.Title As Sequence,
--   IQS.SequenceType AS Type,
STUFF(OQS.types,1,1,'') AS [type],
--  IQS.ImageQuality AS IQ,
_IQBeforeOpt =MAX(
CASE IQS.SequenceType
WHEN 1 THEN IQS.ImageQuality
END),
_IQDuringOpt =MAX( 
CASE IQS.SequenceType
WHEN 2 THEN IQS.ImageQuality
END),
_IQAfterOpt = 
MAX(CASE IQS.SequenceType
WHEN 3 THEN IQS.ImageQuality
END),
--  ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY))) AS SeqPI,
_SeqPIBeforeOpt = MAX(
CASE IQS.SequenceType
WHEN 1 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END),
_SeqPIDuringOpt =MAX(
CASE IQS.SequenceType
WHEN 2 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END),
_SeqPIAfterOpt =MAX(
CASE IQS.SequenceType
WHEN 3 THEN ((IQS.ImageQuality*IQS.SeqSlices)/(IQS.SeqTime*(CS.slicetkndef*CS.pixelspacingX*CS.pixelspacingY)))
END)       
FROM ClientSequence CS
JOIN ImageQualitySequencePool IQS ON CS.Id = IQS.SequenceId
JOIN ClientProtocol CP ON CS.ClientProtocolId = CP.Id
JOIN Team S ON CP.TeamID = S.Id
JOIN Machine M On CP.MachineId = M.Id
JOIN Vendor V ON M.VendorId = V.Id
OUTER APPLY(SELECT ','+LTRIM(QS.SequenceType) FROM ImageQualitySequencePool AS QS WHERE CS.Id = QS.SequenceId FOR XML PATH('')) OQS(types)
GROUP BY CS.Id, CP.Country, S.Title ,CONCAT(V.Title,' ',M.Model,' ',M.Version),CP.MachineId,CP.Title,CS.Title,oQS.types
ORDER BY CP.Country,S.Title,CP.MachineId,CP.Title,CS.Title--,IQS.SequenceType