重复查询，并列显示

[u010703853]

如下图，图中黄色部分为数据原表，绿色部分是我想得到的查询结果

请问 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)
*/

[Ekun_sky]

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

  

[时间都去哪了啊]

引用 1 楼 ky_min 的回复:

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)

OK 谢谢