请教行转列问题~~

[lzfrab]

if object_id('tb') is not null drop table tb
create table tb (id int,name varchar(50),q varchar(50))
insert into tb
select 1,'a','aa11' union all
select 1,'a','aa22' union all
select 2,'b','bb11' union all
select 2,'b','bb22' union all
select 2,'b','bb33' union all
select 3,'c','cc11'

想要结果
id  name  q1    q2    q3    ...
1   a     aa11  aa22
2   b     bb11  bb22  bb33
3   c     cc11
... 

表结构和数据例子都出来了
请不要贴一堆什么什么都有的大代码~~，呵呵，谢谢

[dawugui]

--sql 2000

create table tb (id int,name varchar(50),q varchar(50))
insert into tb
select 1,'a','aa11' union all
select 1,'a','aa22' union all
select 2,'b','bb11' union all
select 2,'b','bb22' union all
select 2,'b','bb33' union all
select 3,'c','cc11'

go

declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then q else '''' end) [q' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where id = t.id and name = t.name and q < t.q) + 1 from tb t ) m) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where id = t.id and name = t.name and q < t.q) + 1 from tb t) m group by id'
exec(@sql) 

drop table tb

/*
id          q1                                                 q2                                                 q3                                                 
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
1           aa11                                               aa22                                               
2           bb11                                               bb22                                               bb33
3           cc11                                                                                                  
*/

[dawugui]

--上面漏了个name
--sql 2000

create table tb (id int,name varchar(50),q varchar(50))
insert into tb
select 1,'a','aa11' union all
select 1,'a','aa22' union all
select 2,'b','bb11' union all
select 2,'b','bb22' union all
select 2,'b','bb33' union all
select 3,'c','cc11'

go

declare @sql varchar(8000)
set @sql = 'select id , name '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then q else '''' end) [q' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where id = t.id and name = t.name and q < t.q) + 1 from tb t ) m) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where id = t.id and name = t.name and q < t.q) + 1 from tb t) m group by id , name'
exec(@sql) 

drop table tb

/*
id          name                                               q1                                                 q2                                                 q3                                                 
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
1           a                                                  aa11                                               aa22                                               
2           b                                                  bb11                                               bb22                                               bb33
3           c                                                  cc11                                                                                                  

*/

[dawugui]

--sql 2005

create table tb (id int,name varchar(50),q varchar(50))
insert into tb
select 1,'a','aa11' union all
select 1,'a','aa22' union all
select 2,'b','bb11' union all
select 2,'b','bb22' union all
select 2,'b','bb33' union all
select 3,'c','cc11'

go

declare @sql varchar(8000)
set @sql = 'select id , name '
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then q else '''' end) [q' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = row_number() over(partition by id , name order by q)from tb t ) m) as a
set @sql = @sql + ' from (select * , px = row_number() over(partition by id , name order by q)from tb t ) m group by id , name'
exec(@sql) 

drop table tb

/*
id          name                                               q1                                                 q2                                                 q3
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
1           a                                                  aa11                                               aa22                                               
2           b                                                  bb11                                               bb22                                               bb33
3           c                                                  cc11                                                                                                  

(3 行受影响)
*/

[wsh236]

搞定！结贴！

[百年树人]

select 
  id,
  name,
  max(case when q like '%11' then q else '' end) as [q1],
  max(case when q like '%22' then q else '' end) as [q2],
  max(case when q like '%33' then q else '' end) as [q3]
from tb
group by id,name

/**
id          name                                               q1                                                 q2                                                 q3                                                 
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- 
1           a                                                  aa11                                               aa22                                               
2           b                                                  bb11                                               bb22                                               bb33
3           c                                                  cc11                                                                                                  

（所影响的行数为 3 行）
**/