SQL 2008将具有相同ID的多行数据合并为一行

SQL 2008将具有相同ID的多行数据合并为一行

要获得此结果，可以采用几种方式来制定查询。

如果每个

partId

和的值数量有限

cardNumber

，则可以

row_number()

与聚合函数/ CASE组合一起使用：

select partid, cardnumber,  max(case when rn = 1 then rdrgrpid end) rdrgrpid_1,  max(case when rn = 1 then TZID end) TZID_1,  max(case when rn = 2 then rdrgrpid end) rdrgrpid_2,  max(case when rn = 2 then TZID end) TZID_2,  max(case when rn = 3 then rdrgrpid end) rdrgrpid_3,  max(case when rn = 3 then TZID end) TZID_3from(  select partId, cardNumber, RdrGrpID, TZID      , row_number() over(partition by partiD, cardnumber    order by rdrgrpid) rn  from yt) dgroup by partid, cardnumber;

参见带有演示的SQL Fiddle

您还可以使用PIVOT / UNPIVOT函数获取结果：

select *from(  select partid, cardnumber,     col+'_'+cast(rn as varchar(10)) col,     val  from   (    select partId, cardNumber, RdrGrpID, TZID      , row_number() over(partition by partiD, cardnumber    order by rdrgrpid) rn    from yt  ) d  unpivot  (    val    for col in (rdrgrpid, tzid)  ) un) spivot(  max(val)  for col in (RdrGrpID_1, TZID_1, RdrGrpID_2, TZID_2,   RdrGrpID_3, TZID_3)) piv

请参阅带有演示的SQL Fiddle。

现在，如果您有未知数量的值，那么您将需要使用动态sql：

DECLARE @colsPivot AS NVARCHAr(MAX),    @query  AS NVARCHAr(MAX)select @colsPivot = STUFF((SELECT ',' + QUOTENAME(c.col + '_'+cast(rn as varchar(10)))          from         (select row_number() over(partition by partiD, cardnumber  order by rdrgrpid) rnfrom yt         ) t         cross apply         (select 'RdrGrpID' col, 1 so union allselect 'TZID', 2         ) c         group by col, rn, so         order by rn, so FOR XML PATH(''), TYPE ).value('.', 'NVARCHAr(MAX)')         ,1,1,'')set @query   = 'select partid, cardnumber,  '+@colsPivot+'       from      (        select partid, cardnumber,col+''_''+cast(rn as varchar(10)) col,val        from         (          select partId, cardNumber, RdrGrpID, TZID , row_number() over(partition by partiD, cardnumber          order by rdrgrpid) rn          from yt        ) d        unpivot        (          val          for col in (rdrgrpid, tzid)        ) un      ) s      pivot      (        max(val)        for col in ('+ @colspivot +')      ) p'exec(@query);

请参阅带有演示的SQL Fiddle。所有版本均提供结果：

| PARTID | CARDNUMBER | RDRGRPID_1 | TZID_1 | RDRGRPID_2 | TZID_2 | RDRGRPID_3 | TZID_3 |-----------------------------------------------------------------------------------------|      0 |        412 |         31 |      1 |         34 |      1 |     (null) | (null) ||      0 |        567 |         33 |      5 |         38 |      1 |         71 |      3 |