--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.实现转置
DECLARE @WO varchar(50)
set @WO='PRD_100000048'
DECLARE @ColumnList varchar(5000)
SELECT @ColumnList=STUFF(( SELECT ',[' + ColoumnName+']'
FROM ( SELECT distinctColoumnName FROMdbo.[TABLEA]
wherewo=@WO
) T1
FOR
XMLPATH('')
),1, 1, '')
print @ColumnList
declare @SQL varchar(max)
set @SQL='
select *from
( select [WO], [ColoumnName], [ColoumnValue]from [TABLEA] where wo='''+@WO+''' ) P
PIVOT(
MAX(ColoumnValue) for p.[ColoumnName] in(
'+@ColumnList+'
)
) AS PVT
'
exec(@SQL)