22,300
社区成员
发帖
与我相关
我的任务
分享SQL全列转行
原数据如下（日期会一直增加）：
Tdate Splan Ship Input Tstock
20180701 0 0 0 267
20180702 0 0 262 483
20180703 42 42 642 1083
20180704 195 195 828 1716
20180705 874 651 997 2062
求一语句转成以下数据
Tdate 20180701 20180702 20180703 20180704 20180705
Splan 0 0 42 195 874
Ship 0 0 42 195 651
Input 0 262 642 828 997
Tstock 267 483 1083 1716 2062
Tdate Splan Ship Input Tstock
20180701 0 0 0 267
20180702 0 0 262 483
20180703 42 42 642 1083
20180704 195 195 828 1716
20180705 874 651 997 2062
求一语句转成以下数据
Tdate 20180701 20180702 20180703 20180704 20180705
Splan 0 0 42 195 874
Ship 0 0 42 195 651
Input 0 262 642 828 997
Tstock 267 483 1083 1716 2062
...全文
请发表友善的回复…
发表回复
听雨停了 2018-07-12
- 打赏
- 举报
use Tempdb
go
--> --> 听雨停了-->生成测试数据
if not object_id(N'Tempdb..#tab') is null
drop table #tab
Go
Create table #tab([Tdate] Date,[Splan] int,[Ship] int,[Input] int,[Tstock] int)
Insert #tab
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 * from #tab
--测试数据结束
--先列转行，再行转列，就可以得到数据了
declare @tdate nvarchar(4000),@title NVARCHAR(MAX), @sql NVARCHAR(max)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tempdb..#tab')and Name not in('tdate')
order by Colid
SELECT @tdate= STUFF((SELECT DISTINCT ',['+cast(tdate AS VARCHAR(20))+']' FROM #tab FOR XML PATH('')),1,1,'')
PRINT @title
PRINT @tdate
set @sql='select * from ( select tdate,title,num from #tab unpivot ([num] for [title] in('+@title+'))b) up pivot(max(num) for tdate in ('+ @tdate+') ) a'
PRINT @sql
exec(@sql)
title 2018-07-01 2018-07-02 2018-07-03 2018-07-04 2018-07-05
--------------------------------------------------------------------------------- ----------- ----------- ----------- ----------- -----------
Input 0 262 642 828 997
Ship 0 0 42 195 651
Splan 0 0 42 195 874
Tstock 267 483 1083 1716 2062
追风_GaoWu 2018-07-12
- 打赏
- 举报
你好，改成这样，为什么会报错呢
SELECT * FROM (SELECT 'SPLAN' AS TDATE,TDATE AS NEW_TDATE,Splan FROM tfg_sta where tdate>='20180701') AS A
PIVOT (MAX(SPLAN) FOR NEW_TDATE) AS B
UNION ALL
SELECT *
FROM (SELECT 'SHIP' AS TDATE,TDATE AS NEW_TDATE,SHIP FROM tfg_sta where tdate>='20180701') AS A
PIVOT (MAX(SHIP) FOR NEW_TDATE) AS B
UNION ALL
SELECT *
FROM (SELECT 'Input' AS TDATE,TDATE AS NEW_TDATE,Input FROM tfg_sta where tdate>='20180701') AS A
PIVOT (MAX(Input) FOR NEW_TDATE) AS B
UNION ALL
SELECT *
FROM (SELECT 'Tstock' AS TDATE,TDATE AS NEW_TDATE,Tstock FROM tfg_sta where tdate>='20180701') AS A
PIVOT (MAX(Tstock) FOR NEW_TDATE) AS B
RINK_1 2018-07-08
- 打赏
- 举报
DECLARE @STR VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)
SELECT @STR=ISNULL(@STR+',','')+QUOTENAME(TDATE,'[') FROM (SELECT DISTINCT TDATE FROM TABLE) AS A
SET @SQL='SELECT * FROM (SELECT ''SPLAN'' AS TDATE,TDATE AS NEW_TDATE,Splan FROM TABLE) AS A
PIVOT (MAX(SPLAN) FOR NEW_TDATE IN ('+@STR+')) AS B
UNION ALL
SELECT *
FROM (SELECT ''SHIP'' AS TDATE,TDATE AS NEW_TDATE,SHIP FROM TABLE) AS A
PIVOT (MAX(SHIP) FOR NEW_TDATE IN ('+@STR+')) AS B
UNION ALL
SELECT *
FROM (SELECT ''Input'' AS TDATE,TDATE AS NEW_TDATE,Input FROM TABLE) AS A
PIVOT (MAX(Input) FOR NEW_TDATE IN ('+@STR+')) AS B
UNION ALL
SELECT *
FROM (SELECT ''Tstock'' AS TDATE,TDATE AS NEW_TDATE,Tstock FROM TABLE) AS A
PIVOT (MAX(Tstock) FOR NEW_TDATE IN ('+@STR+')) AS B'
EXEC(@SQL)
星先 2018-07-06
- 打赏
- 举报
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
借了楼上部分
二月十六 2018-07-06
- 打赏
- 举报
--测试数据
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+')')
