用sql把查询结果竖向变成横向 在线等
怎么显示结果为：吴道祥 时间 时间 时间。。。 SQL? 竖向变横向
[解决办法]
行列转换问题,早上刚有人问:
http://bbs.csdn.net/topics/390635760
[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-11-07 13:49:04
-- Version:
-- Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
--Dec 28 2012 20:23:12
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([datetime] datetime,[name] varchar(1))
insert [huang]
select '2012-11-10','a' union all
select '2013-11-10','a'
--------------开始查询--------------------------
declare @s nvarchar(4000)
set @s=''
Select @s=@s+','+quotename([name])+'=max(case when [name]='+quotename([name],'''')+' then [datetime] else 0 end)'
from [huang]
exec('select [name]'+@s+' from [huang] group by [name]')
----------------结果----------------------------
/*
name a a
---- ----------------------- -----------------------
a 2013-11-10 00:00:00.000 2013-11-10 00:00:00.000
*/
create table xz
(col1 varchar(15),col2 varchar(10))
insert into xz
select '2012-11-10','吴道祥' union all
select '2012-11-13','吴道祥' union all
select '2012-12-03','吴道祥' union all
select '2012-12-04','吴道祥' union all
select '2012-12-13','吴道祥'
declare @tsql varchar(6000)
select @tsql=isnull(@tsql+',','')+'['+rtrim(number)+']'
from master.dbo.spt_values
where type='P' and number between 1 and
(select max(c) from (select count(1) 'c' from xz group by col2) t)
select @tsql='select col2,'+@tsql+'
from (select col1,col2,
row_number() over(partition by col2 order by getdate()) ''rn''
from xz) t
pivot(max(col1) for rn in('+@tsql+')) p '
exec(@tsql)
/*
col2 1 2 3 4 5
---------- --------------- --------------- --------------- --------------- ---------------
吴道祥 2012-11-10 2012-11-13 2012-12-03 2012-12-04 2012-12-13
(1 row(s) affected)
*/
