英文:
Columns into rows with identify in SQL Server
问题
You can achieve the desired result without calling the same table multiple times using the UNPIVOT operation in SQL Server. Here's an alternative query:
SELECT empid, empaddrvalues, EmpFields, EmpFieldFlag
FROM (
SELECT empid, addr1, addr2, addr3
FROM emp
) AS Source
UNPIVOT (
empaddrvalues FOR EmpFields IN (addr1, addr2, addr3)
) AS Unpivoted
CROSS APPLY (
VALUES
(1, 'addr1'),
(2, 'addr2'),
(3, 'addr3')
) AS EmpFieldFlags(EmpFieldFlag, EmpFields)
ORDER BY empid, EmpFieldFlag;
This query unpivots the addr1, addr2, and addr3 columns into rows, assigns the corresponding EmpFields and EmpFieldFlag values, and then orders the result as specified in your example. It should be more efficient than the previous query since it doesn't call the same table multiple times.
英文:
I have a question about SQL Server: how to combine three columns into one column with identify in a query.
Table: emp
empid | addr1 | addr2 | addr3
------+-------+-------+------
1 | hyd | chen | pune
2 | del | hyd | nagp
1 | pune | bang | keral
Based on above data I want output like below
empid | empaddrvalues | EmpFields | EmpFieldFlag
------+---------------+-----------+--------------
1 | hyd | addr1 | 1
1 | chen | addr2 | 2
1 | pune | addr3 | 3
2 | del | addr1 | 1
2 | hyd | addr2 | 2
2 | nagp | addr3 | 3
1 | pune | addr1 | 1
1 | bang | addr2 | 2
1 | keral | addr3 | 3
I tried with a query like this:
select
empid,
cast (addr1 as varchar(100)) as empaddrvalues,
'addr1' as EmpFields, '1' as EmpFieldFlag
from
emp
union all
select
empid,
cast (addr2 as varchar(100)) as empaddrvalues,
'addr2' as EmpFields, '2' as EmpFieldFlag
from
emp
union all
select
empid,
cast (addr3 as varchar(100)) as empaddrvalues,
'addr3' as EmpFields, '3' as EmpFieldFlag
from
emp
This query is returning the correct result, but it takes a lot of time due to calling same table 3 times.
Can you please tell me how to write any alternative query to achieve this task in SQL Server?
答案1
得分: 1
Unpivot by cross applying with 3 values (1, 2, 3) and CASE on that value to get the corresponding address.
create table dbo.employee
(
empid int,
addr1 varchar(50),
addr2 varchar(50),
addr3 varchar(50)
)
go
insert into dbo.employee(empid, addr1, addr2, addr3)
values
(1, 'hyd', 'chen', 'pune'),
(2, 'del', 'hyd', 'nagp'),
(1, 'pune', 'bang', 'keral');
go
select
e.empid,
case nums.n when 1 then addr1 when 2 then addr2 when 3 then addr3 end as empaddrvalues,
--concat('???', nums.n) as xyz,
nums.n as EmpFieldFlag
from dbo.employee as e
cross apply (values(1), (2), (3)) as nums(n);
go
drop table dbo.employee
go
英文:
Unpivot by cross applying with 3 values (1, 2, 3) and CASE on that value to get the corresponding address.
create table dbo.employee
(
empid int,
addr1 varchar(50),
addr2 varchar(50),
addr3 varchar(50)
)
go
insert into dbo.employee(empid, addr1, addr2, addr3)
values
(1, 'hyd', 'chen', 'pune'),
(2, 'del', 'hyd', 'nagp'),
(1, 'pune', 'bang', 'keral');
go
select
e.empid,
case nums.n when 1 then addr1 when 2 then addr2 when 3 then addr3 end as empaddrvalues,
--concat('???', nums.n) as xyz,
nums.n as EmpFieldFlag
from dbo.employee as e
cross apply (values(1), (2), (3)) as nums(n);
go
drop table dbo.employee
go
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论