SQL SERVER 转置功能
需积分: 5 78 浏览量
2017-12-26
17:01:31
上传
评论
收藏 21KB DOCX 举报
--转置功能
--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.得到可利用的临时表
--1 生成测试表
CREATE TABLE [dbo].[TABLEA](
[WO] [varchar](50) NOT NULL,
资源评论
