在 SQL 中将行转换为列

Table A

ID  COLA    
-----------------------
A   value1      
B   value1
C   value1      

表B

ID  DETAIL_ID   COL_X   COL_Y
A   0           foo     foo
A   1           bar     bar
B   0           foo     foo

我的预期结果是这样的

ID  COLA    COL_X_0 COL_X_1 COL_Y_0 COL_Y_1
A   value1  foo     bar     foo     bar
B   value1  foo     NULL    foo     NULL
C   value1  NULL    NULL    NULL    NULL

这意味着表 B 的行将是基于 DETAIL_ID 列的列值。

我尝试为此编写查询，但由于以下原因而无法成功。 DetailID 值的数量不会是固定长度的。这意味着我无法对列的名称进行硬编码。

这将给出您所描述的确切输出，并且您可以根据需要添加更多列

DECLARE @a table (id char, cola varchar(10))
DECLARE @b table (id char, detail_id int, colx char(3), coly char(3))

INSERT @a values('A', 'value1'),('B', 'value2'),('C','value3')
INSERT @b values('A', 0, 'foo', 'foo'),('A', 1, 'bar', 'bar'),
('B',0, 'foo','foo')--,('A', 2, 'bar', 'bar') -- add this for extra columns

CREATE TABLE ##t(id char, detail_id tinyint, colvalue char(3), col varchar(8), cola varchar(10))
DECLARE @columns varchar(max)=''
DECLARE @sqlstring varchar(1000)

;WITH a as (
SELECT a.id, a.cola, b.detail_id, colx, coly,
'col_x_' + cast(detail_id as varchar) col_a,
'col_y_' + cast(detail_id as varchar) col_b
FROM @a a LEFT JOIN @b b on a.id = b.id
) 
INSERT ##t
SELECT id, detail_id, colx, col_a, cola FROM a
UNION
SELECT id, detail_id, coly, col_b, cola FROM a
ORDER BY 4,2

SELECT @columns = coalesce(@columns, '') +',[' + col + ']'
FROM (
SELECT DISTINCT col, detail_id FROM ##t where not col is null
) a 

SET @columns = stuff(@columns, 1,1,'')

SET @sqlstring = 
'SELECT * FROM (
SELECT id, cola, col, colvalue FROM ##t 
) b
PIVOT(max(colvalue) FOR col 
in(
'+@columns+'))AS p order by 1'

EXEC(@sqlstring)

DROP TABLE ##t