救命啊~横向纵向问题，高手帮帮忙，完成不了就没有办法交差~
数据：
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
万分感谢~
[解决办法]
使用一个临时表,可以用动态语句实现你的要求.
if object_id( 'pubs..tb ') is not null
drop table tb
go
create 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 ')
go
select 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 , px
declare @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 TEST
(rkeyInt,
param_codeVarchar(10),
param_valueNvarchar(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 A
Declare @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
/*
rkeyabc
1+0.5是1
2-0.8否2
3+0.63
4-0.7
*/
