疑难问题求解

soulJava 2011-07-08 02:19:11
表结构
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号相同的行数不确定
...全文
121 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
soulJava 2011-07-08
  • 打赏
  • 举报
回复
懂了,谢了各位,虽然有想到动态行转列,弄了半天没弄出来,学习哈,结贴给分!~
guguda2008 2011-07-08
  • 打赏
  • 举报
回复
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
*/
AcHerat 2011-07-08
  • 打赏
  • 举报
回复

--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 行受影响)
mingpei0703 2011-07-08
  • 打赏
  • 举报
回复
[Quote=引用 1 楼 qianjin036a 的回复:]
行转列..........
[/Quote]
晴天很反感行转列了
AcHerat 2011-07-08
  • 打赏
  • 举报
回复

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 行受影响)
-晴天 2011-07-08
  • 打赏
  • 举报
回复
行转列..........

22,300

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧