类似于行转列的一种需求

在 工作中 遇到这样一种需求:

在群里问了下朋友,朋友 SQL 代码如下:

 

create table dou
(
number nvarchar(10),
no int,
name nvarchar(10),
height int
)
 

insert into dou
select 'AAA',1,'张三',120
union all select 'AAA',1,'李四', 115
union all select 'AAA',2,'王五', 112
union all select 'AAA',3,'赵六', 125
union all select 'AAA',3,'周八', 132
union all select 'AAA',3,'刘九', 136
union all select 'AAA',3,'草根', 136
union all select 'BBB',1,'B第一', 132
union all select 'BBB',2,'B第二', 136
union all select 'CCC',1,'C第一', 136
go

--SELECT * FROM dou

if object_id('tempdb..#t') is not null Begin  
drop table #t  
End 

create table #t (row int, number nvarchar(10))

-- 列
declare @maxrow int
set @maxrow = 0
select @maxrow = max(no) from dou

declare @i int
set @i =1 
declare @tempsql nvarchar(max)
while @i <= @maxrow
begin
    set @tempsql = 'alter table #t add no'+cast(@i as nvarchar(10))+' int;'
    set @tempsql = @tempsql + 'alter table #t add name'+cast(@i as nvarchar(10))+' nvarchar(10);'
    set @tempsql = @tempsql + 'alter table #t add height'+cast(@i as nvarchar(10))+' int;'
    exec(@tempsql)
    set @i = @i +1
end

-- 行
declare @maxcol int
set @maxcol = 0
select @maxcol = max(couno) from (select no,count(no) couno from dou group by no ) a

declare @sqlinsone nvarchar(max)
set @sqlinsone ='insert into #t (row,number,no'+cast(@maxrow as varchar)+',name'+cast(@maxrow as varchar)+',height'+cast(@maxrow as varchar)+') select row_number() over (order by getdate()),number,no,name,height from dou where no =' + cast(@maxrow as varchar)
exec(@sqlinsone)

set @i =1 
while @i <= @maxrow
begin
    set @sqlinsone = 'update a set no'+cast(@i as varchar)+' = no,name'+cast(@i as varchar)+' =name,height'+cast(@i as varchar)+' = height from #t a inner join (select * ,row_number() over (order by getdate()) row from dou where no = '+cast(@i as varchar)+') b on a.number = b.number and a.row = b.row'
    exec(@sqlinsone)
    set @i = @i +1
end

select * from #t
drop table #t

结果如下,但只显示 AAA 的,BBB的没有显示(在WHERE 条件中 限制了 NUBLER 的条件 ,一次只查一个内容):

 

create table dou
(
number nvarchar(10),
no int,
name nvarchar(10),
height int
)
go

insert into dou
select 'AAA',1,'张三',120
union all select 'AAA',1,'李四', 115
union all select 'AAA',2,'王五', 112
union all select 'AAA',3,'赵六', 125
union all select 'AAA',3,'周八', 132
union all select 'AAA',3,'刘九', 136
union all select 'AAA',3,'草根', 136
union all select 'BBB',1,'草根1', 136
union all select 'BBB',2,'草根2', 136
union all select 'BBB',3,'草根3', 136
union all select 'BBB',2,'草根4', 136
union all select 'CCC',1,'草根5', 136
union all select 'CCC',2,'草根6', 136
go

if object_id('tempdb..#t') is not null Begin  
drop table #t  
End 

create table #t (row int, number nvarchar(10))

-- 列
declare @maxrow int
set @maxrow = 0
select @maxrow = max(no) from dou

declare @i int
set @i =1 
declare @tempsql nvarchar(max)
while @i <= @maxrow
begin
	set @tempsql = 'alter table #t add no'+cast(@i as nvarchar(10))+' int;'
	set @tempsql = @tempsql + 'alter table #t add name'+cast(@i as nvarchar(10))+' nvarchar(10);'
	set @tempsql = @tempsql + 'alter table #t add height'+cast(@i as nvarchar(10))+' int;'
	exec(@tempsql)
	set @i = @i +1
end

-- 一共几种类型
declare @type int
declare @number nvarchar(10)
select @type = count(*) from ( select number from dou group by number) a
declare @j int
set @j = 1
while @j <= @type
begin 
	;with cte as
	(
		select number,max(no) no,row_number() over (order by getdate()) row from dou group by number
	)
	select @maxrow = no,@number = number from cte where row = @j

	-- 行
	declare @maxcol int
	set @maxcol = 0
	select @maxcol =max(no) from dou where number = 'BBB' 

	declare @sqlinsone nvarchar(max)
	set @sqlinsone ='insert into #t (row,number,no'+cast(@maxrow as varchar)+',name'+cast(@maxrow as varchar)+',height'+cast(@maxrow as varchar)+') select row_number() over (order by getdate()),number,no,name,height from dou where no =' + cast(@maxrow as varchar) + ' and number = ''' + @number + ''''
	exec(@sqlinsone)

	set @i =1 
	while @i <= @maxrow
	begin
		set @sqlinsone = 'update a set no'+cast(@i as varchar)+' = no,name'+cast(@i as varchar)+' =name,height'+cast(@i as varchar)+' = height from #t a inner join (select * ,row_number() over (order by getdate()) row from dou where no = '+cast(@i as varchar)+' and number = '''+@number+''') b on a.number = b.number and a.row = b.row where b.number = ''' + @number + ''''
		exec(@sqlinsone)
		set @i = @i +1
	end

	set @j = @j + 1
end

select * from #t
drop table #t

　　这是第二种,显示如下: