sql2008行转列

---------不是自己写的
--测试数据
if not object_id(N'T') is null
	drop table T
Go
Create table T([Tdate] Date,[Splan] int,[Ship] int,[Input] int,[Tstock] int)
Insert T
select '20180701',0,0,0,267 union all
select '20180702',0,0,262,483 union all
select '20180703',42,42,642,1083 union all
select '20180704',195,195,828,1716 union all
select '20180705',874,651,997,2062
Go
--测试数据结束
DECLARE @s NVARCHAR(4000) ,
    @s2 NVARCHAR(4000) ,
    @s3 NVARCHAR(4000) ,
    @s4 NVARCHAR(4000)  
SELECT  @s = ISNULL(@s + ',', 'declare ') + '@' + RTRIM(Colid)
        + ' nvarchar(4000)' ,
        @s2 = ISNULL(@s2 + ',', 'select ') + '@' + RTRIM(Colid) + '='''
        + CASE WHEN @s2 IS NOT NULL THEN 'union all select'
               ELSE ' select '
          END + '  Tdate=''' + QUOTENAME(Name, '''') + '''''' ,
        @s3 = ISNULL(@s3, '') + 'select @' + RTRIM(Colid) + '=@' + RTRIM(Colid)
        + '+'',''+quotename([Tdate])+''=''+quotename(' + QUOTENAME(Name)
        + ','''''''')  from T ' ,
        @s4 = ISNULL(@s4 + '+', '') + '@' + RTRIM(Colid)
FROM    syscolumns
WHERE   id = OBJECT_ID('T')
        AND Name NOT IN ( 'Tdate' )  
		ORDER BY colid
EXEC(@s+' '+@s2+' '+@s3+' exec('+@s4+')')  
-------------自己写的

Create table T([Tdate] Date,[Splan] int,[Ship] int,[Input] int,[Tstock] int)
Insert T
select '20180701',0,0,0,267 union all
select '20180702',0,0,262,483 union all
select '20180703',42,42,642,1083 union all
select '20180704',195,195,828,1716 union all
select '20180705',874,651,997,2062
Go
select 1 xh,* into #xh from T
GO
declare @s varchar(8000)
SELECT  @s = isnull(@s + 'union all 
','') 
		+ 'SELECT '''+RTRIM(Name)+''' 字段名,replace(LEFT(xList,LEN(xList)-1),'','','' '') 字段组合值 FROM (SELECT xh,(SELECT cast('+RTRIM(Name)+' as varchar)+'','' FROM #xh WHERE xh=A.xh FOR XML PATH('''')) AS xList FROM #xh A GROUP BY xh) B'+RTRIM(Name)+'
'
FROM    syscolumns
WHERE   id = OBJECT_ID('T')
        ORDER BY colid
--print @s
exec(@s)
GO
drop table #xh