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

2022-01-22 00:00:00 pivot sql sql-server-2008 sql-server unpivot

我在一个表中有数据，其中包含同一 CardNum 的多行数据.我想创建一个表，其中同一 CardNum 的所有数据都显示在同一行上.

I have data in one table that contains several rows of data for the same CardNum. I would like to create a table where all the data for the same CardNum is displayed on the same row.

我的数据目前是这样的:

My data is currently like this:

PartID | CardNumber | RdrGrpID | TZID

0         412         31         1
0         412         34         1
0         567         38         1
0         567         33         5
0         567         71         3

这就是我想要的数据:

PartID | CardNumber | RdrGrpID_1 | TZID_1 | RdrGrpID_2 | TZID_2 | RdrGrpID_3 | TZID_3

0         412         31           1        34           1
0         567         38           1        33           5        71           3

提前谢谢你.

推荐答案

要获得此结果，您可以通过多种方式制定查询.

To get this result, there are several ways that you can formulate the query.

如果每个 partId 和 cardNumber 的值数量有限，则可以将 row_number() 与聚合函数/案例组合:

If you have a limited number of values for each partId and cardNumber, then you can use row_number() with an aggregate function/CASE combination:

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_3
from
(
  select partId, cardNumber, RdrGrpID, TZID
      , row_number() over(partition by partiD, cardnumber
                          order by rdrgrpid) rn
  from yt
) d
group by partid, cardnumber;

参见 SQL Fiddle with Demo

您也可以使用 PIVOT/UNPIVOT 函数来获取结果:

You could also use the PIVOT/UNPIVOT function to get the result:

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
) s
pivot
(
  max(val)
  for col in (RdrGrpID_1, TZID_1, RdrGrpID_2, TZID_2,
              RdrGrpID_3, TZID_3)
) piv

请参阅 SQL Fiddle with Demo.

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

Now if you have an unknown number of values, then you will need to use dynamic 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) rn
                      from yt
                    ) t
                    cross apply
                    (
                      select 'RdrGrpID' col, 1 so union all
                      select '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 with Demo.所有版本都给出结果:

See SQL Fiddle with Demo. All versions gives the result:

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

相关文章