Attempting to transpose/pivot columns to rows... I think.

I have something of a challenge for you folks (at least it's very challenging for me).

I have a table that has data that looks like this:

DateOrderNum_WorkDay

2006-06-1__AA1

2006-06-1__AB1

2006-06-2__BA2

2006-06-2__BB2

2006-06-2__BC2

2006-06-5__CA3

2006-06-5__CB3

etc.

So, there are dates that orders happened and each date is marked as the 1, 2, 3, etc. business day of the month.

What I am trying to write is a SQL statement that will display results thus:

Day1Day2Day3_etc.

AA__BA_CAetc.

AB__BB_CBetc.

Is this making any sense to anyone? Basically, I need to turn the WorkDay rows into columns and display all the info for each WorkDay under them.

I have a feeling this isn't hard, but I am fairly new to writing SQL so any advice would be pure gold to me. Thanks!

2 questions.

1 - What version of SQL Server are you using? Is it 2005?

2- Is the set of Work days dynamic, or do you know the specific work days when you are writing the query. More specifically, do the columns in the result need to be generated dynamically from the data in the table?

|||

Ryan,

Thanks for the quick reply! Here are the answers to your questions:

1. I'm using SQL Server 2005 (and I just found the PIVOT command which is new in 2005 apparently).

2. The set of workdays is dynamic. Depending on the size of the month and holidays there could be as few as 20 or as many as 23. And if I understand your second question then yes the columns will need to be generated dynammically. The data in the table will be changing daily and this SQL statement is generating a grid view on a web page.

Thanks again!

|||

Unfortunately, the dynamic aspect of your query makes it pretty complicated.

The PIVOT function is nice, but it requires that you know the resulting columns when you build the SQL. So, what you need to is create a stored proc that dynamically builds the PIVOT sql syntax by looping through the data in your table.

I know of a couple of good articles with examples of doing this (all-be-it using the old SQL 2000 SQL pivot logic).

A good article on simple PIVOTS. http://www.aspfaq.com/show.asp?id=2462

That article has a link to 2 dynamic pivot articles. I think the first is the best for you. http://www.sqlmag.com/Articles/Index.cfm?ArticleID=15608

If I were you, I'd consider re-writing this for the new PIVOT function, and perhaps someone here already has. If this doesn't work, I could probably find some time to do this for you. Let me know. But, I think just using the old version described in this article should work.

|||

Thanks again, Ryan! You have a wealth of knowledge.

I am doing this for a project that needs to be ready this Wednesday, so I may not have enough time to construct this myself by then. However, I am loathe to have you do my work for me. So, I will probably do a quick and dirty view on the data and over the next week or so wrap my mind around your links and other advice. If I do need further advice I will post to this thread and hope you check back now and then.

Thanks for everything!

|||

Hi there Ryan,

I've seen you've gave a solution in case of SQL Server 2005. Do you have a solution for 2000?

Thanks in advance

|||You can have the best example here:
http://orafaq.com/node/1871|||

Code Snippet

If you use SQL Server 2005,

use tempdb

go

create table data (

[date] datetime ,

[ordernum] varchar(100) ,

[workday] int

);

go

insert into data values('2006-06-1','AA','1');

insert into data values('2006-06-1','AB','1');

insert into data values('2006-06-2','BA','2');

insert into data values('2006-06-2','BB','2');

insert into data values('2006-06-2','BC','2');

insert into data values('2006-06-5','CA','3');

insert into data values('2006-06-5','CB','3');

go

;with cte

as

(

select *, row_number() over(partition by date order by ordernum) rid from data

)

select

isnull([1],'') as day1

, isnull([2],'') as day2

, isnull([3],'') as day3

, isnull([4],'') as day4

, isnull([5],'') as day5

, isnull([6],'') as day6

, isnull([7],'') as day7

from

(

select

workday

, rid

, ordernum

from

cte

) as data

pivot

(

max(ordernum)

for workday in

([1],[2],[3],[4],[5],[6],[7] /*.....,[n]*/)

)as pvt