是否可以将第一列转换为表头,第二列转换为行?请参考下面的示例
Type | qty (Header)
Apple 4
Orange 10
Grapes 5
Apple | Orange | Grapes (Header)
4 10 5
是否可以将第一列转换为表头,第二列转换为行?请参考下面的示例
Type | qty (Header)
Apple 4
Orange 10
Grapes 5
Apple | Orange | Grapes (Header)
4 10 5
create table #fruits ([type] varchar(10), quantity int)
insert into #fruits select 'Apple',4 union all select 'Orange', 10 union all select 'Grapes', 5
--首先获取唯一的[result]集合
declare @sqlStr varchar(max)
select @sqlStr = '['+
replace(
stuff((select distinct ','+[type] as [text()]
from #fruits s
for xml path ('')) ,1,1,'')
,',','],[') + ']'
from #fruits s1
select @sqlStr
--组装动态查询
declare @sqlStr1 varchar(max)
set @sqlStr1 = 'select *
from #fruits a
pivot (sum (quantity) for [type] in ('+@sqlStr+')
) p'
exec (@sqlStr1)
declare @sqlStr1 varchar(max)
set @sqlStr1 = 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[TblFruits]'') AND type in (N''U''))
DROP TABLE [dbo].[TblFruits]
select *
into TblFruits
from #fruits a
pivot (sum (quantity) for [type] in ('+@sqlStr+')
) p'
-- run dynamic query
exec (@sqlStr1)
select @script = STUFF( SELECT qty + ' AS ' + type + ', ' from table_orig);
EXEC (@script);
SET @sqltxt = null;
SELECT GROUP_CONCAT(CONCAT( qty, ' AS ', type, ' ') ) INTO @sqltxt
FROM table_orig;
SET @sqltxt = concat('SELECT ', @sqltxt , ' FROM DUAL');
PREPARE stmt FROM @sqltxt;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
祝你好运,