Question

sql多列合并为一列多行

如 表tab 有列id, a, b, c
1 a1 b1 c1
2 a2 b2 c2
查询有显示的是
id s
1 a1
b1
c1

2 a2
b2
c2

Answer 1

SQL SERVER2005以上版本可以
use Tempdb
go
--> -->

if not object_id(N'Tempdb..#') is null
drop table #
Go
Create table #([a] int,[b] nvarchar(2),[c] nvarchar(2),[d] nvarchar(2))
Insert #
select 1,N'a1',N'b1',N'c1' union all
select 2,N'a2',N'b2',N'c2'
Go

Select a=case when ColName='b' then rtrim(a) else '' end,s
from # AS a
UNPIVOT (s FOR ColName IN([b],[c],[d]))AS b

/*
a s
1 a1
b1
c1
2 a2
b2
c2
*/

Answer 2

select * from (select id id,a a from tab UNION All select id id,b b from tab) A order by id ;试试这个吧