数据:
rkey param_code param_value
1 a +0.5
1 a -0.8
2 b 是
2 b 否
3 c 1
3 c 2实现如下效果:
rkey a b c
1 +0.5 是 1
2 -0.5 否 2 万分感谢~
rkey param_code param_value
1 a +0.5
1 a -0.8
2 b 是
2 b 否
3 c 1
3 c 2实现如下效果:
rkey a b c
1 +0.5 是 1
2 -0.5 否 2 万分感谢~
Declare @S Nvarchar(4000)
Select @S = ' Select rkey'
Select @S = @S + ' , Max(Case param_code When ''' + param_code + ''' Then param_value Else '''' As ' + param_code
From TableName Group By param_code
Select @S = @S + ' From TableName Group By rkey Order By rkey'
EXEC(@S)
(rkey Int,
param_code Varchar(10),
param_value Nvarchar(10))
Insert TEST Select 1, 'a', N'+0.5'
Union All Select 1, 'a', N'-0.8'
Union All Select 2, 'b', N'是'
Union All Select 2, 'b', N'否'
Union All Select 3, 'c', N'1'
Union All Select 3, 'c', N'2'
GO
Select ID = Identity(Int, 1, 1), * Into #T From TEST
Declare @S1 Nvarchar(4000), @S2 Nvarchar(4000)
Select @S1 = ' Select 1 As rkey', @S2 = ' Select 2'
Select @S1 = @S1 + ' , Max(Case When param_code = ''' + param_code + ''' And ID % 2 = 1 Then param_value Else '''' End)As ' + param_code,
@S2 = @S2 + ' , Max(Case When param_code = ''' + param_code + ''' And ID % 2 = 0 Then param_value Else '''' End)As ' + param_code
From TEST Group By param_code
Select @S1 = @S1 + ' From #T', @S2 = ' Union ' + @S2 + ' From #T'
EXEC(@S1 + @S2)
Drop Table #T
GO
Drop Table TEST
--Result
/*
rkey a b c
1 +0.5 是 1
2 -0.8 否 2
*/
1 a +0.5
1 a -0.8
1 a +0.7
1 a -0.6
2 b 是
2 b 否
3 c 1
3 c 2
3 c 3結果是怎樣的?
-------------------- ----------- ----------- ----------- -----------
x 1 2 3 4
y 5 6 7 8
z 9 10 11 12(3 row(s) affected)A x y z
-------------------- ---------- ---------- ----------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12
--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
--生成中间数据表
declare @s varchar(8000)
set @s='create table test2(A varchar(20)'
select @s=@s+','+A+' varchar(10)' from test1
set @s=@s+')'
exec(@s)--借助中间表实现行列转换
declare @name varchar(20)declare t_cursor cursor for
select name from syscolumns
where id=object_id('test1') and colid>1 order by colidopen t_cursorfetch next from t_cursor into @namewhile @@fetch_status=0
begin
exec('select '+@name+' as t into test3 from test1')
set @s='insert into test2 select '''+@name+''''
select @s=@s+','''+rtrim(t)+'''' from test3
exec(@s)
exec('drop table test3')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互换处理结果
select * from test1
select * from test2--删除表
drop table test1
drop table test2
drop table tb
gocreate table tb(rkey int,param_code varchar(10),param_value varchar(10))
insert into tb(rkey,param_code,param_value) values(1, 'a', '+0.5')
insert into tb(rkey,param_code,param_value) values(1, 'a', '-0.8')
insert into tb(rkey,param_code,param_value) values(2, 'b', '是')
insert into tb(rkey,param_code,param_value) values(2, 'b', '否')
insert into tb(rkey,param_code,param_value) values(3, 'c', '1')
insert into tb(rkey,param_code,param_value) values(3, 'c', '2')select id=identity(int,1,1) , * into test from
(
select max(a) a, max(b) b, max(c) c from
(
select rkey,
max(case when param_code = 'a' then param_value end) as a,
max(case when param_code = 'b' then param_value end) as b,
max(case when param_code = 'c' then param_value end) as c
from tb
group by rkey
) m
union all
select max(a) a, max(b) b, max(c) c from
(
select rkey,
min(case when param_code = 'a' then param_value end) as a,
min(case when param_code = 'b' then param_value end) as b,
min(case when param_code = 'c' then param_value end) as c
from tb
group by rkey
) n
) tselect * from test
drop table tb,test/*
id a b c
----------- ---------- ---------- ----------
1 -0.8 是 2
2 +0.5 否 1(所影响的行数为 2 行)
*/
drop table tb
gocreate table tb(rkey int,param_code varchar(10),param_value varchar(10))
insert into tb(rkey,param_code,param_value) values(1, 'a', '+0.5')
insert into tb(rkey,param_code,param_value) values(1, 'a', '-0.8')
insert into tb(rkey,param_code,param_value) values(2, 'b', '是')
insert into tb(rkey,param_code,param_value) values(2, 'b', '否')
insert into tb(rkey,param_code,param_value) values(3, 'c', '1')
insert into tb(rkey,param_code,param_value) values(3, 'c', '2')select id=identity(int,1,1) , * into test from
(
select max(a) a, max(b) b, max(c) c from
(
select rkey,
min(case when param_code = 'a' then param_value end) as a,
min(case when param_code = 'b' then param_value end) as b,
min(case when param_code = 'c' then param_value end) as c
from tb
group by rkey
) n
union all
select max(a) a, max(b) b, max(c) c from
(
select rkey,
max(case when param_code = 'a' then param_value end) as a,
max(case when param_code = 'b' then param_value end) as b,
max(case when param_code = 'c' then param_value end) as c
from tb
group by rkey
) m
) tselect * from test
drop table tb,test/*
id a b c
----------- ---------- ---------- ----------
1 +0.5 否 1
2 -0.8 是 2(所影响的行数为 2 行)
*/
假如數據是這樣的rkey param_code param_value
1 a +0.5
1 a -0.8
1 a +0.7
1 a -0.6
2 b 是
2 b 否
3 c 1
3 c 2
3 c 3結果是怎樣的?
1 +0.5 是 1
2 -0.8 否 2
3 +0.7 null 3
4 -0.6 null
如果你也找不到,可以试试
1 把数据导入到excel表sheet1中
2 在sheet1中复制全部数据到sheet2 ,右键 ‘选择性粘贴’----〉转置
drop table tb
gocreate table tb(rkey int,param_code varchar(10),param_value varchar(10))
insert into tb(rkey,param_code,param_value) values(1, 'a', '+0.5')
insert into tb(rkey,param_code,param_value) values(1, 'a', '-0.8')
insert into tb(rkey,param_code,param_value) values(2, 'b', '是')
insert into tb(rkey,param_code,param_value) values(2, 'b', '否')
insert into tb(rkey,param_code,param_value) values(3, 'c', '1')
insert into tb(rkey,param_code,param_value) values(3, 'c', '2')
goselect px=(select count(1) from tb where rkey=a.rkey and param_code = a.param_code and param_value<a.param_value)+1 , * into test from tb a order by rkey , param_code , pxdeclare @sql varchar(8000)
set @sql = 'select px'
select @sql = @sql + ' , min(case param_code when ''' + param_code + ''' then param_value end) [' + param_code + ']'
from (select distinct param_code from test) as a
set @sql = @sql + ' from test group by px'
exec(@sql) drop table tb,test/*
px a b c
----------- ---------- ---------- ----------
1 +0.5 否 1
2 -0.8 是 2
*/
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select 'x',1,2 ,3 ,4
insert into test1 select 'y',5,6 ,7 ,8
insert into test1 select 'z',9,10,11,12
SELECT * FROM
(
SELECT A, AB, _value
FROM
(SELECT A, b, c, d, e
FROM test1) p
UNPIVOT
(_value FOR AB IN
(b, c, d, e)
)AS unpvt
) TB
PIVOT
(MAX(_value)
FOR A IN ([X],[Y],[Z])
) AS PIT/*
A X Y Z
--- ----- ---- ------
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12*/
drop table tb
gocreate table tb(rkey int,param_code varchar(10),param_value varchar(10))
insert into tb(rkey,param_code,param_value) values(1, 'a', '+0.5')
insert into tb(rkey,param_code,param_value) values(1, 'a', '-0.8')
insert into tb(rkey,param_code,param_value) values(2, 'b', '是')
insert into tb(rkey,param_code,param_value) values(2, 'b', '否')
insert into tb(rkey,param_code,param_value) values(3, 'c', '1')
insert into tb(rkey,param_code,param_value) values(3, 'c', '2')
godeclare @sql varchar(8000)
set @sql = 'select px'
select @sql = @sql + ' , min(case param_code when ''' + param_code + ''' then param_value end) [' + param_code + ']'
from (select distinct param_code from (select px=(select count(1) from tb where rkey=a.rkey and param_code = a.param_code and param_value<a.param_value)+1 , * from tb a ) t) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where rkey=a.rkey and param_code = a.param_code and param_value<a.param_value)+1 , * from tb a ) m group by px'
exec(@sql) drop table tb/*
px a b c
----------- ---------- ---------- ----------
1 +0.5 否 1
2 -0.8 是 2
*/
(rkey Int,
param_code Varchar(10),
param_value Nvarchar(10))
Insert TEST Select 1, 'a', N'+0.5'
Union All Select 1, 'a', N'-0.8'
Union All Select 1, 'a', N'+0.6'
Union All Select 1, 'a', N'-0.7'
Union All Select 2, 'b', N'是'
Union All Select 2, 'b', N'否'
Union All Select 3, 'c', N'1'
Union All Select 3, 'c', N'2'
Union All Select 3, 'c', N'3'
GO
Select ID = Identity(Int, 1, 1), * Into #T From TEST
Select ID2 = (Select Count(ID) From #T Where ID <= A.ID And param_code = A.param_code), * Into #T2 From #T ADeclare @S Nvarchar(4000), @I Int, @Row Int
Select @S = '' ,@I = 1, @Row = Max(ID2) From #T2
While @I <= @Row
Begin
Select @S = @S + ' Union All Select ' + Rtrim(@I) + ' As rkey'
Select @S = @S + ' , Max(Case When param_code = ''' + param_code + ''' And ID2 % ' + Rtrim(@Row) + ' = ' + (Case @I When @Row Then '0' Else Rtrim(@I) End) +' Then param_value Else '''' End) As ' + param_code
From TEST Group By param_code
Select @S = @S + ' From #T2 '
Select @I = @I + 1
End
Select @S = Stuff(@S, 1, 11, '')
Select @S
EXEC(@S)
Drop Table #T, #T2
GO
Drop Table TEST
--Result
/*
rkey a b c
1 +0.5 是 1
2 -0.8 否 2
3 +0.6 3
4 -0.7
*/
(rkey Int,
param_code Varchar(10),
param_value Nvarchar(10))
Insert TEST Select 1, 'a', N'+0.5'
Union All Select 1, 'a', N'-0.8'
Union All Select 1, 'a', N'+0.6'
Union All Select 1, 'a', N'-0.7'
Union All Select 2, 'b', N'是'
Union All Select 2, 'b', N'否'
Union All Select 3, 'c', N'1'
Union All Select 3, 'c', N'2'
Union All Select 3, 'c', N'3'
GO
Select ID = Identity(Int, 1, 1), * Into #T From TEST
Select ID2 = (Select Count(ID) From #T Where ID <= A.ID And param_code = A.param_code), * Into #T2 From #T ADeclare @S Nvarchar(4000)
Select @S = ' Select ID2 As rkey'
Select @S = @S + ', Max(Case param_code When ''' + param_code + ''' Then param_value Else '''' End) As ' + param_code
From TEST Group By param_code
Select @S = @S + ' From #T2 Group By ID2'
EXEC(@S)Drop Table #T, #T2
GO
Drop Table TEST
--Result
/*
rkey a b c
1 +0.5 是 1
2 -0.8 否 2
3 +0.6 3
4 -0.7
*/