需求:
ID sigma cpk Xbar
1 sigma值 cpk值 xbar值
2 sigma值 cpk值 xbar值
. .
. .
. .
n sigma值N
我有很多条数据且ID不会重复
但是要显示成下面格式:
ID 1 2 .... N
sigma sigma值 sigma值 .... sigma值N
cpk cpk值 cpk值
Xbar xbar值 xbar值
------------------------------------------------------------------------------------------
SQL codeuse test go -- > --> (Andy)生成测试数据 2008-09-15 Set Nocount On If Object_id ( ' Test ' ) Is Not null Drop Table Test Go Create table Test ( [ ID ] int , [ sigma ] nvarchar ( 50 ), [ cpk ] nvarchar ( 50 ), [ Xbar ] nvarchar ( 50 )) Insert Into Test select 1 ,N ' sigma值 ' ,N ' cpk值 ' ,N ' xbar值 ' union all select 2 ,N ' sigma值 ' ,N ' cpk值 ' ,N ' xbar值 ' Declare @Sql nvarchar ( max ) Select @Sql = Isnull ( @Sql + ' , ' , '' ) + Quotename ( [ ID ] ) From Test Group By [ ID ] Exec (N ' ;With t As ( Select * from Test Unpivot ([Value] For Col In([sigma],[cpk],[Xbar])) UnP ) Select ID=Col, ' + @Sql + ' From t Pivot(Max([Value]) For ID in( ' + @Sql + ' )) P ' ) /* ID 1 2 -------------------------- cpk cpk值 cpk值 sigma sigma值 sigma值 Xbar xbar值 xbar值 */------------------------------------------------------------ 当原表的类型不同时,会出现“类型与 UNPIVOT 列表中指定的其他列的类型冲突。”这时可以创建一个临时表(类型必须一致)来保存原表的数据
---order by charindex(Col,''Xbar,cpk,sigma'')是为了实现自定义排序
- Set Nocount On
- If Object_id('#tb') Is Not null
- drop table #tb
- declare @1 table([ID] int,[sigma] int,[cpk] nvarchar(50),[Xbar] nvarchar(50))
- Insert @1
- select 1,3,N'cpk',N'xbar' union all
- select 9,3,N'cpk',N'xbar' union all
- select 5,3,N'cpk',N'xbar'
- ;
- create table #tb([ID] int IDENTITY(1,1),[sigma] nvarchar(50),[cpk] nvarchar(50),[Xbar] nvarchar(50))
- insert into #tb ([sigma],[cpk],[Xbar])
- Select [sigma],[cpk],[Xbar] from @1 where [sigma]=3
- Declare @Sql nvarchar(max)
- Select @Sql=Isnull(@Sql+',','')+Quotename([ID])
- From #tb Group By [ID]
- Exec (N'
- ;With t As
- (
- Select * from #tb
- Unpivot ([Value] For Col In([cpk],[sigma],[Xbar])) UnP
- )
- Select ID=Col,'+@Sql+' From t
- Pivot(Max([Value]) For ID in('+@Sql+')) P order by charindex(Col,''Xbar,cpk,sigma'')
- ')
- drop table #tb