SQL SERVER 实现转置功能

来源：互联网发布：手机蓝牙打印机软件编辑：程序博客网时间：2024/06/05 09:57

--1生成测试表
CREATE TABLE [dbo].[TABLEA](
[WO] [varchar](50) NOT NULL,
[ColoumnName] [varchar](50) NULL,
[ColoumnValue] [varchar](50) NULL,

)
insert into [TABLEA]
values('PRD_100000048','SN','SN001')
insert into [TABLEA]
values('PRD_100000048','PO','PO001')
insert into [TABLEA]
values('PRD_100000048','Version','VersionO001')

select * from [TABLEA]

--2.实现转置

IF(OBJECT_ID(N'tempdb..#TABLEA') is NOT NULL) drop table #TABLEA
IF(OBJECT_ID(N'tempdb..##TABLEA') is NOT NULL) drop table ##TABLEA
DECLARE @WO varchar(50)
set @WO='PRD_100000048'

DECLARE @ColumnList varchar(5000)
SELECT @ColumnList=STUFF(( SELECT ',' + 'MAX(case when ColoumnName='''+ColoumnName+''' then ColoumnValue end) as '''+ColoumnName+''' '
FROM ( SELECT distinct ColoumnName FROM dbo.[TABLEA]
where wo=@WO
) T1
FOR
XML PATH('')
), 1, 1, '')

declare @SQL varchar(max)
set @SQL='
select wo,
'+@ColumnList+'
into ##TABLEA
FROM [TABLEA] where wo='''+@WO+'''
group by wo
'
exec(@SQL)
select * into #TABLEA from ##TABLEA
IF(OBJECT_ID(N'tempdb..##TABLEA') is NOT NULL) drop table ##TABLEA --全局临时表不能长时间存在，及时删除

--3.得到可利用的临时表
select * from #TABLEA

0 0