Pivot - but for a quite a few splits 1

[SQLScholar]

Hey all,

I have the below query

SELECT TransactionId, SupplierId, TransactionPercentage FROM tblTransaction
ORDER BY TransactionPercentage desc

This would return data like this (this is for one transaction)

TransactionId SupplierId TransactionPercentage
------------- ----------- ---------------------------------------
47608206 15683 18.0000000
47608206 365 15.0000000
47608206 16963 10.0000000
47608206 41 8.0000000
47608206 657 8.0000000
47608206 1008 7.0000000
47608206 465 7.0000000
47608206 10240 7.0000000
47608206 2022 5.0000000
47608206 3917 5.0000000
47608206 18020 5.0000000
47608206 8097 5.0000000


Now however i need to pivot this out so that i have one row per transaction with this data going cross ways. So for this transaction i would have one row, the top supplier id (by percentage - if there is more then one at the same percentage just choose one), then the pecentage for that supplier - then the next Supplier\percentage. The max number i have of suppliers is 12 for one transaction. Suppose to have a little lee way 15 would cover all we would ever need.

So my question.... there are many ways to skin this cat. Whats the cleanest way to do this?

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------

 

[gmmastros]

Can you post what the data would look like after pivoting?

BTW: when posting data, I like to use the TT tag which uses a mono-space font. So something like this...

[ignore]
[tt]
column1 column2
--------- ---------
1 A
2 B
[/tt]
[/ignore]

When posted, it would look like this:

[tt]
column1 column2
--------- ---------
1 A
2 B
[/tt]



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Try this:

Select *
From   (
       Select TransactionId, 
              SupplierId,
              Row_Number() Over (Partition By TransactionId Order By TransactionPercentage DESC) As OrderNumber
       From   tblTransaction
       ) As SourceTable
PIVOT
       (
       Min (SupplierId)
       For OrderNumber in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15])
       ) As PivotTable

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLScholar]

Doh! I didnt think of it like that.

Couple of changes and its working exactly as i wanted (not your code - just my description of what i needed).

Thanks a bunch!

Dan

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

http://www.fountain.me.uk

Be who you are and say what you feel, because those who mind don't matter and those who matter don't mind - Bernard Baruch

Computer Science is no more about computers than astronomy is about telescopes - EW Dijkstra
----------------------------------------