如何将数据从基于特定列的行转换为另一种数据结构

小编典典

如何将数据从基于特定列的行转换为另一种数据结构

sql

我有一个结果集，我想按此Service列“展平” 。示例将最好地解释它：

给定此结果集（称为request）：

---------------------------------------
| Id |    Service   |  C1 | ... | Cn  |
--------------------------------------|
|  1 |       A      |  5  |     | 3   |
--------------------------------------|
|  1 |       B      |  2  |     | 1   |
--------------------------------------|
|  2 |       A      |  9  |     | 4   |
--------------------------------------

我想得到这个：

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

最终预期结果：

每个Id（现在具有多个值的一行）
服务的每个不同值n在最终结果集中都将包含列

当前解决方案（有效，但超长且效率不高）：

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

注意：
服务数量约为10（Service列中的#distinct值），这是问题本身的通用表述。

我知道SQL操作（例如数据透视，取消透视，交叉应用，联接等），但仍然困扰着我这个问题，因为我没有发现可以立即解决此问题的棘手问题。我很高兴知道其中一种方法可以解决此问题，但我错过了。

谢谢

阅读 166

2021-04-15

共1个答案

小编典典

您可以使用取消透视/透视来获得所需的结果。有几种不同的方法可以获取结果，如果值的数量有限，则可以对查询进行硬编码，但如果值的数量未知，则需要使用动态SQL。

UNPIVOT进程会将c1等等的多列转换为多行。一旦数据在多行中，您就可以轻松应用PIVOT功能。您可以使用unpivot函数或CROSS
APPLY来转换多列中的数据：

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)

请参阅带有演示的SQL Fiddle。交叉应用会将您的数据转换为以下格式：

| 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 |

数据采用这种格式后，您可以应用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;

请参阅带有演示的SQL Fiddle。

然后，如果您有未知数量的值，则可以将上述查询转换为动态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;

请参阅带有演示的SQL Fiddle。两者都会给出结果：

| ID | SERVICEA_C1_1 | SERVICEA_CN_1 | SERVICEB_C1_2 | SERVICEB_CN_2 |
|  1 |             5 |             3 |             2 |             1 |
|  2 |             9 |             4 |        (null) |        (null) |

2021-04-15