22,300
社区成员




IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
IF OBJECT_ID('TEMPDB..#T') IS NOT NULL DROP TABLE #T
GO
create table tb(id varchar(20),规格1 varchar(10),规格2 VARCHAR(10))
insert into tb values('1','D','D')
insert into tb values('1','A','D')
insert into tb values('1','B','D')
insert into tb values('2','B','D')
insert into tb values('2','C','D')
insert into tb values('2','E','D')
GO
SELECT *,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY GETDATE()) AS NID
INTO #T
FROM TB
DECLARE @STR VARCHAR(MAX)
SELECT @STR=ISNULL(@STR+'
','SELECT ID
')+',MAX(CASE WHEN NID='+LTRIM(NID)+' THEN [规格1] ELSE NULL END) AS [规格1-'+LTRIM(NID)+']
,MAX(CASE WHEN NID='+LTRIM(NID)+' THEN [规格2] ELSE NULL END) AS [规格2-'+LTRIM(NID)+']'
FROM #T
GROUP BY NID
ORDER BY NID
EXEC (@STR+'
FROM #T
GROUP BY ID')
/*
ID 规格1-1 规格2-1 规格1-2 规格2-2 规格1-3 规格2-3
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
1 D D A D B D
2 B D C D E D
*/
--SQL2000
create table tb(id varchar(20),规格1 varchar(10),规格2 VARCHAR(10))
set nocount on
insert into tb values('1','D','D')
insert into tb values('1','A','D')
insert into tb values('1','B','D')
insert into tb values('2','B','D')
insert into tb values('2','C','D')
insert into tb values('2','E','D')
set nocount off
go
declare @str varchar(4000)
set @str = 'select id'
select *,sid=identity(int,1,1)
into #tc
from tb
select *,(select count(*) from #tc where id=t.id and sid<=t.sid) as rid
into #tb
from #tc t
select @str = @str + ',max(case rid when ' + ltrim(rid) + ' then 规格1 else '''' end) [规格1]'
+ ',max(case rid when ' + ltrim(rid) + ' then 规格2 else '''' end) [规格2]'
from (select distinct rid from #tb)t
select @str = @str + ' from #tb group by id'
exec(@str)
drop table tb,#tc,#tb
/************
id 规格1 规格2 规格1 规格2 规格1 规格2
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
1 D D A D B D
2 B D C D E D
(2 行受影响)
create table tb(id varchar(20),规格1 varchar(10),规格2 VARCHAR(10))
set nocount on
insert into tb values('1','D','D')
insert into tb values('1','A','D')
insert into tb values('1','B','D')
insert into tb values('2','B','D')
insert into tb values('2','C','D')
insert into tb values('2','E','D')
set nocount off
go
declare @str varchar(4000)
set @str = 'select id'
select *,rid=row_number() over (partition by id order by getdate())
into #tb
from tb
select @str = @str + ',max(case rid when ' + ltrim(rid) + ' then 规格1 else '''' end) [规格1]'
+ ',max(case rid when ' + ltrim(rid) + ' then 规格2 else '''' end) [规格2]'
from (select distinct rid from #tb)t
select @str = @str + ' from #tb group by id'
exec(@str)
drop table tb,#tb
/************
id 规格1 规格2 规格1 规格2 规格1 规格2
-------------------- ---------- ---------- ---------- ---------- ---------- ----------
1 D D A D B D
2 B D C D E D
(2 行受影响)