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

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?

CodePudding user response：

In most database systems, you can join tables using a query language like SQL. For example, if you are using a relational database like MySQL, you could use the following query to join the tables together:

SELECT candidate.id, candidate.candidate_name, unit1.name AS unit1, unit2.name AS unit2, unit3.name AS unit3
FROM candidate
LEFT JOIN units AS unit1 ON candidate.id = unit1.id
LEFT JOIN units AS unit2 ON candidate.id = unit2.id
LEFT JOIN units AS unit3 ON candidate.id = unit3.id

This query will create a new table that contains all of the columns from the candidate and units tables. The unit1, unit2, and unit3 columns will contain the names of the units associated with each candidate. If a candidate does not have a unit associated with them, the corresponding cell will be null.

Keep in mind that this is just one way to join these tables together. Depending on your specific needs and the structure of your data, you may need to use a different approach.

CodePudding user response：

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