code Qty
A1 10
21 11
AB 15
3d 13 上面表能变成下面表呀,下面是固定的前五大不良,没有五个的显示为空白
code1 Qty1 code2 Qty2 code3 Qty3 code4 Qty4 code5 Qty5
AB 15 3d 13 21 11 A1 10
-------------------------------------------------------------------------------
通过上面表得到以上结果,上面表数据有可能少于五行,当少于五行时,用空表示,下面是固定的,排列顺序是,按QTY从大到小排列。非常感谢,再次感谢Q不知SQL语句能不能搞定
A1 10
21 11
AB 15
3d 13 上面表能变成下面表呀,下面是固定的前五大不良,没有五个的显示为空白
code1 Qty1 code2 Qty2 code3 Qty3 code4 Qty4 code5 Qty5
AB 15 3d 13 21 11 A1 10
-------------------------------------------------------------------------------
通过上面表得到以上结果,上面表数据有可能少于五行,当少于五行时,用空表示,下面是固定的,排列顺序是,按QTY从大到小排列。非常感谢,再次感谢Q不知SQL语句能不能搞定
insert into @tb
select * from tb
order by qty descselect
code1=a.code ,Qty1=a.qty
,code2=b.code ,Qty2=b.qty
,code3=c.code ,Qty3=c.qty
,code4=d.code ,Qty4=d.qty
,code5=e.code ,Qty5=e.qty
from
(select ln = id/5, code, qty from @tb where id % 5 = 0) as a,
(select ln = id/5, code, qty from @tb where id % 5 = 1) as b,
(select ln = id/5, code, qty from @tb where id % 5 = 2) as c,
(select ln = id/5, code, qty from @tb where id % 5 = 3) as d,
(select ln = id/5, code, qty from @tb where id % 5 = 4) as e
where a.ln*=b.ln
and a.ln*=c.ln
and a.ln*=d.ln
and a.ln*=e.ln
insert into tb values('A1', 10)
insert into tb values('21', 11)
insert into tb values('AB', 15)
insert into tb values('3d', 13)
goselect
max(case place when 1 then code else '' end) 'code1',
max(case place when 1 then qty else '' end) 'qty1',
max(case place when 2 then code else '' end) 'code2',
max(case place when 2 then qty else '' end) 'qty2',
max(case place when 3 then code else '' end) 'code3',
max(case place when 3 then qty else '' end) 'qty3',
max(case place when 4 then code else '' end) 'code4',
max(case place when 4 then qty else '' end) 'qty4',
max(case place when 5 then code else '' end) 'code5',
max(case place when 5 then qty else '' end) 'qty5'
from
(
SELECT px = 1 , * , Place=(SELECT COUNT(DISTINCT qty) FROM tb WHERE qty >= t.qty) FROM tb t
) t
group by pxdrop table tb/*
code1 qty1 code2 qty2 code3 qty3 code4 qty4 code5 qty5
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------
AB 15 3d 13 21 11 A1 10 0(所影响的行数为 1 行)
*/
insert into tb values('A1', 10)
insert into tb values('21', 11)
insert into tb values('AB', 15)
insert into tb values('3d', 13)
go
declare @s varchar(8000)
set @s = ''
select @s = @s + ',[code'+ltrim(px)+']=max(case when px = '+ltrim(px)+' then code else '''' end)'+
' ,[qty'+ltrim(px)+ ']=max(case when px = '+ ltrim(px)+ ' then qty else 0 end) '
from (select top 100 percent px=(SELECT COUNT(DISTINCT qty) FROM tb WHERE qty <= t.qty) from tb t order by px) a
set @s = stuff(@s,1,1,'')
exec('select '+ @s + ' from (select *,px= (SELECT COUNT(DISTINCT qty) FROM tb WHERE qty <= t.qty) from tb t ) a')
drop table tb/*code1 qty1 code2 qty2 code3 qty3 code4 qty4
---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------
A1 10 21 11 3d 13 AB 15*/
insert into tb values('A1', 10)
insert into tb values('21', 11)
insert into tb values('AB', 15)
insert into tb values('3d', 13)
insert into tb values('Ac', 16)
insert into tb values('3e', 14)
go
declare @s varchar(8000)
set @s = ''
select @s = @s + ',[code'+ltrim(px+1)+']=max(case when px%5 = '+ltrim(px)+' then code else '''' end)'+
' ,[qty'+ltrim(px+1)+ ']=max(case when px%5 = '+ ltrim(px)+ ' then qty else 0 end) '
from (select px = 1 union select 2 union select 3 union select 4 union select 0) a
set @s = stuff(@s,1,1,'')
exec('select '+ @s + ' from (select *,px= (SELECT COUNT(DISTINCT qty) FROM tb WHERE qty < t.qty) from tb t ) a group by px/5')
drop table tb/*code1 qty1 code2 qty2 code3 qty3 code4 qty4 code5 qty5
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------
A1 10 21 11 3d 13 3e 14 AB 15
Ac 16 0 0 0 0
*/
请看以下示例.create table Dctxt(电池 varchar(10),电压 varchar(10),时间 varchar(10))
insert Dctxt
select '1号' , '2V', '2006-09-13'union all
select '1号' , '6V', '2006-09-14' union all
select '1号' , '8V' , '2006-09-15'union all
select '2号' , '6V', '2006-09-10'union all
select '2号' , '5V' , '2006-09-11'union all
select '2号' , '10V' , '2006-09-12'
/*如何这成这样:
电池 2006-09-10 2006-09-11 2006-09-12
1号 2V 6V 8V
2号 6V 5V 10V
*/declare @str varchar(8000)
set @str = ''
select @str=@str + ',[' + convert(varchar(10),时间,120) + ']=max(case when datediff(dd,时间,''' + convert(varchar(10),时间,120) + ''') = 0 then 电压 else '''' end)'
from Dctxt group by 时间
print @str
EXEC('select 电池' + @str + ' from Dctxt group by 电池')
create table tb(code varchar(10),Qty int)
insert into tb values('A1', 10)
insert into tb values('21', 11)
insert into tb values('AB', 15)
insert into tb values('3d', 13)
goselect
max(case place when 1 then code else '' end) 'code1',
max(case place when 1 then cast(qty as varchar) else '' end) 'qty1',
max(case place when 2 then code else '' end) 'code2',
max(case place when 2 then cast(qty as varchar) else '' end) 'qty2',
max(case place when 3 then code else '' end) 'code3',
max(case place when 3 then cast(qty as varchar) else '' end) 'qty3',
max(case place when 4 then code else '' end) 'code4',
max(case place when 4 then cast(qty as varchar) else '' end) 'qty4',
max(case place when 5 then code else '' end) 'code5',
max(case place when 5 then cast(qty as varchar) else '' end) 'qty5'
from
(
SELECT px = 1 , * , Place=(SELECT COUNT(DISTINCT qty) FROM tb WHERE qty >= t.qty) FROM tb t
) t
group by pxdrop table tb/*
code1 qty1 code2 qty2 code3 qty3 code4 qty4 code5 qty5
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
AB 15 3d 13 21 11 A1 10 (所影响的行数为 1 行)*/
insert into tb values('A1', 10)
insert into tb values('21', 11)
insert into tb values('AB', 15)
insert into tb values('3d', 13)
insert into tb values('Ac', 16)
insert into tb values('3e', 14)
go
declare @s varchar(8000)
set @s = ''
select @s = @s + ',[code'+ltrim(px+1)+']=max(case when px%5 = '+ltrim(px)+' then code else '''' end)'+
' ,[qty'+ltrim(px+1)+ ']=max(case when px%5 = '+ ltrim(px)+ ' then cast(qty as varchar) else '''' end) '
from (select px = 1 union select 2 union select 3 union select 4 union select 0) a
set @s = stuff(@s,1,1,'')
exec('select '+ @s + ' from (select *,px= (SELECT COUNT(DISTINCT qty) FROM tb WHERE qty < t.qty) from tb t ) a group by px/5')
drop table tb/*code1 qty1 code2 qty2 code3 qty3 code4 qty4 code5 qty5
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
A1 10 21 11 3d 13 3e 14 AB 15
Ac 16 */
if object_id('t1') is not null drop table t1
go
create table t1 (code varchar(10),qty int)
go
set nocount on
insert into t1 values('A1',10)
insert into t1 values('21',11)
insert into t1 values('AB',15)
insert into t1 values('3d',13)
declare @str varchar(200),@code varchar(10),@qty int,@i int
declare t1_cur cursor for select code,qty from t1 order by qty desc
set @i=1
set @str=''
open t1_cur
fetch next from t1_cur into @code,@Qty
while @@fetch_status=0
begin
if @str<>'' set @str=@str+','
select @str=@str+''''+@code+''' as code'+convert(varchar(1),@i)+','+convert(varchar(10),@qty)+' as qty'+convert(varchar(1),@i)
set @i=@i+1
fetch next from t1_cur into @code,@qty
end
close t1_cur
deallocate t1_curwhile @i<=5
begin
select @str=@str+','''' as code'+convert(varchar(1),@i)+','''' as qty'+convert(varchar(1),@i)
set @i=@i+1
end
select @str='select '+@strexec(@str)