Unoivot using SQL

Question

这是代码

create table info
(id smallint Not Null primary key,name char,date_of_birth date, salary int)
insert into info values ('1', 'sara','2019-02-02', '3000')
insert into info values ('2', 'amir','2019-02-02', '2000')
insert into info values ('3', 'barfi','', '')
insert into info values ('4', 'panbe','', '1000')
insert into info values ('5', 'milow','2020-02-02', '')
insert into info values ('6', '','2021-02-02', '3500')
insert into info values ('7', '','2019-02-02', '2020')
insert into info values ('8', 'ggg','2018-02-02', '4500')
insert into info values ('9', '','', '100')
insert into info values ('10', 'jjj','', '5000')

例如，第一行必须是:

1   name   roya
1   date_of_birth   2019-02-02
1   salary   3000
2   name   amir
.
.

Answer 1

一般的解决方案使用union all:

select id, 'name' as which, name as value, 
from info
union all
select id, 'date_of_birth' as which, cast(date_of_birth as varchar(255)), 
from info
union all
select id, 'salary' as which, cast(salary as varchar(255))
from info;