Combine SQL select data-set multiple rows into a single row based on column values

Question

I have an SQL query than combines data from several database tables into a single data set like:

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

The query above is used to combine data from several tables (main tables are ClientSequence & ImageQualitySequencePool) to build a data set i want to use for a report.

Here is some indicative values:

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

So each combination of "Country, Site, Machine, Protocol, Sequence" can be of Types: 1,2 or 3 meaning:

Type 1: Sequence before optimization
Type 2: Sequence during optimization
Type 3: Sequence after optimization

As you can see in the query initially i have some values for IQ & SeqPI stored in the database and i populate fields (_IQBeforeOpt, _IQDuringOpt, _IQAfterOpt) and (_SeqPIBeforeOpt,_SeqPIDuringOpt,_SeqPIAfterOpt) on the fly based on the type of the Sequence.

Some "Sequences" have records that correspond to all 3 Types, and some other they do not.

What i want is to merge into a single row all the records for a specific combination of "Country, Site, Machine, Protocol, Sequence". So finally i would like to have a query that is resulting a data set like:

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

Your reporting tool may already have a built-in feature for Crosstab or Pivot reports. You can use it out of the box without any modifications to your query. Try and see if it works — cha, Jan 03 '18 at 23:59

score 2 · Accepted Answer · answered Jan 04 '18 at 04:31

Try this query:

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

score 0 · Answer 2 · answered Jan 04 '18 at 00:09

0

Window functions are an efficient way to aggregate: Over Clause

With the OVER clause you can define your own aggregate function.

If that fails you can hack it with a CTE Concatenation with CTEs

answered Jan 04 '18 at 00:09

Nutatello

89
4

Nolan Shang · Answer 3 · 2018-01-04T13:42:47.030

can you try this?

    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

Did tried but it does not work. Will take a look closer to your query though — thanili, Jan 04 '18 at 02:09

Combine SQL select data-set multiple rows into a single row based on column values

3 Answers3