August 30, 2007 at 10:59 am
Hi all
I have a problem which would be easily solvable if I could execute dynamic sql from within a UDF. However, this is not possible and so I am wondering if anyone can come up with a different solution.
Here is the problem. I have two sets of data. The first looks like:
Column name: GroupID Type Value
row: 1 Type1 6
row: 1 Type2 7
------------------------------------------------------
My second set of data looks like:
Column name: Type1 Type2
data: 6 7
-------------------------------------------------------
The key feature is that the data in the 'Type' column in the first data set corresponds to the column names in the second data set. Therefore, in dynamic sql I could do:
----------------------------------------------------------------
DECLARE @sql nvarchar(4000)
SET @sql = 'SELECT * FROM 2ndTable WHERE '
SELECT @sql = @sql + Type + '=''' + Value + ''' AND '
FROM 1stTable
SET @sql = SUBSTRING(@SQL, LEN(@SQL) - 4)
------------------------------------------------------------------
If there was just one 'GroupID' in 1stTable it would be easy. However, I want to be able to execute the same for multiple GroupIDs, all in one go.
With UDF's I could simply do 'SELECT udfMyFunction(type,value) FROM 1stTable' but that is not possible.
I therefore need a way of JOINing the first table to the second to produce the result set. If I could join to the results of a stored procedure, that would do it. I have no idea if that is possible though.
I hope I have explained this ok.
Regards
Brad
August 30, 2007 at 11:03 am
I should have added, cursors are very much a last resort. I want to do this as a straight set of joins if possible.
Thanks
Brad
August 30, 2007 at 11:32 am
You are welcome!
-- Prepare sample data
DECLARE
@Table1 TABLE (GroupID INT, Type VARCHAR(10), Value INT)
INSERT
@Table1
SELECT
1, 'Type1', 6 UNION ALL
SELECT
1, 'Type2', 7
DECLARE
@Table2 TABLE (Type1 INT, Type2 INT)
INSERT
@Table2
SELECT
6, 7
-- Show expected output
SELECT
t1.*
FROM
@Table2 AS t2
INNER
JOIN (
SELECT GroupID,
MAX(CASE WHEN Type = 'Type1' THEN Value END) AS Type1,
MAX(CASE WHEN Type = 'Type2' THEN Value END) AS Type2
FROM @Table1
GROUP BY GroupID
) AS t1 ON t1.Type1 = t2.Type1 AND t1.Type2 = t2.Type2
N 56°04'39.16"
E 12°55'05.25"
August 30, 2007 at 11:45 am
Hi Peter
Thanks for this. It works great when I know what the name of table2 is. I have an extra complication in that I don't know up front what table 2 would be. So, my data for table1 looks like:
column name: TableID GroupID Type Value
row: 1 1 Type1 6
row: 1 1 Type2 7
row: 2 2 Type3 8
row: 2 2 Type4 9
row: 3 3 Type5 10
Note that each group can have a variable number of Type/Value pairs, corresponding to the primary key columns of the table identified by TableID.
Apologies for not making that clearer, it didn't occur to me as a stumbling block until I saw your solution.
Many thanks for your herlp.
Brad
August 30, 2007 at 11:51 am
I am now thinking of breaking table1 up so that it is the following:
table1:
column name: GroupID TableID
row: 1 1
row: 2 1
row: 3 2
table2:
column name: GroupID Type Value
row: 1 Type1 6
row: 1 Type2 7
row: 2 Type3 8
row: 2 Type4 9
row: 3 Type5 10
Then, using a while loop to step through the unique GroupIDs in table1, constructing and executing a dynamic sql statement for each iteration.
Brad
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply