Column convert to row issue - 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

Column convert to row issue

Author

Topic

thomson
Starting Member

9 Posts

Posted - 2009-03-05 : 16:09:22

I am looking for a solution. convert one column to row's title,please see 2 tables below

Table 1
userid name
001 Geoge
002 John
....

Table 2
calendar
2009-02-03
2009-02-04
....

The final table I want to looks like
userid name 2009-02-03 2009-02-04
001 Geoge
002 John
...
Thanks

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-03-05 : 16:21:08

Research the PIVOT function

Some days you're the dog, and some days you're the fire hydrant.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-05 : 16:25:36

[code]Declare @T table
(userid char(4),names varchar(20))
Insert @T
select '001', 'Geoge' union all
Select '002', 'John'

Declare @M table
(calendar datetime)
Insert @M
Select '2009-02-03' union all
Select '2009-02-04'

Select Z.Userid,Z.names,r.[2009-02-03],r.[2009-02-04]
from
(Select Userid,names from @T)Z
outer apply
(Select Max(Case when Calendar = '2009-02-03' then '' Else Null end)as [2009-02-03],
Max(Case when Calendar = '2009-02-04' then '' Else Null end)as [2009-02-04]
from @M)r[/code]

If you want dynamic,you have to search for dynamic cross tab

thomson
Starting Member

9 Posts

Posted - 2009-03-05 : 17:13:22

I have a SQL SERVER 2005 table with 3 columns: Operator, Shift, and Date.
Sample rows are :
Operator Shift Date
John Early Oct. 1, 2008
Mary Late Oct. 1, 2008
John Late Oct. 2, 2008
Mary Early Oct. 2, 2008

How can i use PIVOT or Crosstab to create a SQL SERVER 2005 report that lists ALL the days of a month as columns and the operators as rows - one row for each operator. More importantly in the row-column intersection, I will like to see Shifts instead of aggregate data (as the shift field is non numeric). I want something like this:

Oct 1 Oct 2 Oct3 ..........
John Early Late
Mary Late Early
Thanks all

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-05 : 23:56:38

try this one
using pivot
select operator, [2008-oct-1],[2008-oct-2],..........
from
tablename
pivot (max(shift) for date in ([2008-oct-1],[2008-oct-2],.......))pvt

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-05 : 23:57:38

by dynamic cross tab
declare @tbl table(Operator varchar(32),Shift varchar(32),Date datetime)
insert into @tbl select 'John', 'Early','2008-oct-1'
insert into @tbl select 'Mary', 'Late', '2008-oct-1'
insert into @tbl select 'John', 'Late','2008-oct-2'
insert into @tbl select 'Mary', 'Early', '2008-oct-2'

select operator,
max(case when date = '2008-oct-1' then shift end) as '2008-oct-1',
max(case when date = '2008-oct-2' then shift end) as '2008-oct-2'
from @tbl
group by operator

see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources