SQL - Take data from multiple rows into single row

Question

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.

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

Answer 1

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 

Answer 2

This can be done by applying the PIVOT function which was made available starting in SQL Server 2005.

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:

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:

|     SETID | APPCODE | APPEVENTID | EVENTID | VALUEDATA1 | VALUEDATA2 | VALUEDATA3 |
-------------------------------------------------------------------------------------
| 2012/2013 |    1000 |     361616 |      16 |        UNI |  Isolation |         DN |
| 2012/2013 |    1050 |     378194 |      16 |        BUL |  Isolation |         RD |

Answer 3

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