Sql 动态的行列转化

中国好梦 2017-10-25 11:03:50
如题:
Year qty money empid qy
2012 20 40 12 20%
2013 16 35 19 60%
2014 15 30 43 50%
...... ........ ....... ... ... .... ....

想得到的结果如题:
2012 2013 2014
qty 20 16 15
money 40 35 30
empid 12 19 43
qy 20% 60% 50%

首先Year是不固定的列 转化为行时也是动态的 有可能还有2015 2016 2017...... 但是qty money empid qy固定的就这几个字段
...全文
286 11 打赏 收藏 转发到动态 举报
写回复
用AI写文章
11 条回复
切换为时间正序
请发表友善的回复…
发表回复
听雨停了 2017-10-25
  • 打赏
  • 举报
回复
引用 2 楼 qq_37170555 的回复:

--测试数据
create table tab
(
	[YEAR] varchar(10),
	qty VARCHAR(20),
	[MONEY] VARCHAR(20),
	empid VARCHAR(20),
	qy VARCHAR(20)
)
insert into tab values('2012','74','83','93','20%')
INSERT INTO tab values('2013','74','84','94','30%')
--测试数据结束

declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from tab2 for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
把上面的set @year =stuff((SELECT DISTINCT ',['+[year]+']' from tab2 for xml PATH('')),1,1,'')中的tab2改成tab,写错了,多写了个2进去
听雨停了 2017-10-25
  • 打赏
  • 举报
回复
逻辑大概就是先把除了year以外的其他字段进行unpivot(列转行)显示,然后再把year字段pivot(行转列)显示。这样就得到了你要的结果了
听雨停了 2017-10-25
  • 打赏
  • 举报
回复

--测试数据
create table tab
(
[YEAR] varchar(10),
qty VARCHAR(20),
[MONEY] VARCHAR(20),
empid VARCHAR(20),
qy VARCHAR(20)
)
insert into tab values('2012','74','83','93','20%')
INSERT INTO tab values('2013','74','84','94','30%')
--测试数据结束

declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']' from tab2 for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
吉普赛的歌 2017-10-25
  • 打赏
  • 举报
回复
USE tempdb
GO
IF OBJECT_ID('t') IS NOT NULL DROP TABLE t
CREATE TABLE t([Year] INT,qty INT,[money] INT,empid INT,qy VARCHAR(20))
INSERT INTO t
SELECT 2012,20,40,12,'20%'
UNION ALL
SELECT 2013,16,35,19,'60%'
UNION ALL
SELECT 2014,15,30,43,'50%'
--SELECT * FROM t
/*
Year        qty         money       empid       qy
----------- ----------- ----------- ----------- --------------------
2012        20          40          12          20%
2013        16          35          19          60%
2014        15          30          43          50%
*/
DECLARE @sql NVARCHAR(MAX),@years1 NVARCHAR(MAX),@years2 NVARCHAR(MAX)
SELECT 
@years1=ISNULL(@years1,'')+',SUM(['+CAST([Year] AS VARCHAR(20))+']) AS ['+CAST([Year] AS VARCHAR(20))+']'
,@years2=ISNULL(@years2,'')+',['+CAST([Year] AS VARCHAR(20))+']'
FROM t GROUP BY [Year]
SELECT @years2=SUBSTRING(@years2,2,LEN(@years2))

SET @sql='
;WITH cte AS (
	SELECT *,CONVERT(INT,REPLACE(qy,''%'','''')) AS qy2 FROM t 	
)
SELECT ''qty'' AS [flag]
'+@years1+'
FROM t PIVOT(MAX(qty) FOR [year] IN ('+@years2+')) t2
UNION ALL
SELECT ''money'' AS [flag]
'+@years1+'
FROM t PIVOT(MAX([money]) FOR [year] IN ('+@years2+')) t2
UNION ALL
SELECT ''empid'' AS [flag]
'+@years1+'
FROM t PIVOT(MAX([empid]) FOR [year] IN ('+@years2+')) t2
UNION ALL
SELECT ''qy'' AS [flag]
'+@years1+'
FROM cte PIVOT(MAX([qy2]) FOR [year] IN ('+@years2+')) t2
'
EXEC (@sql)
/*
flag  2012        2013        2014
----- ----------- ----------- -----------
qty   20          16          15
money 40          35          30
empid 12          19          43
qy    20          60          50
*/
听雨停了 2017-10-25
  • 打赏
  • 举报
回复
引用 9 楼 y1466799426 的回复:
[quote=引用 8 楼 qq_37170555 的回复:] [quote=引用 5 楼 y1466799426 的回复:] [quote=引用 4 楼 qq_37170555 的回复:] [quote=引用 2 楼 qq_37170555 的回复:]

--测试数据
create table tab
(
	[YEAR] varchar(10),
	qty VARCHAR(20),
	[MONEY] VARCHAR(20),
	empid VARCHAR(20),
	qy VARCHAR(20)
)
insert into tab values('2012','74','83','93','20%')
INSERT INTO tab values('2013','74','84','94','30%')
--测试数据结束

declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from tab2 for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
把上面的set @year =stuff((SELECT DISTINCT ',['+[year]+']' from tab2 for xml PATH('')),1,1,'')中的tab2改成tab,写错了,多写了个2进去[/quote] 你所说的tab 如果是临时表 这个方法就不可以了 只能是实体表吗???[/quote] 临时表也是可以得,像下面这样

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
--必须把临时表建立在tempdb的数据库里,不然查询不到#tab表的列字段名	
USE tempdb
GO	
create table #tab
(
    [YEAR] varchar(10),
    qty VARCHAR(20),
    [MONEY] VARCHAR(20),
    empid VARCHAR(20),
    qy VARCHAR(20)
)
insert into #tab values('2012','74','83','93','20%')
INSERT INTO #tab values('2013','74','84','94','30%')
--测试数据结束
 
declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from tempdb.dbo.syscolumns
where ID=object_id('#tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from #tab for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from #tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
[/quote] 这样是可以的 但是真正用到项目中 把临时表新建到tempdb库中 要用到其他库的表就有点麻烦了[/quote] 你要这么说那就没办法搞了
吉普赛的歌 2017-10-25
  • 打赏
  • 举报
回复
引用 9 楼 y1466799426 的回复:
这样是可以的 但是真正用到项目中 把临时表新建到tempdb库中 要用到其他库的表就有点麻烦了
他就是取列用到。 难道除了年份之外, 其它的列固定不下来? --qty money empid qy 能够固定下来的话, 根本不需要取列名这一步啊。
中国好梦 2017-10-25
  • 打赏
  • 举报
回复
引用 8 楼 qq_37170555 的回复:
[quote=引用 5 楼 y1466799426 的回复:] [quote=引用 4 楼 qq_37170555 的回复:] [quote=引用 2 楼 qq_37170555 的回复:]

--测试数据
create table tab
(
	[YEAR] varchar(10),
	qty VARCHAR(20),
	[MONEY] VARCHAR(20),
	empid VARCHAR(20),
	qy VARCHAR(20)
)
insert into tab values('2012','74','83','93','20%')
INSERT INTO tab values('2013','74','84','94','30%')
--测试数据结束

declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from tab2 for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
把上面的set @year =stuff((SELECT DISTINCT ',['+[year]+']' from tab2 for xml PATH('')),1,1,'')中的tab2改成tab,写错了,多写了个2进去[/quote] 你所说的tab 如果是临时表 这个方法就不可以了 只能是实体表吗???[/quote] 临时表也是可以得,像下面这样

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
--必须把临时表建立在tempdb的数据库里,不然查询不到#tab表的列字段名	
USE tempdb
GO	
create table #tab
(
    [YEAR] varchar(10),
    qty VARCHAR(20),
    [MONEY] VARCHAR(20),
    empid VARCHAR(20),
    qy VARCHAR(20)
)
insert into #tab values('2012','74','83','93','20%')
INSERT INTO #tab values('2013','74','84','94','30%')
--测试数据结束
 
declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from tempdb.dbo.syscolumns
where ID=object_id('#tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from #tab for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from #tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
[/quote] 这样是可以的 但是真正用到项目中 把临时表新建到tempdb库中 要用到其他库的表就有点麻烦了
听雨停了 2017-10-25
  • 打赏
  • 举报
回复
引用 5 楼 y1466799426 的回复:
[quote=引用 4 楼 qq_37170555 的回复:] [quote=引用 2 楼 qq_37170555 的回复:]

--测试数据
create table tab
(
	[YEAR] varchar(10),
	qty VARCHAR(20),
	[MONEY] VARCHAR(20),
	empid VARCHAR(20),
	qy VARCHAR(20)
)
insert into tab values('2012','74','83','93','20%')
INSERT INTO tab values('2013','74','84','94','30%')
--测试数据结束

declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from tab2 for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
把上面的set @year =stuff((SELECT DISTINCT ',['+[year]+']' from tab2 for xml PATH('')),1,1,'')中的tab2改成tab,写错了,多写了个2进去[/quote] 你所说的tab 如果是临时表 这个方法就不可以了 只能是实体表吗???[/quote] 临时表也是可以得,像下面这样

--测试数据
IF OBJECT_ID('tempdb..#tab') IS NOT NULL
	DROP TABLE #tab
--必须把临时表建立在tempdb的数据库里,不然查询不到#tab表的列字段名	
USE tempdb
GO	
create table #tab
(
    [YEAR] varchar(10),
    qty VARCHAR(20),
    [MONEY] VARCHAR(20),
    empid VARCHAR(20),
    qy VARCHAR(20)
)
insert into #tab values('2012','74','83','93','20%')
INSERT INTO #tab values('2013','74','84','94','30%')
--测试数据结束
 
declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from tempdb.dbo.syscolumns
where ID=object_id('#tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from #tab for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from #tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
顺势而为1 2017-10-25
  • 打赏
  • 举报
回复


IF not Object_id('Tempdb..#Tmp_Data') is null
     DROP TABLE #Tmp_Data

GO
CREATE TABLE #Tmp_Data (
          year int,
          qty int,
		  money int,
		  empid int,
		  qy varchar(10))
GO
		  
INSERT INTO #Tmp_Data
Select 2012,20,40,12,'20%' union 
Select 2013,16,35,19,'60%' union
Select 2014,15,30,43,'50%'


DECLARE @sql_qty NVARCHAR(max)=''
DECLARE @sql_money NVARCHAR(max)=''
DECLARE @sql_empid NVARCHAR(max)=''
DECLARE @sql_qy NVARCHAR(max)=''

DECLARE @minYear varchar(4)
DECLARE @maxYear varchar(4)
SELECT @maxYear=max(year),@minYear=min(year) FROM #Tmp_Data 


 
WHILE @maxYear>=@minYear
    Begin
			select @sql_qty=',max(case when year='+@maxYear+' then qty else 0 end) as ['+@maxYear+']'+@sql_qty
			select @sql_money=',max(case when year='+@maxYear+' then qty else 0 end) as ['+@maxYear+']'+@sql_money
			select @sql_empid=',max(case when year='+@maxYear+' then qty else 0 end) as ['+@maxYear+']'+@sql_empid
			select @sql_qy=',max(case when year='+@maxYear+' then qty else 0 end) as ['+@maxYear+']'+@sql_qy

			set @maxYear=@maxYear-1
	End

DECLARE @sql nvarchar(max)
SET @SQL='SELECT '''+'qty'+''''+@sql_qty +  
         ' From #Tmp_Data union Select '''+'money'+''''+@sql_money+
		 ' From #Tmp_Data union Select '''+'empid'+''''+@sql_empid+
		 ' From #Tmp_Data union Select '''+'qy'+''''+@sql_qy +
		 ' From #Tmp_Data '

EXEC(@SQL)
 
      2012        2013        2014
----- ----------- ----------- -----------
empid 20          16          15
money 20          16          15
qty   20          16          15
qy    20          16          15

(4 row(s) affected)


中国风 2017-10-25
  • 打赏
  • 举报
回复
中国好梦 2017-10-25
  • 打赏
  • 举报
回复
引用 4 楼 qq_37170555 的回复:
[quote=引用 2 楼 qq_37170555 的回复:]

--测试数据
create table tab
(
	[YEAR] varchar(10),
	qty VARCHAR(20),
	[MONEY] VARCHAR(20),
	empid VARCHAR(20),
	qy VARCHAR(20)
)
insert into tab values('2012','74','83','93','20%')
INSERT INTO tab values('2013','74','84','94','30%')
--测试数据结束

declare @sql nvarchar(4000),@title Nvarchar(400),@year NVARCHAR(200)
select @title=isnull(@title+',','')+quotename(Name)
from syscolumns
where ID=object_id('tab')and Name not in('year')
order by Colid
PRINT @title
set @year =stuff((SELECT DISTINCT ',['+[year]+']'  from tab2 for xml PATH('')),1,1,'')
PRINT @year
set @sql='SELECT * from(select [year],[title],cnt from tab unpivot ([cnt] for [title] in('+@title+'))b)a pivot(max(cnt)for [year] in('+@year+'))a'
PRINT @sql
exec(@sql)
把上面的set @year =stuff((SELECT DISTINCT ',['+[year]+']' from tab2 for xml PATH('')),1,1,'')中的tab2改成tab,写错了,多写了个2进去[/quote] 你所说的tab 如果是临时表 这个方法就不可以了 只能是实体表吗???

588

社区成员

发帖
与我相关
我的任务
社区描述
提出问题
其他 技术论坛(原bbs)
社区管理员
  • community_281
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧