Yet another crosstab query

Question

Post reply

Yet another crosstab query

Luke L

SSC-Dedicated

Points: 35547
More actions
April 13, 2006 at 12:56 pm

#114273

OK folks, I need to dump some data from my database and give it to another company. I just can't get my head wrapped around how I'd do this. Below is how I have the data.
ID                ItemNumber   Field1             Field2               sequenceNumber
(Ident 1,1)    (int4)            (Varchar(150) (varchar(10)     (int4)
--------------------------------------------------------------
1                 123456          BlahBlah          moreblah           1
2                 123456          Blah2Blah2       moreblah2         2
3                 123456          blah3Blah3       moreblah3         3
4                 789789          Blah5Blah5       moreblah           1
5                 789789          blah2Blah2       moreblah2         2
How they want it....
Record
Number Field11     Field21   field12      field22      field13       field23
-------------------------------------------------------------------
123456 BlahBlah    moreblah Blah2Blah2 moreblah2 Blah3Blah3 moreblah3
789789 Blah5Blah5 moreblah Blah2Blah2 moreblah2 <Null>     <Null>
There will never be more than 3 records ber Item number so the sequence numeb will never be > 3.
I tried using the scripts listed in This post, but I wasn't able to quite get the syntax correct for what I'm looking to do.
Any help would be greatly appreciated. Many thanks in advance. -Luke.
To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

So will the sequence always be a value of 1, 2 or 3? You have to have something to control your pivot with.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 2

Yes the sequence will always be 1,2,or 3 and the ItemNumber is what I to pivot on, while the other dtat field need to be strung out into 1 line. Unfortunately it not just a simple sum or max that I'mlooking for. I need each value in the data fields represented per row.

I hope that's more clear.

Thanks -Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Luke L SSC-Dedicated Points: 35547 More actions · Answer 3

In other words, There would be 1 ItemNumber and 6 (2 fields by 3 records) addtional fields per row.

Thanks again.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 4

OK, what you are looking for is simply like this.

SELECT

ItemNumber as Record_Number,

NullIf(Max((CASE WHEN sequenceNumber = 1 THEN Field1 else '' END)),'') AS Field11,

NullIf(Max((CASE WHEN sequenceNumber = 1 THEN Field2 else '' END)),'') AS Field21,

NullIf(Max((CASE WHEN sequenceNumber = 2 THEN Field1 else '' END)),'') AS Field12,

NullIf(Max((CASE WHEN sequenceNumber = 2 THEN Field2 else '' END)),'') AS Field22,

NullIf(Max((CASE WHEN sequenceNumber = 3 THEN Field1 else '' END)),'') AS Field13,

NullIf(Max((CASE WHEN sequenceNumber = 3 THEN Field2 else '' END)),'') AS Field23

FROM

dbo.TheTableName

GROUP BY

ItemNumber

Should do the trick.

Antares686 SSC Guru Points: 125444 More actions · Answer 5

Actually you are pivoting on Sequence Number with the output being consolidated into each Item Number.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 6

That should work perfectly. It's always the simple solutions you look right past.

Thanks a bunch!

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]