请教一个横向连接的语句
各位大哥,请教一个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
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(ID varchar(10),NAME varchar(10),SEX varchar(10))
insert into tb(ID,NAME,SEX) values( '1 ', 'MM ', 'M ')
insert into tb(ID,NAME,SEX) values( '1 ', 'TT ', 'M ')
insert into tb(ID,NAME,SEX) values( '1 ', 'YY ', 'M ')
insert into tb(ID,NAME,SEX) values( '1 ', 'PP ', 'M ')
go
declare @sql varchar(8000)
set @sql = 'select id '
select @sql = @sql + ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then name end) [ ' + 'name ' + cast(px as varchar) + '] '
+ ' , max(case px when ' ' ' + cast(px as varchar) + ' ' ' then sex end) [ ' + 'sex ' + cast(px as varchar) + '] '
from (select distinct px from (select px=(select count(1) from tb where id=a.id and name <a.name)+1 , * from tb a) t) as a
set @sql = @sql + ' from (select px=(select count(1) from tb where id=a.id and name <a.name)+1 , * from tb a) t group by id '
exec(@sql)
drop table tb
/*
id name1 sex1 name2 sex2 name3 sex3 name4 sex4
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
1 MM M PP M TT M YY M
警告: 聚合或其它 SET 操作消除了空值。
*/
