How to transform data from rows based on a specific column to another data structure

I have a result set which I want to “flatten” by the Service column.
Example will explain it best:

Given this result set (Lets call it request):

  • How to cascade delete over many to many table
  • SQL Server Trigger switching Insert,Delete,Update
  • Return date as ddmmyyyy in SQL Server
  • Join certain rows to the results
  • How can I retrieve data from sql statement based on two table's criteria
  • Sql column adding another column
  • ---------------------------------------
    | Id |    Service   |  C1 | ... | Cn  |
    --------------------------------------|
    |  1 |       A      |  5  |     | 3   |
    --------------------------------------|
    |  1 |       B      |  2  |     | 1   |
    --------------------------------------|
    |  2 |       A      |  9  |     | 4   |
    --------------------------------------
    

    I want to get this one:

    ---------------------------------------------------------------------------
    | Id |  ServiceA_C1 | ... | ServiceA_Cn |ServiceB_C1 | ... | ServiceB_C2n |
    ---------------------------------------------------------------------------
    | 1 |       5       | ... |      3      |     2      | ... |       1      |   
    ---------------------------------------------------------------------------
    | 2 |       9       | ... |      4      |    NULL    | ... |      NULL    |   
    ---------------------------------------------------------------------------
    

    Final desired outcome:

    • Each Id (which had multiple values now have one row)
    • Each distinct value of service will have n columns in the final result set

    Current solution (which works, but super long and not efficient):

    SELECT A.C1 AS ServiceA_C1, ..,A.Cn AS ServiceA_Cn,B.C1 AS ServiceB_C1, ..,B.Cn AS ServiceB_Cn  
    FROM (SELECT *
          FROM request
          WHERE Service = 'A') AS A
        JOIN
          (SELECT *
          FROM request
          WHERE Service = 'B') AS B
        ON A.Id = B.Id
    

    Notes:
    Number of services is about 10 (#distinct values in Service column),
    This is a generic phrasing of the problem itself.

    I know SQL manipulations like pivot, unpivot, cross apply, joins etc, and still this problem annoys me because I didn’t found something tricky to instantly solve this. I would be happy to know that one of this methods solves this and I missed it.

    Thanks

    One Solution collect form web for “How to transform data from rows based on a specific column to another data structure”

    You can use the unpivot/pivot to get the result that you want. There are a few different ways that you can get the result, if you have a limited number of values then you can hard-code the query but if you have an unknown number of values then you will need to use dynamic SQL.

    The UNPIVOT process will convert the multiple columns of c1, etc` into multiple rows. Once the data is in the multiple rows then you can easily apply the PIVOT function. You can use the unpivot function or CROSS APPLY to convert the data from multiple columns:

    select id,
      col = 'Service'+Service+'_'+col+'_'+cast(seq as varchar(10)),
      value
    from
    (
      select id, service, c1, cn
        , row_number() over(partition by id
                            order by service) seq
      from yourtable
    ) t
    cross apply
    (
      select 'c1', c1 union all
      select 'cn', cn
    ) c (col, value)
    

    See SQL Fiddle with Demo. The cross apply will convert your data into the format:

    | ID |           COL | VALUE |
    |  1 | ServiceA_c1_1 |     5 |
    |  1 | ServiceA_cn_1 |     3 |
    |  1 | ServiceB_c1_2 |     2 |
    |  1 | ServiceB_cn_2 |     1 |
    |  2 | ServiceA_c1_1 |     9 |
    |  2 | ServiceA_cn_1 |     4 |
    

    Once the data is in this format you can apply PIVOT:

    select id, ServiceA_c1_1, ServiceA_cn_1,
      ServiceB_c1_2, ServiceB_cn_2
    from
    (
      select id,
        col = 'Service'+Service+'_'+col+'_'+cast(seq as varchar(10)),
        value
      from
      (
        select id, service, c1, cn
          , row_number() over(partition by id
                              order by service) seq
        from yourtable
      ) t
      cross apply
      (
        select 'c1', c1 union all
        select 'cn', cn
      ) c (col, value)
    ) d
    pivot
    (
      max(value)
      for col in (ServiceA_c1_1, ServiceA_cn_1,
                  ServiceB_c1_2, ServiceB_cn_2)
    ) piv;
    

    See SQL Fiddle with Demo.

    Then if you have an unknown number of values, you can convert the above query to dynamic SQL:

    DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX)
    
    select @cols = STUFF((SELECT ',' + QUOTENAME('Service'+Service+'_'+col+'_'+cast(seq as varchar(10))) 
                        from 
                        (
                          select service, 
                            row_number() over(partition by id
                                              order by service) seq
                          from yourtable 
                        )d
                        cross apply
                        (
                          select 'c1', 1 union all
                          select 'cn', 2
                        ) c (col, so)
                        group by seq, Service, col, so
                        order by seq, so
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'')
    
    set @query = 'SELECT id, ' + @cols + ' 
                from 
                (
                  select id,
                    col = ''Service''+Service+''_''+col+''_''+cast(seq as varchar(10)),
                    value
                  from
                  (
                    select id, service, c1, cn
                      , row_number() over(partition by id
                                          order by service) seq
                    from yourtable
                  ) t
                  cross apply
                  (
                    select ''c1'', c1 union all
                    select ''cn'', cn
                  ) c (col, value)
                ) x
                pivot 
                (
                    max(value)
                    for col in (' + @cols + ')
                ) p '
    
    execute sp_executesql @query;
    

    See SQL Fiddle with Demo. Both will give a result:

    | ID | SERVICEA_C1_1 | SERVICEA_CN_1 | SERVICEB_C1_2 | SERVICEB_CN_2 |
    |  1 |             5 |             3 |             2 |             1 |
    |  2 |             9 |             4 |        (null) |        (null) |
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.