关于字符串的行转列的sql语句咋写，大家给个思路吧

Question

例如：
号码  姓名
1     张三
1     李四
1     王五
2     赵六
2     田七转换后（固定为5列）号码  姓名1   姓名2   姓名3  姓名4
1     张三    李四    王五
2     赵六    田七请高手指点一下

Answer 1

declare @t table(ID int,name varchar(10))
insert @t values(1,'张三')
insert @t values(1,'李四') 
insert @t values(1,'王五') 
insert @t values(2,'赵六') 
insert @t values(2,'田七') 
;with tb as(
select row=row_number() over(partition by id order by name) ,* from @t)select 号码=ID,
姓名1=isnull((select name from tb where id=a.id and row%4=1 and row/4=a.row/4),''),
姓名2=isnull((select name from tb where id=a.id and row%4=2 and row/4=a.row/4),''),
姓名3=isnull((select name from tb where id=a.id and row%4=3 and row/4=a.row/4),''),
姓名4=isnull((select name from tb where id=a.id and row%4=0 and row/4-1=a.row/4),'')
from tb a group by ID,row/4/*
号码          姓名1        姓名2        姓名3        姓名4
----------- ---------- ---------- ---------- ----------
1           李四         王五         张三         
2           田七         赵六                    (2 行受影响)
*/

Answer 2

create table k (号码 int, 姓名 varchar(10)) 
insert k select 
1 ,   '张三' union all select 
1 ,   '李四' union all select
1 ,   '王五' union all select
2 ,  '赵六'union all select
2 ,   '田七' 
go
select *
from (select '姓名'+rtrim(ROW_NUMBER() over(PARTITION by 号码 order by GETDATE()))  as rn,*from k )z pivot(
max(姓名) for rn in([姓名1],[姓名2],[姓名3],[姓名4])
)k
/*
号码          姓名1        姓名2        姓名3        姓名4
----------- ---------- ---------- ---------- ----------
1           张三         李四         王五         NULL
2           赵六         田七         NULL       NULL
*/

Answer 3

select *,IDENTITY(int,1,1) as rn into # from  kselect 号码,
姓名1=MAX(case when z=1 then 姓名 else '' end ),
姓名2=MAX(case when z=2 then 姓名 else '' end ),
姓名3=MAX(case when z=3 then 姓名 else '' end ),
姓名4=MAX(case when z=4 then 姓名 else '' end )
from (select (select COUNT(*)+1 from # where 号码=z.号码 and z.rn<rn) as z,* from # z) p
group by 号码/*
号码          姓名1        姓名2        姓名3        姓名4
----------- ---------- ---------- ---------- ----------
1           王五         李四         张三         
2           田七         赵六                    
*/

Answer 4

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([号码] int,[姓名] varchar(4))
insert [tb]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'赵六' union all
select 2,'田七'
 
---查询---
declare @sql varchar(8000)
select @sql=isnull(@sql+',','')+'max(case when px='+ltrim(px)+' then 姓名 else '''' end) as [姓名'+ltrim(px)+']'
from (select distinct px=(select count(1)+1 from tb where 号码=t.号码 and 姓名<t.姓名) from tb t) ttset 
  @sql='select 号码,'
      +@sql
      +' from (select *,px=(select count(1)+1 from tb where 号码=t.号码 and 姓名<t.姓名) from tb t) tt group by 号码'exec (@sql)
---结果---
号码          姓名1  姓名2  姓名3  
----------- ---- ---- ---- 
1           李四   王五   张三
2           田七   赵六   

Answer 5

固定为5列
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([号码] int,[姓名] varchar(4))
insert [tb]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'赵六' union all
select 2,'田七'
 
---查询---
select 
  号码,
  max(case when px=1 then 姓名 else '' end) as [姓名1],
  max(case when px=2 then 姓名 else '' end) as [姓名2],
  max(case when px=3 then 姓名 else '' end) as [姓名3],
  max(case when px=4 then 姓名 else '' end) as [姓名4]
from 
  (select *,px=(select count(1)+1 from tb where 号码=t.号码 and 姓名<t.姓名) from tb t) tt 
group by 
  号码
---结果---
号码          姓名1  姓名2  姓名3  姓名4  
----------- ---- ---- ---- ---- 
1           李四   王五   张三   
2           田七   赵六        （所影响的行数为 2 行）

Answer 6

----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2009-11-11 18:40:45
-- Version:
--      Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) 
-- Nov 24 2008 13:01:59 
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([号码] int,[姓名] varchar(4))
insert [tb]
select 1,'张三' union all
select 1,'李四' union all
select 1,'王五' union all
select 2,'赵六' union all
select 2,'田七'
--------------开始查询--------------------------
select 
  号码,
  max(case id when 1 then 姓名 else '' end) as 姓名1,
  max(case id when 2 then 姓名 else '' end) as 姓名2,
  max(case id when 3 then 姓名 else '' end) as 姓名3
from
 (
 select id=row_number() over(partition by 号码 order by getdate()),* from [tb]
 )t
group by
 号码----------------结果----------------------------
/* 号码          姓名1  姓名2  姓名3
----------- ---- ---- ----
1           张三   李四   王五
2           赵六   田七   (2 行受影响)
*/

Answer 7

declare @t table(ID int,name varchar(10))
insert @t values(1,'张三')
insert @t values(1,'李四') 
insert @t values(1,'王五') 
insert @t values(2,'赵六') 
insert @t values(2,'田七') ;with hgo as
(
 select *,row_number() over(partition by ID order by name) rank from @t
)
select ID,
max(case when rank=1 then name else '' end) '姓名1',
max(case when rank=2 then name else '' end) '姓名2',
max(case when rank=3 then name else '' end) '姓名3',
max(case when rank=4 then name else '' end) '姓名4',
max(case when rank=5 then name else '' end) '姓名5'
from hgo group by IDID          姓名1        姓名2        姓名3        姓名4        姓名5
----------- ---------- ---------- ---------- ---------- ----------
1           李四         王五         张三                    
2           田七         赵六                               

Answer 8

修改下declare @t table(ID int,name varchar(10))
insert @t values(1,'张三')
insert @t values(1,'李四') 
insert @t values(1,'王五') 
insert @t values(2,'赵六') 
insert @t values(2,'田七') ;with hgo as
(
 select *,row_number() over(partition by ID order by name) rank from @t
)
select ID,
max(case when rank=1 then name else '' end) '姓名1',
max(case when rank=2 then name else '' end) '姓名2',
max(case when rank=3 then name else '' end) '姓名3',
max(case when rank=4 then name else '' end) '姓名4'
from hgo group by ID
ID          姓名1        姓名2        姓名3        姓名4
----------- ---------- ---------- ---------- ----------
1           李四         王五         张三         
2           田七         赵六