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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.