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

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

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
-------------------------------------------------------------------------------

...全文
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'

/*如何这成这样：

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

``````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

3.2w+

MS-SQL Server相关内容讨论专区