set nocount on create table tb(id int,key1 varchar(10),value varchar(10),cid int) insert tb select 1,'b1','一',1 insert tb select 2,'b2','二',1 insert tb select 3,'b3','三',1 insert tb select 4,'b4','四',1 insert tb select 5,'c1','1000',1 insert tb select 6,'c2','2000',1 insert tb select 7,'c3','3000',1 insert tb select 8,'c4','4000',1 insert tb select 9,'year','2005',1 insert tb select 10,'year','2006',1 insert tb select 12,'year','2007',1 insert tb select 13,'b1','一',2 insert tb select 14,'b2','二',2 insert tb select 15,'b3','三',2 insert tb select 16,'b4','四',2 insert tb select 17,'c1','1000',2 insert tb select 18,'c2','2000',2 insert tb select 19,'c3','3000',2 insert tb select 20,'c4','4000',2 insert tb select 21,'year','2006',2 go declare @sql varchar(8000) select @sql = 'select b.value as '' '' ' select @sql = @sql + ',c.value as ['+value+']' from (select distinct value from tb where key1='year') a order by value select @sql = @sql + ' from (select distinct right(key1,1) as id,value from tb where left(key1,1)= ''b'') b, (select right(key1,1) as id,rtrim(sum(cast(value as int))) value from tb where left(key1,1)= ''c'' group by right(key1,1)) c where b.id = c.id' exec(@sql) go drop table tb /* 2005 2006 2007 ---------- ------------ ------------ ------------ 一 2000 2000 2000 二 4000 4000 4000 三 6000 6000 6000 四 8000 8000 8000 */
create table tb(ID varchar(20),[key] varchar(20),value varchar(20) , CID varchar(20)) insert into tb values('ID=1' ,'key=b1' ,'value=一' ,'CID=1') insert into tb values('ID=2' ,'key=b2' ,'value=二' ,'CID=1') insert into tb values('ID=3' ,'key=b3' ,'value=三' ,'CID=1') insert into tb values('ID=4' ,'key=b4' ,'value=四' ,'CID=1') insert into tb values('ID=5' ,'key=c1' ,'value=1000','CID=1') insert into tb values('ID=6' ,'key=c2' ,'value=2000','CID=1') insert into tb values('ID=7' ,'key=c3' ,'value=3000','CID=1') insert into tb values('ID=8' ,'key=c4' ,'value=4000','CID=1') insert into tb values('ID=9' ,'key=year','value=2005','CID=1') insert into tb values('ID=13','key=b1' ,'value=一' ,'CID=2') insert into tb values('ID=14','key=b2' ,'value=二' ,'CID=2') insert into tb values('ID=15','key=b3' ,'value=三' ,'CID=2') insert into tb values('ID=16','key=b4' ,'value=四' ,'CID=2') insert into tb values('ID=17','key=c1' ,'value=1000','CID=2') insert into tb values('ID=18','key=c2' ,'value=2000','CID=2') insert into tb values('ID=19','key=c3' ,'value=3000','CID=2') insert into tb values('ID=20','key=c4' ,'value=4000','CID=2') insert into tb values('ID=21','key=year','value=2006','CID=2') go select t1.value , t2.value as [2005], t3.value as [2006] from ( select [key] = right([key],1) , value = substring(value,charindex('=',value)+1,len(value)) from tb where cid = 'CID=1' and left([key],5) = 'key=b' ) t1, ( select [key] = right([key],1) , value = substring(value,charindex('=',value)+1,len(value)) from tb where cid = 'CID=1' and left([key],5) = 'key=c' ) t2, ( select [key] = right([key],1) , value = substring(value,charindex('=',value)+1,len(value)) from tb where cid = 'CID=2' and left([key],5) = 'key=c' ) t3 where t1.[key] = t2.[key] and t1.[key] = t3.[key]drop table tb /* value 2005 2006 -------------------- -------------------- -------------------- 一 1000 1000 二 2000 2000 三 3000 3000 四 4000 4000(所影响的行数为 4 行) */
set nocount on create table tb(id int,key1 varchar(10),value varchar(10),cid int) insert tb select 1,'b1','一',1 insert tb select 2,'b2','二',1 insert tb select 3,'b3','三',1 insert tb select 4,'b4','四',1 insert tb select 5,'c1','1000',1 insert tb select 6,'c2','2000',1 insert tb select 7,'c3','3000',1 insert tb select 8,'c4','4000',1 insert tb select 9,'year','2005',1 insert tb select 13,'b1','一',2 insert tb select 14,'b2','二',2 insert tb select 15,'b3','三',2 insert tb select 16,'b4','四',2 insert tb select 17,'c1','1000',2 insert tb select 18,'c2','2000',2 insert tb select 19,'c3','3000',2 insert tb select 20,'c4','4000',2 insert tb select 21,'year','2006',2 go declare @sql varchar(8000) select @sql = 'select b.value' --select @sql = @sql + ',c.value as ['+value+']' from (select distinct value from tb where key1='year') a order by value select @sql = @sql +',max(case when b.cid='+rtrim(cid)+' then c.value end) as ['+value+'] ' from (select cid,value from tb where key1='year' group by cid,value) a order by valueselect @sql = @sql + ' from (select right(key1,1) as id,value,cid from tb where left(key1,1)= ''b'') b, (select right(key1,1) as id,value,cid from tb where left(key1,1)= ''c'') c where b.id = c.id and b.cid = c.cid group by b.value,b.id order by b.id' exec(@sql) go drop table tb /* value 2005 2006 ---------- ---------- ---------- 一 1000 1000 二 2000 2000 三 3000 3000 四 4000 4000 */
select b.value,max(case when b.cid=1 then c.value end) as [2005] ,max(case when b.cid=2 then c.value end) as [2006] from (select right(key1,1) as id,value,cid from tb where left(key1,1)= 'b') b, (select right(key1,1) as id,value,cid from tb where left(key1,1)= 'c') c where b.id = c.id and b.cid = c.cid group by b.value,b.id order by b.id
set nocount on create table ta(ID int,[key] varchar(10),value varchar(10),CID int) insert ta select 1,'b1','一',1 insert ta select 2,'b2','二',1 insert ta select 3,'b3','三',1 insert ta select 4,'b4','四',1 insert ta select 5,'c1','1000',1 insert ta select 6,'c2','2000',1 insert ta select 7,'c3','3000',1 insert ta select 8,'c4','4000',1 insert ta select 9,'year','2005',1 insert ta select 13,'b1','一',2 insert ta select 14,'b2','二',2 insert ta select 15,'b3','三',2 insert ta select 16,'b4','四',2 insert ta select 17,'c1','1000',2 insert ta select 18,'c2','2000',2 insert ta select 19,'c3','3000',2 insert ta select 20,'c4','6000',2 insert ta select 21,'year','2006',2 godeclare @s varchar(8000) select @s = isnull(@s+',','') +'['+value+']=(select value from ta where cid ='+ltrim(cid)+' and replace(c.[key],''b'',''c'') = [key])'from ( select distinct a.* from (select cid,value from ta where [key] = 'year') a cross join (select cid as cid1, [key] from ta where [key] like 'b%') b where a.cid = b.cid1) c --print @sexec('select distinct c.d as [ ],'+@s+' from (select distinct a.* ,b.* from (select cid,value from ta where [key] = ''year'') a cross join (select cid as cid1,value as d, [key] from ta where [key] like ''b%'') b where a.cid = b.cid1 )c') /* 2005 2006 ---------- ---------- ---------- 二 2000 2000 三 3000 3000 四 4000 6000 一 1000 1000*/drop table ta set nocount off
select b.cellvalue,max(case when b.templet_h_id=1 then c.value end) as [2007] ,max(case when b.cid=2 then c.value end) as [2008] from (select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'b') b (select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'c') c where b.id = c.id and b.templet_h_id = c.templet_h_id group by b.cellvalue,b.id order by b.id
select b.cellvalue ,max(case when b.templet_h_id=1 then c.value end) as [2007] ,max(case when b.templet_h_id=2 then c.value end) as [2008] from (select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'b') b (select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'c') c where b.id = c.id and b.templet_h_id = c.templet_h_id group by b.cellvalue,b.id order by b.id
select b.cellvalue ,max(case when b.templet_h_id=1 then c.cellvalue end) as [2007] ,max(case when b.templet_h_id=2 then c.cellvalue end) as [2008] from (select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'b') b (select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'c') c where b.id = c.id and b.templet_h_id = c.templet_h_id group by b.cellvalue,b.id order by b.id
2005 2006
一 2000 2000
二 4000 4000
三 6000 6000
四 8000 8000
这个意思就是,上面的2005,2006是year的value
下面的2000是CID=1和CID=2的key=B1的value的和
是挺复杂的,谢谢大家了
断层啊
ID=2,key=b2,value=二,CID=1
ID=3,key=b3,value=三,CID=1
ID=4,key=b4,value=四,CID=1
ID=5,key=c1,value=1000,CID=1
ID=6,key=c2,value=2000,CID=1
ID=7,key=c3,value=3000,CID=1
ID=8,key=c4,value=4000,CID=1
ID=9,key=year,value=2005,CID=1
ID=13,key=b1,value=一,CID=2
ID=14,key=b2,value=二,CID=2
ID=15,key=b3,value=三,CID=2
ID=16,key=b4,value=四,CID=2
ID=17,key=c1,value=1000,CID=2
ID=18,key=c2,value=2000,CID=2
ID=19,key=c3,value=3000,CID=2
ID=20,key=c4,value=4000,CID=2
ID=21,key=year,value=2006,CID=2
数据应该是这样,对不起大家,写的有点失误
create table tb(id int,key1 varchar(10),value varchar(10),cid int)
insert tb select 1,'b1','一',1
insert tb select 2,'b2','二',1
insert tb select 3,'b3','三',1
insert tb select 4,'b4','四',1
insert tb select 5,'c1','1000',1
insert tb select 6,'c2','2000',1
insert tb select 7,'c3','3000',1
insert tb select 8,'c4','4000',1
insert tb select 9,'year','2005',1
insert tb select 10,'year','2006',1
insert tb select 12,'year','2007',1
insert tb select 13,'b1','一',2
insert tb select 14,'b2','二',2
insert tb select 15,'b3','三',2
insert tb select 16,'b4','四',2
insert tb select 17,'c1','1000',2
insert tb select 18,'c2','2000',2
insert tb select 19,'c3','3000',2
insert tb select 20,'c4','4000',2
insert tb select 21,'year','2006',2
go
declare @sql varchar(8000)
select @sql = 'select b.value as '' '' '
select @sql = @sql + ',c.value as ['+value+']' from (select distinct value from tb where key1='year') a order by value
select @sql = @sql + ' from
(select distinct right(key1,1) as id,value from tb where left(key1,1)= ''b'') b,
(select right(key1,1) as id,rtrim(sum(cast(value as int))) value from tb where left(key1,1)= ''c'' group by right(key1,1)) c
where b.id = c.id'
exec(@sql)
go
drop table tb
/*
2005 2006 2007
---------- ------------ ------------ ------------
一 2000 2000 2000
二 4000 4000 4000
三 6000 6000 6000
四 8000 8000 8000
*/
insert into tb values('ID=1' ,'key=b1' ,'value=一' ,'CID=1')
insert into tb values('ID=2' ,'key=b2' ,'value=二' ,'CID=1')
insert into tb values('ID=3' ,'key=b3' ,'value=三' ,'CID=1')
insert into tb values('ID=4' ,'key=b4' ,'value=四' ,'CID=1')
insert into tb values('ID=5' ,'key=c1' ,'value=1000','CID=1')
insert into tb values('ID=6' ,'key=c2' ,'value=2000','CID=1')
insert into tb values('ID=7' ,'key=c3' ,'value=3000','CID=1')
insert into tb values('ID=8' ,'key=c4' ,'value=4000','CID=1')
insert into tb values('ID=9' ,'key=year','value=2005','CID=1')
insert into tb values('ID=13','key=b1' ,'value=一' ,'CID=2')
insert into tb values('ID=14','key=b2' ,'value=二' ,'CID=2')
insert into tb values('ID=15','key=b3' ,'value=三' ,'CID=2')
insert into tb values('ID=16','key=b4' ,'value=四' ,'CID=2')
insert into tb values('ID=17','key=c1' ,'value=1000','CID=2')
insert into tb values('ID=18','key=c2' ,'value=2000','CID=2')
insert into tb values('ID=19','key=c3' ,'value=3000','CID=2')
insert into tb values('ID=20','key=c4' ,'value=4000','CID=2')
insert into tb values('ID=21','key=year','value=2006','CID=2')
go
select t1.value ,
t2.value as [2005],
t3.value as [2006]
from
(
select [key] = right([key],1) ,
value = substring(value,charindex('=',value)+1,len(value))
from tb where cid = 'CID=1' and left([key],5) = 'key=b'
) t1,
(
select [key] = right([key],1) ,
value = substring(value,charindex('=',value)+1,len(value))
from tb where cid = 'CID=1' and left([key],5) = 'key=c'
) t2,
(
select [key] = right([key],1) ,
value = substring(value,charindex('=',value)+1,len(value))
from tb where cid = 'CID=2' and left([key],5) = 'key=c'
) t3
where t1.[key] = t2.[key] and t1.[key] = t3.[key]drop table tb /*
value 2005 2006
-------------------- -------------------- --------------------
一 1000 1000
二 2000 2000
三 3000 3000
四 4000 4000(所影响的行数为 4 行)
*/
create table tb(id int,key1 varchar(10),value varchar(10),cid int)
insert tb select 1,'b1','一',1
insert tb select 2,'b2','二',1
insert tb select 3,'b3','三',1
insert tb select 4,'b4','四',1
insert tb select 5,'c1','1000',1
insert tb select 6,'c2','2000',1
insert tb select 7,'c3','3000',1
insert tb select 8,'c4','4000',1
insert tb select 9,'year','2005',1
insert tb select 13,'b1','一',2
insert tb select 14,'b2','二',2
insert tb select 15,'b3','三',2
insert tb select 16,'b4','四',2
insert tb select 17,'c1','1000',2
insert tb select 18,'c2','2000',2
insert tb select 19,'c3','3000',2
insert tb select 20,'c4','4000',2
insert tb select 21,'year','2006',2
go
declare @sql varchar(8000)
select @sql = 'select b.value'
--select @sql = @sql + ',c.value as ['+value+']' from (select distinct value from tb where key1='year') a order by value
select @sql = @sql +',max(case when b.cid='+rtrim(cid)+' then c.value end) as ['+value+'] ' from
(select cid,value from tb where key1='year' group by cid,value) a order by valueselect @sql = @sql + ' from
(select right(key1,1) as id,value,cid from tb where left(key1,1)= ''b'') b,
(select right(key1,1) as id,value,cid from tb where left(key1,1)= ''c'') c
where b.id = c.id and b.cid = c.cid group by b.value,b.id order by b.id'
exec(@sql)
go
drop table tb
/*
value 2005 2006
---------- ---------- ----------
一 1000 1000
二 2000 2000
三 3000 3000
四 4000 4000
*/
(select right(key1,1) as id,value,cid from tb where left(key1,1)= 'b') b,
(select right(key1,1) as id,value,cid from tb where left(key1,1)= 'c') c
where b.id = c.id and b.cid = c.cid group by b.value,b.id order by b.id
create table ta(ID int,[key] varchar(10),value varchar(10),CID int)
insert ta select 1,'b1','一',1
insert ta select 2,'b2','二',1
insert ta select 3,'b3','三',1
insert ta select 4,'b4','四',1
insert ta select 5,'c1','1000',1
insert ta select 6,'c2','2000',1
insert ta select 7,'c3','3000',1
insert ta select 8,'c4','4000',1
insert ta select 9,'year','2005',1
insert ta select 13,'b1','一',2
insert ta select 14,'b2','二',2
insert ta select 15,'b3','三',2
insert ta select 16,'b4','四',2
insert ta select 17,'c1','1000',2
insert ta select 18,'c2','2000',2
insert ta select 19,'c3','3000',2
insert ta select 20,'c4','6000',2
insert ta select 21,'year','2006',2
godeclare @s varchar(8000)
select @s = isnull(@s+',','') +'['+value+']=(select value from ta where cid ='+ltrim(cid)+' and replace(c.[key],''b'',''c'') = [key])'from (
select distinct a.*
from (select cid,value from ta where [key] = 'year') a
cross join
(select cid as cid1, [key] from ta where [key] like 'b%') b
where a.cid = b.cid1) c
--print @sexec('select distinct c.d as [ ],'+@s+'
from (select distinct a.* ,b.*
from (select cid,value from ta where [key] = ''year'') a
cross join
(select cid as cid1,value as d, [key] from ta where [key] like ''b%'') b
where a.cid = b.cid1
)c')
/*
2005 2006
---------- ---------- ----------
二 2000 2000
三 3000 3000
四 4000 6000
一 1000 1000*/drop table ta
set nocount off
C表结构是:id,其他没用
请在搞一下,谢谢了
A表的数据是
id=1,templet_h_id=1,cellkey=writedate,cellvalue=2007
id=2,templet_h_id=1,cellkey=b1,cellvalue=业务1
id=3,templet_h_id=1,cellkey=b2,cellvalue=业务2
id=4,templet_h_id=1,cellkey=c1,cellvalue=1000
id=5,templet_h_id=1,cellkey=c2,cellvalue=2000
id=6,templet_h_id=2,cellkey=writedate,cellvalue=2008
id=7,templet_h_id=2,cellkey=b1,cellvalue=业务1
id=8,templet_h_id=2,cellkey=b2,cellvalue=业务2
id=9,templet_h_id=2,cellkey=c1,cellvalue=1000
id=10,templet_h_id=2,cellkey=c2,cellvalue=2000
出来的样子:
2007 2008
---------- ---------- ----------
业务1 2000 2000
业务2 4000 4000
麻烦您了,帮忙在写下,如果是sql语句的话,select 后面的列名就要3个,第一个是(业务1,业务2)的字段,第二个(2007,2008年份)的字段。第三个是(值的字段)
谢谢了
select b.cellvalue,max(case when b.templet_h_id=1 then c.value end) as [2007]
,max(case when b.cid=2 then c.value end) as [2008] from
(select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'b') b
(select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'c') c
where b.id = c.id and b.templet_h_id = c.templet_h_id group by b.cellvalue,b.id order by b.id
,max(case when b.templet_h_id=1 then c.value end) as [2007]
,max(case when b.templet_h_id=2 then c.value end) as [2008]
from
(select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'b') b
(select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'c') c
where b.id = c.id and b.templet_h_id = c.templet_h_id
group by b.cellvalue,b.id order by b.id
,max(case when b.templet_h_id=1 then c.cellvalue end) as [2007]
,max(case when b.templet_h_id=2 then c.cellvalue end) as [2008]
from
(select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'b') b
(select right(cellkey,1) as id,cellvalue,templet_h_id from tb where left(cellkey,1)= 'c') c
where b.id = c.id and b.templet_h_id = c.templet_h_id
group by b.cellvalue,b.id order by b.id