ＳＱＬ 多列合并为单列
CREATE TABLE A(a1 int null ,a2 int null,a3 int null)
insert into A(a1,a2,a3) VALUES(1,2,3)
select * from A 得到的结果是
1 2 3
想得到的结果是：a1,a2,a3并成一个字段b：
SELECT a1 FROM A
UNION ALL
SELECT a2 FROM A
UNION ALL
SELECT a3 FROM A
1
2
3
即将多列加入行。如何做效率高些。用UNION ALL逐列加入总觉不对。
[解决办法]
if object_id(N'a',N'U') is not null drop table a
CREATE TABLE A(a1 int null ,a2 int null,a3 int null)
insert into A(a1,a2,a3) VALUES(1,2,3)
--语句展示
select name from a
unpivot
( b for name in (a1,a2,a3))as unpiv
--结果展示
/*
name
--------------------------------------------------------------------------------------------------------------------------------
a1
a2
a3
(3 行受影响)
*/
----列多用动态SQL：
CREATE TABLE A(a1 int null ,a2 int null,a3 int null)
insert into A(a1,a2,a3) VALUES(1,2,3)
go
DECLARE @sql VARCHAR(MAX)
SELECT @sql=ISNULL(@sql,'')+','+QUOTENAME(name) FROM syscolumns WHERE id=OBJECT_ID('a')
SET @sql='select [name] from a unpivot( [name] for [x] in ('+STUFF(@sql,1,1,'')+')) unpiv'
EXEC(@sql)
--drop table a
CREATE TABLE A(a1 int null ,a2 int null,a3 int null)
insert into A(a1,a2,a3) VALUES(1,2,3)
go
declare @sql nvarchar(max);
set @sql = '';
select @sql = @sql + ' union all select '+c.name +
' from '+t.name
from sys.tables t
inner join sys.columns c
on t.object_id = c.object_id
where t.name = 'A' --表名
and c.name like 'a%' --字段名
set @sql = STUFF(@sql,1,len(' union all'),'')
--select @sql
exec(@sql)
/*
a1
1
2
3
*/
