您可以使用取消透视/透视来获得所需的结果。有几种不同的方法可以获取结果，如果值的数量有限，则可以对查询进行硬编码，但如果值的数量未知，则需要使用动态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) |
602392714
清零编程群