导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

一个比较有难度的行列转换。谢谢!

Angelnet 2008-01-10 08:08:54
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语句能不能搞定
...全文
65 点赞 收藏 10
写回复
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
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 电池')
回复
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告