SQL全列转行

2018-07-06 03:24:32 842 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

sql列转行行转列在 SQL 中，转换数据以按列排列的值成为按行排列的值（即所谓的“列转行”或“列转行”）是常见的数据操作需求。这个操作在不同的数据库管理系统中可以通过不同的技术手段来实现。在 SQL 中，行转列（也称为“透视”或“旋转”）的操作是将行数据转换为列数据，这在数据总结和分析中非常有用。不同的数据库系统有不同的方法来实现行转列的操作。操作的数据库，如 Oracle 和 SQL Server，可以直接使用。将多个列的数据合并到一个新的结果集的单一列中。如果你的列数是固定的，可以使用。函数可以实现类似的操作。阅读详情

原数据如下（日期会一直增加）：
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

...全文

SQL(2) 行转列，列转行 sql 刷题1、行转列（sql sever）2、列转行 1、行转列（sql sever） -- 建表 CREATE TABLE StudentScores ( UserName NVARCHAR(20), -- 学生姓名 Subject NVARCHAR(30), -- 科目 Score FL... 阅读详情

追风_GaoWu

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

听雨停了 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

打赏
举报

引用 3 楼 RINK_1 的回复:



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)

你好，改成这样，为什么会报错呢

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+')')

SQL全列转行

相关推荐