3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
一个比较有难度的行列转换。谢谢！
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语句能不能搞定
...全文
请发表友善的回复…
发表回复
cxmcxm 2008-01-10
--假设表名为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)
-狙击手- 2008-01-10
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
*/dawugui 2008-01-10
--把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 行）
*/duanzhi1984 2008-01-10
使用动态语句。
请看以下示例.
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 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 电池')
Angelnet 2008-01-10
HI，谢谢两位老大，我现在还有一个问题，怎么把0显示为空白呀
dawugui 2008-01-10
他就五个,不用动态.
-狙击手- 2008-01-10
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
*/-狙击手- 2008-01-10
写个动态的SQL
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
*/dawugui 2008-01-10
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 行）
*/tim_spac 2008-01-10
declare @tb table (id int identity(0,1) primary key, code char(2), qty int)
insert into @tb
select * from tb
order by qty desc
select
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
select * from tb
order by qty desc
select
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