疑难问题求解

[soulJava]

表结构
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')
要求查询结果
id 规格1 规格2 规格1 规格2 规格1 规格2
1 D D A D B D
2 B D C D E D

即：相同id号的规格1与规格2的查询结果在一行显示，id号相同的行数不确定

[-晴天]

行转列..........

[AcHerat]

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 行受影响)

[mingpei0703]

[Quote=引用 1 楼 qianjin036a 的回复:]
行转列..........
[/Quote]
晴天很反感行转列了

[AcHerat]

--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 行受影响)

[guguda2008]

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

[soulJava]

懂了,谢了各位,虽然有想到动态行转列,弄了半天没弄出来,学习哈,结贴给分!~