PIVOT May be the answer but help is needed with this conundrum.

Question

PIVOT May be the answer but help is needed with this conundrum.

johnnycash

Ten Centuries

Points: 1172
More actions
April 18, 2014 at 1:23 pm

#396627

Hello to all SQL gurus here.
I have a contracts table with primary key and an Items table referencing the contract table. The Items table has a PK as well. I need to produce a view that will only have one row per contractID with N columns containing the ItemIDs that are part of the ContractID. I thought about using PIVOT but I am at a loss. I looked at BOL and a script in SQL server central and it seems to me that one should know the value of the column you are trying to PIVOT on. In this case, a contract may have 0-100 items and each item will have its own ID and there is no way I could know that, or do I?
My view should come up like this:
ContractID | Item01 |Item02 | Item03 | ....Item100
The ItemXX column should have the ITEMID or 0 when the contractID only has certain number of items.
As always, your help and advice will be highly appreciated and your name will go up in my Wall of Fame 😀
Thanks you!

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

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

johnnycash (4/18/2014)
Hello to all SQL gurus here.
I have a contracts table with primary key and an Items table referencing the contract table. The Items table has a PK as well. I need to produce a view that will only have one row per contractID with N columns containing the ItemIDs that are part of the ContractID. I thought about using PIVOT but I am at a loss. I looked at BOL and a script in SQL server central and it seems to me that one should know the value of the column you are trying to PIVOT on. In this case, a contract may have 0-100 items and each item will have its own ID and there is no way I could know that, or do I?
My view should come up like this:
ContractID | Item01 |Item02 | Item03 | ....Item100
The ItemXX column should have the ITEMID or 0 when the contractID only has certain number of items.
As always, your help and advice will be highly appreciated and your name will go up in my Wall of Fame 😀
Thanks you!

Sure this can be done with an unknown number of columns. The way to do this is using a cross tab. There are two articles in my signature that discuss cross tabs. Read the first one to understand the basics, then move to the second one for the dynamic version.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

johnnycash Ten Centuries Points: 1172 More actions · Answer 2

Sean,

Thanks for the links. I learned a bit more today. But I believe I am too "squared-eyed" already and thus not being able to construct my query accordingly. The articles describe using aggregates to summarize the desired results. I do not want summaries, I need a row per contractID with N columns with the ItemIDs associated with that ContractID. Here's why I get with a simple select

SELECT contractID, ItemID

FROM tblItems

ORDER By contractID

contractiditemid

1039281108295

1039281108296

1039281108300

1039281108301

1039281108302

1039281108303

1039281108304

1039281108305

1039281108306

1039281108307

so cross tab/pivot needs to show:

1039281 108295 108296 108300 108301 108302 108303 108304 108305 108306 108307

now seeing that the number of items will fluctuate, I am starting to think that a view is not a good idea and instead make a function (as per the article).

What do you think?

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

I guess my question would be to ask why you'd ever want to do this. It's a huge denormalization especially when you could have 100 columns. What would you be using this output for? I ask because it may remove some of the dilema here.

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

johnnycash Ten Centuries Points: 1172 More actions · Answer 4

Hi Jeff,

Yes, you might remove the dilemma here. And obviously I am going about it the wrong way. The root problem is that each contractID has tow separate tables, one for the items and one for the receivables but there is no relationship between the items table and the receivable table other than both tables reference the contractID. When I do an inner join, I am getting multiple receivable items because the contract has multiple items. This is for an A/R report that needs to show some elements from the contract, some key elements from the item(s) and the detail of the receivable item(s). My thoughts were to isolate the items (and the key elements needed) in this proposed view/function and end up with something like this:

SELECT cont.contractID, items.storeid, items.storename, ar.duedate, ar.balance, as.agedate

from contracts cont

join [proposedview] as items on cont.contractid = items.contractid

join aritems as ar on cont.contractid = ar.contractid and ar.balance > 0

right now, with the contract I posted as example, where there are ten items, I am getting 10 receivable items when it should only be one

Your advice?

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

As you point out, you have ten items in the table and you'll get 10 rows in the join for that one ContractID.

What's wrong with that? I'm really asking why you need these items to be all in one row? What is the business reason for this necessity? Even if someone were doing this on a spreadsheet, they likely wouldn't do such a thing all on one row.

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

johnnycash Ten Centuries Points: 1172 More actions · Answer 6

Jeff,

Each of the ten items have key elements I need to report on and the contract only has one receivable item. Let me try to paint a clearer picture:

A Contract is made for 10 items. These 10 items might be fulfilled from different locations; either from 10 locations or maybe 1, it depends on the stock of each location. The sale of these items add up to a lump amount that is entered into a one invoice (receivable item). The business needs to know the A/R running balance of that contract with detailed information as to from where the item or items were fulfilled from so the corresponding inter-companies JEs are created for each of those fulfillment locations.

I know, it is very convoluted but this is the way the existing contract application (and its DB) is designed.

Thoughts?