如何将动态查询列写入 SQL Server 中的行中? [英] How to write dynamic query columns into rows in SQL Server?

问题描述

我在 SQL Server 中有一个问题:如何在查询中将 4 列合并为 1 列.

I have a question about in SQL Server: how to combine 4 columns into 1 column in a query.

表:emp

empid | addr | sal | doj
------+------+-----+------------
  1   | hyd  | 10  | 10-01-1990
  2   | del  | 20  | 12-03-1999
  1   | pune | 50  | 12-03-2017

基于以上数据，我想要如下输出

Based on above data I want output like below

empid | empvalues
------+---------------
1     | hyd
1     | 10
1     | 10-01-1990
2     | del
2     | 20
2     | 12-03-1999
1     | pune
1     | 50
1     | 12-03-2017

我尝试过这样的查询:

select 
    empid, cast (addr as varchar(100)) as empvalues 
from emp

union all

select empid, cast (sal as varchar(100)) as empvalues 
from emp

union all

select empid, cast (doj as varchar(100)) as empvalues 
from emp

这个查询返回了正确的结果，但是由于调用了同一个表 3 次，所以花费了很多时间.

This query is returning the correct result, but it takes a lot of time due to calling same table 3 times.

你能告诉我如何编写任何替代查询来在 SQL Server 中完成这个任务吗?

Can you please tell me how to write any alternative query to achieve this task in SQL Server?

推荐答案

使用交叉应用:

create table emp (empid int, addr varchar(10), sal int, doj datetime) 

insert into emp (empid, addr, sal, doj)
values (1, 'hyd', '10', '10-01-1990'),
  (2, 'del', '20', '12-03-1999'),
  (1, 'pune', '50', '12-03-2017'); 


select empid,empvalues from emp
cross apply (values (cast (addr as varchar(10)))
,(cast (sal as varchar(10)))  
,(Format(doj,'MM-dd-yyyy')))   d(empvalues) 

结果:

empid   empvalues
1   hyd
1   10
1   10-01-1990
2   del
2   20
2   12-03-1999
1   pune
1   50
1   12-03-2017 

更多信息请查看以下文章:

More info check below articles:

docs.microsoft和SQL Server CROSS APPLY 和 OUTER APPLY

这篇关于如何将动态查询列写入 SQL Server 中的行中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！