Is there a way to transpose output of table as column headers?

Question

I have a table candidate

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

and another table units

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

i would like to generate an output as

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

Any way I can achieve this?

Answer 1

your data

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');

you should use Cross Join and 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;

using 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