求SQL语句的实现

[czx33859066]

表A
ID 编码 名称
1 001 A
1 002 B
1 003 C
2 004 D
2 005 E
3 001 A
3 002 B

结果如下：
ID 编码1 名称1 编码2 名称2 编码3 名称3
1 001 A 002 B 003 C
2 004 D 005 E
3 001 A 002 B

求SQL语句如何实现？

[骑着蜗牛去爬山]

create table #tb(id int,code varchar(10),name varchar(10));
insert into #tb
select 1,'001','A' union all
select 1,'002','B' union all
select 1,'003','C' union all
select 2,'004','D' union all
select 2,'005','E' union all
select 3,'001','A' union all
select 3,'002','B';
declare @maxcode int;
declare @sql nvarchar(1000);
select @maxcode=max(ct) from (select count(1) as ct from #tb group by id)t;
select @sql=isnull(@sql,'')+',max(case when rownum='+rtrim(number)+' then code else '''' end ) as [编码'+rtrim(number)+'],max(case when rownum='+rtrim(number)+' then name else '''' end ) as [名称'+rtrim(number)+']' from master..spt_values where type='P' and number between 1 and @maxcode
select @sql='select id'+@sql+' from (select *,rownum=ROW_NUMBER()over(partition by id order by getdate()) from #tb)t group by id';
exec(@sql);
drop table #tb;

[顺势而为1]

if not object_id(N'Tempdb..#Tmp_Data') is null
    drop table #Tmp_Data
Go

create table #Tmp_Data(ID int, code varchar(10), name varchar(20))
go
insert into #Tmp_Data(ID,code, name)
select 1,'001', 'A' union all
select 1,'002', 'B' union all
select 1,'003', 'C' union all
select 2,'004', 'D' union all
select 2,'005', 'E' union all
select 2,'001', 'A' union all
select 3,'001', 'B' 


DECLARE @sql NVARCHAR(max)=''
DECLARE @i VARCHAR(3)
SELECT TOP 1 @i=COUNT(*) FROM #Tmp_Data GROUP BY ID ORDER BY COUNT(*) DESC
 
WHILE @i>0
    SELECT @sql=',MAX(CASE WHEN RN='+@i+' THEN code ELSE '''' END) as Code'+@i+',MAX(CASE WHEN RN='+@i+' THEN Name ELSE '''' END) AS Name'+@i+@sql,@i-=1

Print @sql


 
EXEC('SELECT ID'+@sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RN FROM #Tmp_Data) AS T GROUP BY ID')
 

[顺势而为1]

上面用错了数据

if not object_id(N'Tempdb..#Tmp_Data') is null
    drop table #Tmp_Data
Go

create table #Tmp_Data(ID int, code varchar(10), name varchar(20))
go
insert into #Tmp_Data(ID,code, name)
select 1,'001', 'A' union all
select 1,'002', 'B' union all
select 1,'003', 'C' union all
select 2,'004', 'D' union all
select 2,'005', 'E' union all
select 3,'001', 'A' union all
select 3,'002', 'B' 

Select * From #Tmp_Data

DECLARE @sql NVARCHAR(max)=''
DECLARE @i VARCHAR(3)
SELECT TOP 1 @i=COUNT(*) FROM #Tmp_Data GROUP BY ID ORDER BY COUNT(*) DESC
 
WHILE @i>0
    SELECT @sql=',MAX(CASE WHEN RN='+@i+' THEN code ELSE '''' END) as Code'+@i+',MAX(CASE WHEN RN='+@i+' THEN Name ELSE '''' END) AS Name'+@i+@sql,@i-=1

 
EXEC('SELECT ID'+@sql+' FROM (SELECT *,ROW_NUMBER()OVER(PARTITION BY ID ORDER BY ID) AS RN FROM #Tmp_Data) AS T GROUP BY ID')
 

[二月十六]

--测试数据
if not object_id(N'Tempdb..#A') is null
	drop table #A
Go
Create table #A([ID] int,[编码] nvarchar(23),[名称] nvarchar(21))
Insert #A
select 1,N'001',N'A' union all
select 1,N'002',N'B' union all
select 1,N'003',N'C' union all
select 2,N'004',N'D' union all
select 2,N'005',N'E' union all
select 3,N'001',N'A' union all
select 3,N'002',N'B'
Go
--测试数据结束
declare @sql varchar(8000)
set @sql='WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY 名称 ) AS num
FROM    #A
) select ID ';
WITH cte AS (
SELECT  * ,
        ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY 名称 ) AS num
FROM    #A
)
select @sql=@sql+' , max(case num when '''+ RTRIM(num) +''' then [编码] else null end) [编码'+ RTRIM(num) +']
 , max(case num when '''+ RTRIM(num) +''' then [名称] else null end) [名称'+ RTRIM(num) +']'
from (select distinct num from cte) as a
set @sql=@sql+' from cte group by ID'
EXEC(@sql)