SQL Output

I have a stored procedure. For design reasons, I can't alter much of the SQL itself. However, if you look at the attached image of the recordset I'll explain what I have and what I need. The SQL itself builds up a table. The data is based on bids from multiple vendors. So, the customer raises a requisition and gets vendors to bid on it. The requisition contains items. Each vendor sends in their price and the winning vendor gets awarded the bid.

SQL table
As the stored procedure runs, this table gets built up. You can see the structure. The record ID is an identity column. The date stamp was added for debugging purposes but isn't important in the output. The first line from nvchColumn1 onwards will always be:

Item Details
Vendor 1
Vendor 2
Vendor 3
Vendor 4
Vendor 5

Where Vendor is the vendors name. After that the items from the requisition are added for each vendor. So in this example, there are 2 vendors for 1 requisition containing 3 items. Rows 2, 3, and 4 show the items for vendor 1 and their prices; rows 5, 6, and 7 show the items for vendor 2.

What I need to get out somehow, is a table that shows the columns:

iRecordId
nvchColumn1
nvchColumn2
nvchColumn3
nvchColumn4
nvchColumn5
nvchColumn6

but only 4 rows. So, what I'm looking for is this output below:

 required.jpg
The first line contains Item Details in the first column, then each of the sccuessive columns has the Vendor name in it. The rows below give the figures.

Any ideas?
LVL 25
Lee SavidgeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you change the table schema? As you have it isn't really the best way to do it.
You should have another table that will replace the columns 2 to 6. There's no need to have a table with multiple columns where most of the time there are only NULL values.
0
Lee SavidgeAuthor Commented:
The problem is that there can be anything from 1 to 5 vendors. The number is arbitrary. Are you suggesting do a table for each vendor and then join them?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, it should be a table for Vendors. Now you have 5 but imagine if in the future you will have 7 or 8? Do you will add more columns so you will also need to change your code to work with those extra new columns?
Using another table it will be already prepared for any number of vendors as the join between the tables will be the same and only thing is the result will may return more rows.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Pawan KumarDatabase ExpertCommented:
can you please provide few rows from the input and the expected output in TEXT FORMAT.

Will try to solve it.
0
Lee SavidgeAuthor Commented:
I created a number of different tables in the stored procedure and then joined them at the end. By joining them in the correct way and aliasing the columns, I have generated the output in the way I need it so that the report I have that runs the SQL doesn't fail.

Thanks Vitor
1
Lee SavidgeAuthor Commented:
Many thanks. Sometimes you just need someone to give you a different perspective on the approach.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Always normalize your databases. This will make building easier queries.
Cheers.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.