如下图，图中黄色部分为数据原表，绿色部分是我想得到的查询结果
请问 SQL语句怎么写
------解决思路----------------------
DECLARE @SQL VARCHAR(8000)
DECLARE @M INT,@I VARCHAR(10)
SET @SQL='SELECT MAX(CASE WHEN RN=1 THEN NO END)NO'
SELECT TOP 1 @M=COUNT(NO)FROM TB GROUP BY NAME ORDER BY COUNT(NO) DESC
SET @I=2
WHILE @I<=@M
SELECT @SQL=@SQL+',MAX(CASE WHEN RN='''+@I+''' THEN NO END)[NO'+@I+']',@I=@I+1
SET @SQL=@SQL+',NAME FROM(SELECT *,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NO)RN FROM TB)T GROUP BY NAME'
EXEC(@SQL)
------解决思路----------------------
create table u01(NO int,NAME varchar(10))
insert into u01
select 101,'AAA' union all
select 102,'BBB' union all
select 103,'CCC' union all
select 104,'DDD' union all
select 200,'AAA' union all
select 300,'CCC'
select isnull(rtrim([1]),'') 'NO1',
isnull(rtrim([2]),'') 'NO2',
NAME
from (select *,row_number() over(partition by NAME order by NO) 'rn'
from u01) a
pivot(max(NO) for rn in([1],[2])) u
/*
NO1 NO2 NAME
------------ ------------ ----------
101 200 AAA
102 BBB
103 300 CCC
104 DDD
(4 row(s) affected)
*/
------解决思路----------------------
use master
go
if OBJECT_ID('a') is not null drop table a
go
create table a(no int,name varchar(50))
go
insert into a
select 101,'aaa' union all
select 102,'bbb' union all
select 103,'ccc' union all
select 104,'ddd' union all
select 200,'aaa' union all
select 300,'ccc'
go
declare @i varchar(8000),@sql varchar(8000)
set @i=''
set @sql=''
select @i=@i+',[No'+LTRIM(id)+']' from
(select distinct ROW_NUMBER()over(partition by name order by name)
as id from a) b
set @sql='select * from
(select *,''No''+ltrim(ROW_NUMBER()over(partition by name order by name))
as tid from a) as c pivot (max(no) for tid in ('+stuff(@i,1,1,'')+')) as d'
exec(@sql)
/**
name No1 no2
------------------
aaa 101 200
bbb 102 NULL
ccc 103 300
ddd 104 NULL
------------------
**/