34,590
社区成员
发帖
与我相关
我的任务
分享
-> --> 听雨停了-->生成测试数据
if not object_id(N'tab') is null
drop table tab
Go
Create table tab([F_Month] nvarchar(22),[F_MonthTarget] decimal(18,8),[F_UsedValue] int,[F_MonthRate] int)
Insert tab
select N'1月',156003.00,189870,56 union all
select N'2月',166489.00,345678,80 union all
select N'3月',129827.00,345690,37
GO
--测试数据结束
--1.表数据转换类型插入另一个表tab_cast
Select cast(f_month AS VARCHAR(200)) AS f_month,
cast([F_MonthTarget] AS VARCHAR(200)) AS [F_MonthTarget],
cast([F_UsedValue] AS VARCHAR(200)) AS [F_UsedValue],
cast([F_UsedValue] AS VARCHAR(200)) AS [F_MonthRate]
INTO tab_cast
from tab
--2.先列转行插入另一个表tab_unpivot
declare @sql varchar(max)
select @sql=isnull(@sql+',','')+cast(quotename(Name) AS VARCHAR(100))
from syscolumns
where ID=object_id('tab_cast')and Name not in('f_month')
order by Colid
set @sql='select f_month,[title],[cnt] into tab_unpivot from tab_cast unpivot ([cnt] for [title] in('+@sql+'))b'
exec(@sql)
--3.行转列显示得到结果
DECLARE @name VARCHAR(max),@psql VARCHAR(max)
set @name =stuff((SELECT DISTINCT ',['+[f_month]+']' from tab_unpivot for xml PATH('')),1,1,'')
set @psql ='SELECT * from tab_unpivot pivot(max(cnt)for f_month in('+@name+'))a'
EXEC( @psql)
--4.删除表
DROP TABLE tab_cast
DROP TABLE tab_unpivot