Pivot transform with multiple value columns

Question

Pivot transform with multiple value columns

padre

SSChasing Mays

Points: 648
More actions
January 14, 2010 at 3:05 pm

#209135

Hello,
I am trying to pivot the following:
RowId DataElementId NumberValue DateValue
1 1 23
1 2 123
1 3 1/12/2010
2 1 12
2 2 9879
2 3 1/10/2010
Into this:
RowId QtyShipped QtyOrdered AuditDate
1 23 123 1/12/2010
2 12 9879 1/10/2010
Can this be done within a single pivot task?
Thanks,
Padre

Viewing 5 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Something like this? (Please note how I set up the sample date to be ready to use for others...)

Even though SQL Server has several ways of getting your requested results, i doesn't mean that the table structure you're using would be called "best practise". I strongly recommend normalizing your data!

DECLARE @t TABLE (RowId INT ,DataElementId INT , NumberValue INT ,DateValue datetime)

INSERT INTO @t

SELECT 1, 1, 23,null UNION ALL

SELECT 1, 2, 123,null UNION ALL

SELECT 1, 3,null, '1/12/2010' UNION ALL

SELECT 2, 1, 12,null UNION ALL

SELECT 2, 2, 9879,null UNION ALL

SELECT 2, 3,null, '1/10/2010'

SELECT

rowid,

max(CASE WHEN DataElementId=1 THEN NumberValue ELSE NULL END ) AS QtyShipped,

max(CASE WHEN DataElementId=2 THEN NumberValue ELSE NULL END ) AS QtyOrdered,

max(CASE WHEN DataElementId=3 THEN DateValue ELSE NULL END ) AS AuditDate

FROM @t

GROUP BY rowid

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

padre SSChasing Mays Points: 648 More actions · Answer 2

The data I provided was just an example to make the point. I am very familiar with the T-SQL solution suggested and use it frequently. 🙂 I have to get this working in SSIS though. I know that I can get this done chaining together a number of transforms but I was hoping that the Pivot transform could save me some work.

Thanks,

Padre

LutzM SSC Guru Points: 107049 More actions · Answer 3

I used Google with "ssis pivot transformation" which brought this site[/url] back as the first hit.

Seems to answer your question....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

padre (1/14/2010)
Hello,
I am trying to pivot the following:
RowId DataElementId NumberValue DateValue
1 1 23
1 2 123
1 3 1/12/2010
2 1 12
2 2 9879
2 3 1/10/2010
Into this:
RowId QtyShipped QtyOrdered AuditDate
1 23 123 1/12/2010
2 12 9879 1/10/2010
Can this be done within a single pivot task?
Thanks,
Padre

I know you already have an answer and I also know that you're probably not the one that designed that mess. Any chance of you talking to the folks that provide the data in such a terrible fashion and get them to normalize it a bit? That would be the way to go.

--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)