请教一个横向连接的语句
各位大哥,请教一个SQL语句
现有一个表TAB如下:
ID       NAME     SEX
1         MM         M
1         TT         M
1         YY         M
1         PP         M
....
我想实现的效果如下:
ID       NAME1     SEX1       NAME2     SEX2     NAME3   SEX3     NAME4   SEX4
1         MM             M               TT           M         YY         M           PP         M

即ID相同的NAME,SEX字段排成一行

------解决方案--------------------
这里有个列子，只需要改一下列名和表名就行了:
create table ta(empid varchar(10), empname varchar(10), regdate datetime,hour int, money decimal(15,2), total decimal(15,2))
insert ta select '0603024 ', '张三 ', '2007-04-05 ', 15.00, 6.00, 90.00
insert ta select '0603024 ', '张三 ', '2007-04-06 ', 23.00, 6.00, 138.00
insert ta select '0603024 ', '张三 ', '2007-04-07 ', 21.00, 6.00, 126.00
insert ta select '0603024 ', '张三 ', '2007-04-08 ', 11.00, 6.00, 66.00
insert ta select '0605020 ', '张三 ', '2007-04-05 ', 6.00, 9.00, 54.00
insert ta select '0605020 ', '李四 ', '2007-04-06 ', 3.00, 9.00, 27.00
insert ta select '0605020 ', '李四 ', '2007-04-07 ', 8.00, 9.00, 72.00
insert ta select '0605020 ', '李四 ', '2007-04-08 ', 10.00, 10.00, 100.00
insert ta select '0605093 ', '王五 ', '2007-04-05 ', 24.00, 6.00, 144.00
insert ta select '0605093 ', '王五 ', '2007-04-06 ', 21.00, 6.00, 126.00
insert ta select '0605093 ', '王五 ', '2007-04-07 ', 12.00, 6.00, 72.00
insert ta select '0605093 ', '王五 ', '2007-04-08 ', 15.00, 6.00, 90.00

go
create proc test_p
as
begin
declare @sql varchar(8000),@sql1 varchar(8000),@sql2 varchar(8000)
set @sql2= 'select *,id=identity(int,1,1)into # from ta '
declare @a int,@b int,@i decimal(15,2)
select @i=count(*)*1.0/3 from ta
select @a=count(*)/3 from ta
set @a=case when @i> @a then @a else @a-1 end
set @b=0
while @b!> @a
begin
set @sql=isnull(@sql+ ' left join ', ' ')+
'(select id,[empid '+rtrim(@b)+ ']=empid,[empname '+rtrim(@b)+ ']=empname,[regdate '+rtrim(@b)+ ']=regdate,[hour '+rtrim(@b)+ ']=hour,[money '+rtrim(@b)+ ']=money,[total '+rtrim(@b)+ ']=total
from # where case when id%3=0 then (id-1)/3 else id/3 end = '+rtrim(@b)+ ') as tmp '+rtrim(@b)
if @b> 0
set @sql=@sql+ ' on tmp '+rtrim(@b)+ '.id%3= '+ 'tmp0.id%3 '
set @sql1=isnull(@sql1+ ', ', 'select ')+ '[empid '+rtrim(@b)+ '],[empname '+rtrim(@b)+ '],[regdate '+rtrim(@b)+ '],[hour '+rtrim(@b)+ '],[money '+rtrim(@b)+ '],[total '+rtrim(@b)+ '] '
set @b=@b+1
end
exec(@sql2+ ' '+@sql1+ ' from '+@sql)
end

go
--测试分列显示
exec test_p
--drop proc test_p
--drop table ta
------解决方案--------------------
i