T-SQL 2005 Inner join data from a view to the resultset of a stored procedure.
-
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code. For example the original table would look something like this :-
OrderId
ProductCode
Quantitywith data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0So far so good. Now, the problem is that I need to attach this to the result of a view which contains other related details. I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an
Idcolumn of typeBigInt(which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of typeDecimal. Any pointers, gratefully received.Steve Jowett ------------------------- Real Programmers don't need comments -- the code is obvious.
-
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code. For example the original table would look something like this :-
OrderId
ProductCode
Quantitywith data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0So far so good. Now, the problem is that I need to attach this to the result of a view which contains other related details. I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an
Idcolumn of typeBigInt(which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of typeDecimal. Any pointers, gratefully received.Steve Jowett ------------------------- Real Programmers don't need comments -- the code is obvious.
-
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code. For example the original table would look something like this :-
OrderId
ProductCode
Quantitywith data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0So far so good. Now, the problem is that I need to attach this to the result of a view which contains other related details. I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an
Idcolumn of typeBigInt(which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of typeDecimal. Any pointers, gratefully received.Steve Jowett ------------------------- Real Programmers don't need comments -- the code is obvious.
Q1: Is the "other related data" related to the Order ID or to the Products ? Q2: I know I could get alot of grief for this but, What about a Temp table ? Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data. I could probably offer a better answer if Q1 was answered. Regards, David
-
Q1: Is the "other related data" related to the Order ID or to the Products ? Q2: I know I could get alot of grief for this but, What about a Temp table ? Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data. I could probably offer a better answer if Q1 was answered. Regards, David
David Mujica wrote:
Is the "other related data" related to the Order ID or to the Products
The OrderId is the common key.
David Mujica wrote:
Create your Pivot Analysis, store it in a temp table, then process this temp table to add any additional associated data
Can I define a temp table without knowing the column names or the number of columns? My other thought was to create a .NET Extension and have the produce the data resultset required and return a datatable. (never done it before, but I am will to try.)
Steve Jowett ------------------------- Real Programmers don't need comments -- the code is obvious.
-
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code. For example the original table would look something like this :-
OrderId
ProductCode
Quantitywith data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0So far so good. Now, the problem is that I need to attach this to the result of a view which contains other related details. I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an
Idcolumn of typeBigInt(which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of typeDecimal. Any pointers, gratefully received.Steve Jowett ------------------------- Real Programmers don't need comments -- the code is obvious.
While you can join the 2 sets of data (you say the productid is common) by building sub selects and inner joins it will look horrible and will be difficult to support. Go with the temp table or the table var, I often use a table var for pivot stuff because you are usually using dynamic SQL to build the pivot and therefore building the target table code is trivial. Also pivot data tends to be small in volume!
Never underestimate the power of human stupidity RAH
-
I have a stored procedure that pivots data so that I have a row containing an ID column and a number of other columns which are named by a Product Code. For example the original table would look something like this :-
OrderId
ProductCode
Quantitywith data like so...
1000, ProdA, 100
1000, ProdB, 200
1001, Prodc, 50
1002, ProdB, 200Thus my stored procedure would produce a result set of :-
OrderId, ProdA, ProdB, ProdC
1000, 100, 200, 0
1001, 0, 0, 50
1002, 0, 200, 0So far so good. Now, the problem is that I need to attach this to the result of a view which contains other related details. I've researched on the internet, but all the examples I find assume I know the structure (field names) that the Stored Procedure will return. All I know for sure is that there will be an
Idcolumn of typeBigInt(which is common to both the view results and the Stored Procedure results), and a varible number of columns, with names that change, that are of typeDecimal. Any pointers, gratefully received.Steve Jowett ------------------------- Real Programmers don't need comments -- the code is obvious.
More reason not to use stored procedures. Try a table-valued function instead.