588
社区成员
发帖
与我相关
我的任务
分享
--测试数据
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)
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
*/
--测试数据
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]
你要这么说那就没办法搞了
--测试数据
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库中 要用到其他库的表就有点麻烦了
--测试数据
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)
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)