是否有办法将表的输出转置为列标头?

Question

我有一个表候选

id          candidate_name
---------------------------
1            john
2            mary

和另一个表units

id name
--------
1  unit1
2  unit2
3  unit3

我想生成一个输出

id  candidate_name  unit1  unit2  unit3
---------------------------------------
1       john        null   null  null
2       mary        null   null  null

我有什么办法可以做到这一点吗?

Answer 1

您的数据

CREATE TABLE candidate(
id             int NOT NULL 
,candidate_name VARCHAR(40)
);
INSERT INTO candidate
(id,candidate_name) VALUES 
(1,'john'),
(2,'mary');
CREATE TABLE units(
id   int NOT NULL 
,name VARCHAR(50)
);
INSERT INTO units
(id,name) VALUES 
(1,'unit1'),
(2,'unit2'),
(3,'unit3');

你应该使用Cross Join和Pivot

select 
* 
from 
(
select 
c.id, 
candidate_name, 
cast(null as int) id1, 
name 
from 
candidate c 
CROSS JOIN units u
) src pivot (
max(id1) for name in ([unit1], [unit2], [unit3])
) piv;

使用Dynamic Sql

DECLARE @SQL nvarchar(max);
DECLARE @names nvarchar(1000)= (
SELECT STRING_AGG(concat('[',name,']'),',') 
WITHIN GROUP  (order by id) from units)
set @SQL='select 
* 
from 
(
select 
c.id, 
candidate_name, 
cast(null as int) id1, 
name 
from 
candidate c 
CROSS JOIN units u
) src pivot (
max(id1) for name in ('+ @names +' )
) piv;'
exec(@SQL)

dbfiddle