求SQL 豎列變橫列

[kala2013]

數據表
PRD_NO WJ QTY
A W1 5
A W9 6
A W3 3
B Y0 1.2
C X1 2
C X10 5
D Z5 10
D Z0 11
D W1 10.5
D Y0 9
E W8 6
F W5 7
F X2 11.6
要得到如下結果
PRD_NO WJ1 WJ2 WJ3 WJ4
A 5 6 3
B 1.2
C 2 5
D 10 11 10.5 9
E 6
F 7 11.6
求SQL

[LongRui888]

--drop  table tb

create table tb
(
PRD_NO varchar(20), WJ varchar(20),   QTY numeric(10,2)
)

insert into tb
select 'A',       'W1',     5  union all
select 'A',       'W9',     6 union all
select 'A',       'W3',     3 union all
select 'B',       'Y0',     1.2 union all
select 'C',       'X1',     2 union all
select 'C',       'X10',    5 union all
select 'D',       'Z5',     10 union all     
select 'D',       'Z0',     11 union all
select 'D',       'W1',     10.5 union all
select 'D',       'Y0',     9 union all
select 'E',       'W8',     6 union all
select 'F',       'W5',     7 union all
select 'F',       'X2',     11.6
go

declare @sql nvarchar(4000)

set @sql = ''


;with t  
as  
(  
select *,  
       ROW_NUMBER() over(partition by PRD_NO order by @@servername) as rownum  
from tb  
)  
  
select @sql = @sql + ',max(case when rownum = '+cast(rownum as varchar)+' then QTY else null end) as WJ'+LTRIM(ROWNUM)  
from t  
group by rownum 



select @sql = 'select PRD_NO' + @sql +   
              ' from (select *,  
                            ROW_NUMBER() over(partition by PRD_NO order by @@servername) as rownum  
                     from tb)t' +  
              ' group by PRD_NO' 
              
--select @sql 

exec(@sql)
/*
PRD_NO	WJ1	WJ2	WJ3	WJ4
A	5.00	6.00	3.00	NULL
B	1.20	NULL	NULL	NULL
C	2.00	5.00	NULL	NULL
D	10.00	11.00	10.50	9.00
E	6.00	NULL	NULL	NULL
F	7.00	11.60	NULL	NULL
*/

[唐诗三百首]

create table u01
(PRD_NO varchar(10),WJ varchar(10),QTY varchar(10))

insert into u01
 select 'A','W1','5' union all
 select 'A','W9','6' union all
 select 'A','W3','3' union all
 select 'B','Y0','1.2' union all
 select 'C','X1','2' union all
 select 'C','X10','5' union all
 select 'D','Z5','10' union all
 select 'D','Z0','11' union all
 select 'D','W1','10.5' union all
 select 'D','Y0','9' union all
 select 'E','W8','6' union all
 select 'F','W5','7' union all
 select 'F','X2','11.6'
 

declare @tsql varchar(6000)

select @tsql=isnull(@tsql+',','')
            +'max(case when rn='+rtrim(number)+' then QTY else '''' end) ''WJ'+rtrim(number)+''' '
  from master.dbo.spt_values 
  where type='P' and number between 1 and 
  (select max(q) from
   (select count(1) 'q' from u01 group by PRD_NO) t)

select @tsql='select PRD_NO,'+@tsql
            +' from (select PRD_NO,QTY,
                            row_number() over(partition by PRD_NO order by getdate()) ''rn''
                     from u01) t
               group by PRD_NO '
     
exec(@tsql)

/*
PRD_NO     WJ1        WJ2        WJ3        WJ4
---------- ---------- ---------- ---------- ----------
A          5          6          3          
B          1.2                              
C          2          5                     
D          10         11         10.5       9
E          6                                
F          7          11.6                  

(6 row(s) affected)
*/

[KeepSayingNo]

语句

create table #tb
(
PRD_NO varchar(20), WJ varchar(20),   QTY numeric(10,2)
)
 
insert into #tb
select 'A',       'W1',     5  union all
select 'A',       'W9',     6 union all
select 'A',       'W3',     3 union all
select 'B',       'Y0',     1.2 union all
select 'C',       'X1',     2 union all
select 'C',       'X10',    5 union all
select 'D',       'Z5',     10 union all     
select 'D',       'Z0',     11 union all
select 'D',       'W1',     10.5 union all
select 'D',       'Y0',     9 union all
select 'E',       'W8',     6 union all
select 'F',       'W5',     7 union all
select 'F',       'X2',     11.6

select * from 
(
	select 'WJ'+convert(varchar(10),rn) as WJ,PRD_NO,QTY from 
	(select ROW_NUMBER() over (partition by PRD_NO ORDER BY getdate()) as rn,#tb.* from #tb) a
) b
pivot
(
    sum(QTY) for WJ in ([WJ1],[WJ2],[WJ3],[WJ4])
) pvt

drop table #tb

结果

[xxfvba]

向楼上学习 select *,rn=row_number() over (partition by prd_no order by getdate()) into #A from #tb declare @s varchar(max) set @s='' select @s=@s+','+quotename('WJ'+convert(varchar,rn))+'=max(case when [rn]='+quotename(rn,'''')+ 'then qty else 0 end)' from #A group by rn select @s='select prd_no'+@s+' from #A group by prd_no' exec(@s)