sql 2000 列转行

Question

user_id dates hour

368 2010-06-02 4.05
368 2010-06-02 4.07
368 2010-06-02 5.05

379 2010-06-02 4.08
379 2010-06-02 4.09
379 2010-06-02 4.10

368 2010-06-03 4.00
368 2010-06-03 4.15
368 2010-06-03 2.01
368 2010-06-03 3.15
368 2010-06-03 1.00

379 2010-06-03 4.04
379 2010-06-03 4.05
379 2010-06-03 4.16

想转成
user_id dates hour_1 hour_2 hour_3 hour_4 hour_5
368 2010-06-02 4.05 4.07 5.05 0 0
368 2010-06-03 4.00 4.15 2.01 3.15 1.00
379 2010-06-02 4.08 4.09 4.10 0 0
379 2010-06-03 4.04 4.05 4.16 0 0

一天每个用户最多5条记录
存储过程 怎么写啊。。。高手帮忙写下

Answer 1

/*
鉴于一天最多只有5条记录,所以我的做法是重新分组给每用户的5条记录分配id,然后用普通的行转列方法处理.
创建存储过程row2col,
把以下过程的"你的表名"改成实际表名(只有一个地方而已)即可,
如果该表有个自增的id列,就不用做(1)这步
*/

CREATE PROCEDURE row2col
@dt datetime
AS

--(1)生成自增id
select id=IDENTITY(INT),*
into #t
from 你的表名
where dates = @dt

--(2)分组增加序号gid
select *,gid=(select count(*) from #t b where a.id >= b.id and a.userid = b.userid and a.dates = b.dates)
into #t2
from #t a
order by userid,dates

--(3)接着就是普通的行转列做法
select userid,dates,max(case when gid = 1 then hour else 0 end) as hour_1,max(case when gid = 2 then hour else 0 end) as hour_2,
max(case when gid = 3 then hour else 0 end) as hour_3,max(case when gid = 4 then hour else 0 end) as hour_4,max(case when gid = 5 then hour else 0 end) as hour_5
from #t2
group by userid,dates

--(4)删除临时表
drop table #t
drop table #t2
go

/*
以上为过程,调用方法:
exec row2col '2010-06-02'

*/
--
已修改为可以传参数了

Answer 2

嗯，这个~~这能用存储过程来实现~~~

CREATE PROCEDURE PivotTrans AS

declare @user_id varchar(5)
DECLARE @dates varchar(10)
DECLARE @work_hour DECIMAL(5,2)
DECLARE @sSQL VARCHAR(1000)

DECLARE @iCount INT
DECLARE @tmpUser VARCHAR(5)
DECLARE @tmpDate VARCHAR(10)

IF EXISTS(SELECT name FROM tempdb..sysobjects WHERE name = '##PivotTBL' AND type = 'U')
DROP TABLE ##PivotTBL
CREATE TABLE ##PivotTBL(
user_id varchar(5) NOT NULL,/**员工代码**/
dates VARCHAR(10) not null,/**日期:YYYY-MM-DD**/
work_hour1 DECIMAL(5,2),/**工作小时**/
work_hour2 DECIMAL(5,2),/**工作小时**/
work_hour3 DECIMAL(5,2),/**工作小时**/
work_hour4 DECIMAL(5,2),/**工作小时**/
work_hour5 DECIMAL(5,2)/**工作小时**/
)
declare tCursor cursor for
select user_id,CONVERT(VARCHAR(10),dates,120) AS dates,work_hour from kaoqin_record ORDER BY user_id,dates

OPEN tCursor

FETCH NEXT FROM tCursor
INTO @user_id, @dates, @work_hour
set @sSQL = ''
SET @iCount = 0
SET @tmpUser = ''
SET @tmpDate = ''
WHILE @@FETCH_STATUS = 0
BEGIN
if @tmpUser <> @user_id OR @tmpDate <> @dates
begin
set @sSQL = 'insert into ##PivotTBL(user_id,dates,work_hour1) values(''' + @user_id + ''',''' + @dates + ''',' + CONVERT(VARCHAR(10),@work_hour) + ')'
SET @iCount = 1
END
ELSE
BEGIN
set @sSQL = 'update ##PivotTBL set work_hour' + CONVERT(VARCHAR(2),@iCount) + ' = ' + CONVERT(VARCHAR(10),@work_hour) + ' where user_id = ''' + @user_id + ''' and dates =''' + @dates + ''''
END
exec(@sSQL)
SET @tmpUser = @user_id
SET @tmpDate = @dates
SET @iCount = @iCount + 1
FETCH NEXT FROM tCursor
INTO @user_id, @dates, @work_hour
END

CLOSE tCursor
DEALLOCATE tCursor

SELECT * FROM ##PivotTBL

go

EXEC PivotTrans

Answer 3

declare @table table (userid varchar(20),dates datetime,hour money)
create table ##table2(userid varchar(20),dates datetime,hour_1 money,hour_2 money,hour_3 money,hour_4 money,hour_5 money)
declare @userid varchar(20),@dates datetime,@hourid varchar(20),@nums int,@hour money,@sql varchar(100)
insert into @table(userid,dates,hour) values('368' ,'2010-06-02' , 4.05)
insert into @table(userid,dates,hour) values('368' ,'2010-06-02' , 4.07)
insert into @table(userid,dates,hour) values('368', '2010-06-02' , 5.05)
insert into @table(userid,dates,hour) values('379', '2010-06-02' , 4.08)
insert into @table(userid,dates,hour) values('379' ,'2010-06-02' , 4.09)
insert into @table(userid,dates,hour) values('379' ,'2010-06-02' , 4.10)
insert into @table(userid,dates,hour) values('368' ,'2010-06-03', 4.00)
insert into @table(userid,dates,hour) values('368' ,'2010-06-03' , 4.15)
insert into @table(userid,dates,hour) values('368' ,'2010-06-03' , 2.01)
insert into @table(userid,dates,hour) values('368' ,'2010-06-03' , 3.15)
insert into @table(userid,dates,hour) values('368' ,'2010-06-03' , 1.00)
insert into @table(userid,dates,hour) values('379','2010-06-03' , 4.04)
insert into @table(userid,dates,hour) values('379','2010-06-03', 4.05)
insert into @table(userid,dates,hour) values('379' ,'2010-06-03' , 4.16)

insert into ##table2(userid,dates)
select distinct userid,dates from @table
set @nums=1
declare mycur cursor for select distinct userid,dates from @table
open mycur
fetch next from mycur into @userid,@dates
while(@@fetch_status=0)
begin
declare mycur2 cursor for select hour from @table where userid=@userid and dates=@dates
open mycur2
fetch next from mycur2 into @hour
while(@@fetch_status=0)
begin
set @sql='update ##table2 set hour_'+cast(@nums as varchar(2))+'='''+cast(@hour as varchar(10))+''' where userid='+
cast(@userid as varchar(20))+' and dates='''+convert(varchar(10),@dates,120)+''''
exec (@sql)
set @nums=@nums+1
fetch next from mycur2 into @hour
end
close mycur2
deallocate mycur2

set @nums=1
fetch next from mycur into @userid,@dates
end
close mycur
deallocate mycur

select * from ##table2

drop table ##table2

Answer 4

--假设上下两张表你都定义好，表面分别为A,B
create table a(user_id int,dates varchar(20), hour varchar(20))
go
create table b(user_id int,dates varchar(20), hour_1 varchar(20), hour_2 varchar(20), hour_3 varchar(20), hour_4 varchar(20), hour_5 varchar(20))
go
--初始数据如下
insert into a(user_Id,dates,hour)
select 368, '2010-06-02' , '4.01' union all
select 368, '2010-06-02' , '4.02' union all
select 368, '2010-06-02' , '4.03' union all
select 368, '2010-06-02', '4.04' union all
select 368, '2010-06-02', '4.05' union all

select 367, '2010-06-02' , '4.06' union all
select 367, '2010-06-02' , '4.07' union all
select 367, '2010-06-02' , '4.03' union all
select 367, '2010-06-02' , '4.05' union all
select 367, '2010-06-02' , '4.15' union all

select 369, '2010-06-02' , '4.03' union all
select 369, '2010-06-02' , '4.16' union all
select 369, '2010-06-02' , '4.15' union all
select 369, '2010-06-02' , '4.02' union all
select 369, '2010-06-02' , '4.08' union all

select 366, '2010-06-02', '4.02' union all
select 366, '2010-06-02', '4.07' union all
select 366, '2010-06-02', '4.15' union all
select 366, '2010-06-02', '4.05' union all
select 366, '2010-06-02', '4.09'
go
--以下存储过程中的参数的的数据类型你要根据具体的表来重新设置一下
--子存储过程
create proc RowToLine1
@curUser_id int
as
declare @hour varchar(20),@hour1 varchar(20)
begin
declare Cur2 cursor for
select hour from A where user_id=@curUser_id
open cur2
fetch next from cur2 into @hour
while (@@fetch_status=0)
begin
--更新hour_1字段
select @hour1=hour_1 from B where user_id=@curUser_id
if @Hour1 is null
begin
update B set Hour_1=@hour where user_id=@curUser_id
fetch next from cur2 into @hour
continue
end
--更新hour_2字段
select @hour1=hour_2 from B where user_id=@curUser_id
if @Hour1 is null
begin
update B set Hour_2=@hour where user_id=@curUser_id
fetch next from cur2 into @hour
continue
end
--更新hour_3字段
select @hour1=hour_3 from B where user_id=@curUser_id
if @Hour1 is null
begin
update B set Hour_3=@hour where user_id=@curUser_id
fetch next from cur2 into @hour
continue
end
--更新hour_4字段
select @hour1=hour_4 from B where user_id=@curUser_id
if @Hour1 is null
begin
update B set Hour_4=@hour where user_id=@curUser_id
fetch next from cur2 into @hour
continue
end
--更新hour_5字段
select @hour1=hour_5 from B where user_id=@curUser_id
if @Hour1 is null
begin
update B set Hour_5=@hour where user_id=@curUser_id
fetch next from cur2 into @hour
end
end
close Cur2
deallocate cur2
end
go
--行列转换
create proc RowToLine
as
declare @curUser_id int
begin
insert into b(user_id,dates,hour_1,hour_2,hour_3,hour_4,hour_5)
select Distinct user_id,dates,null,null,null,null,null
from A

declare Cur cursor for
select distinct user_id from B
open cur
fetch next from cur into @curUser_id
while (@@fetch_status=0)
begin
exec(' exec RowtoLine1 '+@curUser_id)
fetch next from cur into @curUser_id
end
close Cur
deallocate cur
end
go

exec RowToLine
go
select * from b