有一个表 partnum die
IRF001 9901
IRF001 9902
IRF001 9903
IRF002 9911
IRF002 9912
IRF002 9913
IRF002 9914
要转置成: PartNum die1 die2 die3 die4
IRF001 9901 9902 9903
IRF002 9911 9912 9913 9914
求SQL
IRF001 9901
IRF001 9902
IRF001 9903
IRF002 9911
IRF002 9912
IRF002 9913
IRF002 9914
要转置成: PartNum die1 die2 die3 die4
IRF001 9901 9902 9903
IRF002 9911 9912 9913 9914
求SQL
--try
drop table #
go
create table #([partnum] nvarchar(6),[die] nvarchar(5))
Insert #
select N'IRF001','9901' union all
select N'IRF001','9902' union all
select N'IRF001','9903' union all
select N'IRF002','9911' union all
select N'IRF002','9912' union all
select N'IRF002','9913' union all
select N'IRF002','9914' union all
select N'IRF003','9921' union all
select N'IRF003','9922' union all
select N'IRF003','9923' union all
select N'IRF003','9924' union all
select N'IRF003','9925' union all
select N'IRF004','9931' union all
select N'IRF004','9932' union all
select N'IRF004','9933' declare @icount int ,@i int
declare @sql nvarchar(max)
select @i = 1,@icount = max(c) from ( select count(1) as c from # group by [partnum]) a
set @sql =' select [partnum] '
while @i <=@icount
begin
set @sql = @sql + ', max(case when px ='+convert(nvarchar(2),@i)+' then [die] else 0 end) as [die'++convert(nvarchar(2),@i)++']'
set @i = @i + 1
end
set @sql = @sql + ' from ( Select px=(select count(1) from # where [partnum] = t.[partnum] and [die]<=t.[die]),* from # t ) t group by [partnum] 'exec (@sql)
(15 row(s) affected)
partnum die1 die2 die3 die4 die5
------- ----------- ----------- ----------- ----------- -----------
IRF001 9901 9902 9903 0 0
IRF002 9911 9912 9913 9914 0
IRF003 9921 9922 9923 9924 9925
IRF004 9931 9932 9933 0 0
(4 row(s) affected)
*/