求教，如何将多列数据平铺进一行
表：
列名 no a b c
1 a1 b1 c1
2 a2 b2 c2
3 a3 b3 c3
...
输出格式为：
列名：
输出内容： a1 b1 c1 a2 b2 c2 a3 b3 c3 ...
先谢谢各位高手了！
[解决办法]
declare @v nvarchar(500)
select @v=''
select @v=@v+' '+a+' '+b+' '+c from tab
select @v
select a+' '+b+' '+c+' ' from tab for xml path('')
create table zy
(no int, a varchar(5), b varchar(5), c varchar(5))
insert into zy
select 1, 'a1', 'b1', 'c1' union all
select 2, 'a2', 'b2', 'c2' union all
select 3, 'a3', 'b3', 'c3'
select * from zy
/*
no a b c
----------- ----- ----- -----
1 a1 b1 c1
2 a2 b2 c2
3 a3 b3 c3
(3 row(s) affected)
*/
declare @tsql varchar(6000)
select @tsql='select '
select @tsql=@tsql+' max(da'+rtrim(no)+') da'+rtrim(no)+','
+' max(db'+rtrim(no)+') db'+rtrim(no)+','
+' max(dc'+rtrim(no)+') dc'+rtrim(no)+','
from (select distinct no from zy) z
select @tsql=left(@tsql,len(@tsql)-1)+' from (select no,'
select @tsql=@tsql+' max(case when no='+rtrim(no)+' then a else '''' end) da'+rtrim(no)+','
+' max(case when no='+rtrim(no)+' then b else '''' end) db'+rtrim(no)+','
+' max(case when no='+rtrim(no)+' then c else '''' end) dc'+rtrim(no)+','
from (select distinct no from zy) z
select @tsql=left(@tsql,len(@tsql)-1)+' from zy group by no) t'
exec(@tsql)
/*
da1 db1 dc1 da2 db2 dc2 da3 db3 dc3
----- ----- ----- ----- ----- ----- ----- ----- -----
a1 b1 c1 a2 b2 c2 a3 b3 c3
(1 row(s) affected)
*/
declare @tab table
(
n int,
a nvarchar(50),
b nvarchar(50),
c nvarchar(50)
)
insert into @tab(n,a,b,c)
select 1,'a1','b1','c1' union all
select 2,'a2','b2','c2' union all
select 3,'a3','b3','c3' union all
select 4,'a4','b4','c4'
declare @v nvarchar(500)
declare @sql nvarchar(500)
declare @i int
select @v=''
select @sql='select '
select @i=97
select @v=@v+a+' '+b+' '+c+' ' from @tab
while(1=1)
begin
select @sql=@sql+''''+substring(@v,1,charindex(' ',@v))+''' as '+char(@i)+','
select @v = substring(@v,charindex(' ',@v)+1,len(@v)-charindex(' ',@v))
if(charindex(' ',@v)=0)
begin
select @sql=@sql+''''+ @v+''' as '+char(@i+1)
break
end
select @i=@i+1
end
exec(@sql)