rows into columns

Question

rows into columns

justforgroups2004

Old Hand

Points: 315
More actions
March 15, 2012 at 1:20 pm

#98232

Hi Everybody!
Looking for a solution that would create columns out of rows.
Here's sample data:
-----------------------------------------
PeriodCustomerAmount
12/1/2011 12:00:00 AMJoe150
11/1/2011 12:00:00 AMJoe150
11/1/2011 12:00:00 AMDavid320
...
9/1/2011 12:00:00 AMSteve24
6/1/2011 12:00:00 AMBob45
8/1/2011 12:00:00 AMSteve60
...
-----------------------------------------
Would like output similar to this:
-----------------------------------------
Customer6-2011...8-20119-2011...11-201112-2011
Joe150150
David320
...
Steve6024
Bob60
-----------------------------------------
I would try Pivot in SSIS, but the Period column is dynamic. We run the report every month, and it brings 13 months worth of data. So, this month the Period would contain values from February 2011 to February 2012 (02-2011, 03-2011 … 02-2012), and next month - 03-2011, 04-2011 … 02-2012
Any input would be greatly appreciated.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 1

justforgroups2004 (3/15/2012)
Hi Everybody!
Looking for a solution that would create columns out of rows.
Here's sample data:
-----------------------------------------
PeriodCustomerAmount
12/1/2011 12:00:00 AMJoe150
11/1/2011 12:00:00 AMJoe150
11/1/2011 12:00:00 AMDavid320
...
9/1/2011 12:00:00 AMSteve24
6/1/2011 12:00:00 AMBob45
8/1/2011 12:00:00 AMSteve60
...
-----------------------------------------
Would like output similar to this:
-----------------------------------------
Customer6-2011...8-20119-2011...11-201112-2011
Joe150150
David320
...
Steve6024
Bob60
-----------------------------------------
I would try Pivot in SSIS, but the Period column is dynamic. We run the report every month, and it brings 13 months worth of data. So, this month the Period would contain values from February 2011 to February 2012 (02-2011, 03-2011 … 02-2012), and next month - 03-2011, 04-2011 … 02-2012
Any input would be greatly appreciated.

The following article has step by step instructions for such a thing.

http://www.sqlservercentral.com/articles/Crosstab/65048/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

justforgroups2004 Old Hand Points: 315 More actions · Answer 2

That looks cool. Will give a try next week.

Thank you, Jeff Moden! 🙂

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 3

You bet. If you get the chance, let us know how it pans out for you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

justforgroups2004 Old Hand Points: 315 More actions · Answer 4

Worked like a dream! 😎 Totally amazing.

Do you think it'll survive ForEachLoop in SSIS (SQL command text filed of a source query to be exact)?

Something like this:

--------------------------------------

......

SELECT @SQL3 =

' SUM(Total) AS Total

FROM (--==== Derived table "d" does preaggregation by whole month

SELECT DATEADD(mm,DATEDIFF(mm,0,SomeDate),0) AS MonthDate,

SomeLetters2,

SUM(SomeMoney) AS Total

FROM dbo.JBMTest

WHERE SomeDate >= ' + QUOTENAME(@StartDate,'''') + '

AND SomeDate < ' + QUOTENAME(@EndDate,'''') + '

--SomeExtraField - to get a Parameter for multiple report runs

AND SomeExtraField =?

GROUP BY DATEADD(mm,DATEDIFF(mm,0,SomeDate),0), SomeLetters2

) d

GROUP BY SomeLetters2 WITH ROLLUP

'

...

--------------------------------------

Thanks a MILLION!!!! 🙂

justforgroups2004 Old Hand Points: 315 More actions · Answer 5

🙁

Look like it doesn't work inside the OLE DB Source of SSIS (?)

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 6

justforgroups2004 (3/22/2012)
🙁
Look like it doesn't work inside the OLE DB Source of SSIS (?)

Ummmm... why are you doing this type of stuff in SSIS? And, if you absolutely need to, turn the code into a stored procedure and have SSIS call it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Cadavre SSC-Forever Points: 41690 More actions · Answer 7

If this needs to be done in SSIS, I'm fairly sure that it has PIVOT/UNPIVOT operators in the data flow task.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/