Transpose a table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Transpose a table

Author

Topic

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-03-08 : 09:00:56

How to transpose a table. My table is like

taskid date1 hrs1 date2 hrs2 date3 hrs3 emp
1 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 AAA
2 3/3/2007 8.0 3/4/2007 8.5 3/5/2007 8.0 BBB

I want the result as

date1 date2 date3
emp taskid hrs1 hrs2 hrs3

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-08 : 09:16:13

Why?
Can't this be done in front-end?

You want the actual dates as the column header names?

Peter Larsson
Helsingborg, Sweden

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-03-09 : 02:39:34

Yes I want dates as column headers. Actually I am supposed to do this in backend rather doing in frontend.

vmurali
Yak Posting Veteran

88 Posts

Posted - 2007-03-09 : 02:42:30

I want the result as

emp taskid 3/3/2007 3/4/2007 3/5/2007
AAA 1 8.5 8.0 8.5
BBB 2 8.0 8.5 7.5

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-03-09 : 02:55:12

you mean the date1 column in the table all having the same date ? What if there are different date ?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-03-09 : 05:45:14

[code]
create table #src(taskid int,date1 datetime,hrs float,date2 datetime,hrs2 float,date3 datetime,hrs3 float,emp varchar(3))
declare @t table(datecol varchar(10))
declare @col varchar(10)
create table #pivot(emp varchar(3),taskid int)

insert into #src
select 1, '3/3/2007', 8.0, '3/4/2007', 8.5, '3/5/2007', 8.0 ,'AAA'
union
select 2, '3/3/2007', 8.0, '3/4/2007', 8.5, '3/5/2007', 8.0, 'BBB'

insert into @t(datecol)
select convert(varchar(12),date1,101)
from #src
group by date1
union
select convert(varchar(12),date2,101)
from #src
group by date2
union
select convert(varchar(12),date3,101)
from #src
group by date3

declare @cmd nvarchar(4000)

select @cmd=coalesce((@cmd + ','),'') + ('[' + datecol + '] [float]') from @t
order by datecol asc

set @cmd='alter table #pivot
add ' + @cmd
exec sp_executesql @cmd

while exists (select * from @t)
begin
set @col=(select top 1 datecol from @t order by datecol asc)
set @cmd='insert into #pivot(emp,taskid,[' + @col + '])
select emp,taskid,hrs from #src
where datediff(day,date1,''' + @col + ''')=0 '
exec sp_executesql @cmd

set @cmd='update #pivot
set [' + @col + ']=hrs2
from #pivot p join #src s on p.emp=s.emp and p.taskid=s.taskid
where datediff(day,date2,''' + @col + ''')=0 '
exec sp_executesql @cmd

set @cmd='update #pivot
set [' + @col + ']=hrs3
from #pivot p join #src s on p.emp=s.emp and p.taskid=s.taskid
where datediff(day,date3,''' + @col + ''')=0 '
exec sp_executesql @cmd

delete from @t where datecol=@col
end

select * from #pivot

drop table #pivot
drop table #src

[/code]

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources