Select A=(case when Kind='A' then Pno else NULL end),
B=(case when Kind='A' then Pno else NULL end),
C=(case when Kind='A' then Pno else NULL end),
from Tt
B=(case when Kind='A' then Pno else NULL end),
C=(case when Kind='A' then Pno else NULL end),
from Tt
Select A=(case when Kind='A' then Pno else NULL end),
B=(case when Kind='B' then Pno else NULL end),
C=(case when Kind='C' then Pno else NULL end),
from Tt
不过好像不满足要求啊
你的查询结果为:
A B C
0001 NULL NULL
0002 NULL NULL
NULL 0011 NULL
NULL 0002 NULL
NULL 0008 NULL
NULL NULL 001而且 字段 A B C 不是固定的
由TT表中GUAN字段决定 也就是说还可能有 D E F G H....
继续等待
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into #t
from Tt select aa.A,bb.B,cc.C
from #t a full join #t b on a.orders=b.orders
full join #t c on a.orders=c.orders
http://search.csdn.net/Expert/topic/1832/1832125.xml?temp=.8702509
请教各路高手?
有一个表:
BH GZLB JE
----------------------
001 A 100
001 B 150
001 C 110
002 A 99
002 B 180
002 C 150
003 A 160
003 B 170
003 C 130
用SQL语句如何将上面的表变成下面横向的排列方式呢。
BH A B C
-------------------
001 100 150 110
002 99 180 150
003 160 170 130
(GZLB 里面的字段是动态的,不是只有A,B,C 可能还有A,B,C,D,E...)
-----------------------------------------------------
这样解决
declare @sql varchar(6000)
set @sql = 'select '
select @sql = @sql +' sum(case GZLB when '''+ GZLB +''' then JE else null end) ['+ GZLB +'],' from (select distinct GZLB as GZLB from tblA) tmp
set @sql = substring(@sql, 1, len(@sql)-1) +' from tblA group by BH'
exec(@sql)
------------------------------------------
不过我这里没有一个分组的字段,不知道这么写了
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into #t
from Tt 先这样然后用orders 做 分组字段
能帮忙写一下吗?在这里先谢过了^_^
select (select count(*)
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into ##t
from Tt set @s='select orders'
select @s=@s+','+Kind+'=max(case orders when '''+Kind+''' then cast(Pno as varchar) else '''' end)'
from ##t
group by Kindset @s=@s+' from ##t group by orders'exec(@s)
你那样得到的分组有问题:
你的查询结果
orders kind pno
4 A 0001
4 A 0002
4 A 0003
4 A 0004
3 B 001C
3 B 0002
3 B 0008
2 C 001
---------------------
而正确的分组应该这样
Kind Pno orders
A 0001 1
A 0002 2
A 0003 3
A 0004 4
B 001C 1
B 0002 2
B 0008 3
C 001 1
select (select count(*)
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into ##t
from Tt set @s='select orders'
select @s=@s+','+Kind+'=max(case orders when '''+Kind+''' then cast(Pno as varchar) else '''' end)'
from ##t
group by Kindset @s=@s+' from ##t group by orders'exec(@s)
-------------------------------
我先看看这个
工资项表的中数据一条一条取出来,然后拼成select语句的字段针对工资表的(即实验由行来列的转换),然后再分组求个小和:),就出来了。
主要要用到动态SQL的方法select b.单位名称,sum(a.反对) As '反对',sum(a.各个) As '各个',sum(a.环境) As '环境',sum(a.看见) As '看见',sum(a.就是) As '就是',sum(a.环有) As '环有',sum(a.合计) As '合计'
from (
select 单位编号,
(case when 检查编号=2 then count(*) else 0) As '反对',
(case when 检查编号=3 then count(*) else 0) As '各个',
(case when 检查编号=4 then count(*) else 0) As '环境',
(case when 检查编号=5 then count(*) else 0) As '看见',
(case when 检查编号=6 then count(*) else 0) As '就是',
(case when 检查编号=7 then count(*) else 0) As '环有',
count As '合计'
from 数据结果副表
where 条件
) a,
数据基本表 b
where a.单位编号 = b.单位编号
group by b.单位名称
having sum(a.检查项目字段) > @指定次数 (指定任意检查项目的数次数)
参考上例,将case的语句使用游标,组成动态SQL,再使用sp_executesql即可实验你的要求,且出来的结果中列是完全可要据你的设置可变
select (select count(*)
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into ##t a --这里改一点
from Tt set @s='select orders'
select @s=@s+','+Kind+'=max(case orders when '''+Kind+''' then cast(Pno as varchar) else '''' end)'
from ##t
group by Kindset @s=@s+' from ##t group by orders'exec(@s)
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into ##t a --这里有语法错误
from Tt
----------------------
铅笔刀哥哥
能不能在查询分析器里面测试一下再帖出来啊,真的很感谢你哦
A 0001 1
A 0002 2
A 0003 3
A 0004 4
B 001C 1
B 0002 2
B 0008 3
C 001 1
现在只要能得到这样一个临时表
我就知道怎么解决了
from Tt b
where b.kind=Tt.Kind and Pno>=a.Pno) as orders,*
into ##t
from Tt a
from Tt
where kind=a.Kind and Pno>=a.Pno) as orders,*
into ##t
from Tt a
insert into Tta
select 'A','001' union all
select 'A','002' union all
select 'B','0011' union all
select 'B','002' union all
select 'B','008' union all
select 'C','001'
select (select count(*)
from Tta
where kind=a.Kind and Pno>=a.Pno) as orders,*
into ##t
from Tta aselect * from ##t order by Kind,orders--结果
orders Kind Pno
----------- ---------- ----------
1 A 002
2 A 001
1 B 008
2 B 002
3 B 0011
1 C 001(所影响的行数为 6 行)
select @s=@s+',['+Kind+']=max(case Kind when '''+Kind+''' then Pno else null end)'
from ##t
group by Kindset @s=@s+' from ##t group by orders'exec(@s)
--结果orders A B C
----------- ---------- ---------- ----------
1 002 008 001
2 001 002 NULL
3 NULL 0011 NULL
不过不知道为什么结果是这样的呢
orders Kind Pno
----------- -------------------- --------------------
4 A 0001
3 A 0002
2 A 0003
1 A 0004
1 B 001C
3 B 0002
2 B 0008
2 C 001
1 C 002 你看B的ORDERS
orders Kind
1 B
3 B
2 B 为什么不按3 2 1的顺序呢
select * from ##t order by Kind,orders
特别感谢lsxaa(小李铅笔刀)
结帖