34,594
社区成员
发帖
与我相关
我的任务
分享
--假设表名为T1
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_cur
while @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 '+@str
exec(@str)
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)
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
*/
--把0显示为空白.
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)
go
select
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 px
drop table tb
/*
code1 qty1 code2 qty2 code3 qty3 code4 qty4 code5 qty5
---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------ ---------- ------------------------------
AB 15 3d 13 21 11 A1 10
(所影响的行数为 1 行)
*/
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)
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 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)
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
*/
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)
go
select
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 px
drop table tb
/*
code1 qty1 code2 qty2 code3 qty3 code4 qty4 code5 qty5
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- -----------
AB 15 3d 13 21 11 A1 10 0
(所影响的行数为 1 行)
*/