sql - 获取列名称和值的sql查询

  显示原文与译文双语对照的内容
142 2

我有一个类似这样的表格:

emplid year JAN FEB MAR APR MAY
------ ---- --- --- --- --- ---
1234 2018 x x x Retire
5678 2018 x Terminated
9876 2018 x x x x Transfer
5432 2018 x x x x x

我想返回一个如下所示的表:

emplid year month Reason
------ ---- ----- ------
1234 2018 APR Retire
5678 2018 FEB Terminated
9876 2018 MAY Transfer

基本上我想找到一个雇员退出。退出。被终止等的年份和月份,等等。还有什么要完成的?我正在使用 SQL Server 。如果你需要其他信息,请告诉我。我不清楚怎么做。在我的专业水平上。谢谢

时间:原作者:0个回答

93 2

使用 APPLY 有一个非常方便的地方:

select t.empid, t.year, v.mon, v.reason
from t cross apply
 (values ('JAN', jan), ('FEB', feb), ('MAR', mar), ('APR', apr), ('MAY', may)
 ) v(mon, reason)
where reason <> 'x';
原作者:
83 5

从以下查询扩展到query月rest月

SELECT emplid,year,
CASE WHEN JAN <> 'x' THEN 'JAN' 
 WHEN FEB <> 'x' THEN 'FEB'
 WHEN MAR <> 'x' THEN 'MAR'
 WHEN APR <> 'x' THEN 'APR'
 WHEN MAY <> 'x' THEN 'MAY'
--Rest of months
END [month],
REPLACE(JAN+FEB+MAR+APR+MAY,'x','') Reason
FROM tableName
原作者:
135 1

不过,你的表没有标准化。可以使用:

select emplid, year, 
case 
when not (JAN = 'x')
 then 'JAN'
when not (FEB = 'x')
 then 'FEB'
as month

原因也是如此。

原作者:
82 5

请尝试使用

select t1.emplid,t1.year,
(
 CASE 
 WHEN t1.JAN <> 'x' AND t1.JAN <> '' THEN 'JAN'
 WHEN t1.FEB <> 'x' AND t1.FEB <> '' THEN 'FEB'
 WHEN t1.MAR <> 'x' AND t1.MAR <> '' THEN 'MAR'
 WHEN t1.APR <> 'x' AND t1.APR <> '' THEN 'APR'
 WHEN t1.MAY <> 'x' AND t1.MAY <> '' THEN 'MAY'
 end
) as month,
(
 CASE 
 WHEN t1.JAN <> 'x' AND t1.JAN <> '' THEN t1.JAN
 WHEN t1.FEB <> 'x' AND t1.FEB <> '' THEN t1.FEB
 WHEN t1.MAR <> 'x' AND t1.MAR <> '' THEN t1.MAR
 WHEN t1.APR <> 'x' AND t1.APR <> '' THEN t1.APR
 WHEN t1.MAY <> 'x' AND t1.MAY <> '' THEN t1.MAY 
 end
) as Reason
 from table1 t1
 where Reason is not null
原作者:
...