将不确定数量的行转置为列？

Question

我有两张有数据的表，我们称它们为表 T 和表 B。T 和 B 中的相关数据相互之间具有多对一关系，如下所示：

T.id	B.code
A	1
A	1.2
B	1.2
B	1.5
C	1
C	2
C	3
C	15
D	296

等（这只是一个粗略的演示，与我使用的实际数据并不相似）。

我运行了一个 count(*) 查询，发现表 T 中的项目出现在表 B 中的次数最多是 8 次。我想要的是转置上面的表，其中 T 中有成千上万的条目，还有几个将 B 对应值的一百个可能条目放入 9 列表中，如下所示：

T.id	CODE 1	CODE 2	CODE 3	CODE 4	CODE 5	CODE 6	CODE 7	CODE 8
A	1	1.2	NULL	NULL	NULL	NULL	NULL	NULL
B	1.2	1.5	NULL	NULL NULL	NULL	C	1	NULL NULL
2	3	15	NULL	NULL	NULL	NULL	D	296
NULL	NULL	NULL	NULL	NULL	NULL	NULL	等等	。

我

能找到的唯一搜索信息需要每次手动加入 B 并排除 B.code 的先前值（正如我目前正在做的那样），或使用 PIVOT 表（动态 SQL 或否则），它必须为 B.code 的每个可能值（如上所述，有数百个值）有一列，这两个选项都不是可扩展的选项。

作为参考，我的代码现在看起来像这样：

SELECT
T.id,
b1.code as 'CODE 1', b2.code as 'CODE 2', b3.code as 'CODE 3', b4.code as 'CODE 4' (etc)
FROM T
LEFT JOIN B b1 ON b1.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id)
LEFT JOIN B b2 ON b2.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code))
LEFT JOIN B b3 ON b3.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code, b2.code))
LEFT JOIN B b4 ON b4.code = 
  (SELECT TOP 1 code FROM B WHERE B.t_id = T.id AND B.code NOT IN (b1.code, b2.code, b3.code))

我的选择是什么？我注定了吗？一定有更好的方法，对吗？

Answer 1

您想要最终的查询是：

;WITH x AS 
 (
   SELECT [T.id], Code, rn = ROW_NUMBER() OVER 
     (PARTITION BY [T.id] ORDER BY @@SPID)
   FROM dbo.B
 ) SELECT [T.id], [CODE 1] = p.[1],[CODE 2] = p.[2], ... 
   FROM x
   PIVOT (MAX(Code) FOR rn IN 
   ([1],[2],...)) AS p;

但是您需要动态 SQL 才能到达那里，而不必硬编码 ...Code 8 或添加 Code 9 当另一个代码添加到表中时：

DECLARE @sql     nvarchar(max) = N'',
  @outputColumns nvarchar(max),
  @pivotColumns  nvarchar(max),
  @maxCount      int = (SELECT MAX(c) FROM 
    (SELECT COUNT(*) FROM dbo.B GROUP BY [T.id]) AS c(c));

;WITH x(n) AS (SELECT 1 UNION ALL SELECT n+1
  FROM x WHERE n < @maxCount)
SELECT @outputColumns = STRING_AGG(CONCAT('[CODE ',n,
                        '] = p.',QUOTENAME(n)), ','),
       @pivotColumns  = STRING_AGG(QUOTENAME(n), N',')
 FROM x;
 
 SELECT @sql = N';WITH x AS 
 (
   SELECT [T.id], Code, rn = ROW_NUMBER() OVER 
    (PARTITION BY [T.id] ORDER BY @@SPID)
   FROM dbo.B
 ) SELECT [T.id], ' + @outputColumns + N' 
   FROM x
   PIVOT (MAX(Code) FOR rn IN 
   (' + @pivotColumns + N')) AS p;';
 
 EXEC sys.sp_executesql @sql;

输出（来自问题中的示例数据）：

<表类=“s-表”>
<标题>

T.id
代码 1
代码 2
代码 3
代码 4

<正文>

A
1
1.2
空
空

B
1.2
1.5
空
空

C
1
2
3
15

D
296
空
空
空

• 示例 db<>fiddle

Answer 2

未经测试，但这也许会有所帮助

Select *
 From (
        Select T.id
              ,B.Code
              ,Col = concat('Code ',row_number() over (partition by T.id order by B.code) )
         From T
         Join B on B.ID=T.ID
      ) src
 Pivot (max(Code) for Col in ([Code 1],
                              [Code 2],
                              [Code 3],
                              [Code 4],
                              [Code 5],
                              [Code 6],
                              [Code 7],
                              [Code 8]) ) pvt