Solved

Dynamic Table Function - Part 2

Posted on 2005-05-09
249 Views
Here’s a simplified version of the problem:

tblParent
=======
ParentID – INT identity
ParentName – VARCHAR(50)
ParentTypeID - INT

tblChild
======
ChildID – INT identity
ParentID – INT
ChildName – VARCHAR(50)
ChildValue – VARCHAR(50); for the sake of this example, numbers will be stored as strings

I need a table function (a stored proc might do) that accepts a ParentTypeID value and returns a resultset containing the following:

ParentID, ParentName, cn1, …, cnn

where the column name for cn1 is the value of nth row for ChildName and the value for that column is the value of the nth row for ChildValue.

For example
tblParent
ParentID           ParentName   ParentTypeID
1                     MyPond          1
2                     YourPond       1
3                     MyTrashCan   2

tblChild
ChildID  ParentID            ChildName        ChildValue
1          1                      Contractor         ABC Inc
2          1                      Area                 123.4567
3          1                      Stocked            Yes
4          2                      Contractor         XYZ Inc
5          2                      Area                 234.5678
6          2                      Stocked             No
7          3                      Material            Aluminum
7          4                      Manufacturer    TrashCanCo

When I call this function (or stored proc) with a value of ParentTypeID=1 I want to get back the following resultset:

ParentID           ParentName     Contractor        Area                 Stocked
1                      MyPond            ABC Inc            123.4567           Yes
2                      YourPond          XYZ Inc            234.5678           No

For a given value of ParentTypeID, the child table will always have the same number of rows with the same ChildNames but diifferent ChildValues.  I’d really like a table function instead of a stored procedure because my ultimate goal is to be able to be able to execute:

SELECT * FROM udfMyFunction(1)

and get the above resultset.

My previous question did not have the ParentTypeID attribute. The following elegant stored procedure solution was suggested, but I can't figure out how to extend it to replace the @ParentID argument with a @ParentTypeID argument

CREATE PROCEDURE GetParentData ( @ParentID INT)
AS
DECLARE @SQL VARCHAR(4000)

SET @SQL = 'SELECT ParentID, ParentName'
SELECT @SQL = @SQL + ', ''' + ChildValue + ''' AS [' + ChildName + ']'
FROM tblChild
WHERE ParentID = @ParentID

SET @SQL = @SQL + ' FROM tblParent WHERE ParentID=' + CAST(@ParentID AS VARCHAR(20))
EXEC (@SQL)
0
Question by:rmk

LVL 13

Expert Comment

1. Use cannot use a table function, functions cannot use dynamic SQL.
2. SQL server 2000 string processing is limited to 8000 ASCII characters. There exists techniques to overcome this limit, but the final code can complicated. You want SP, you will have it.
0

LVL 26

Accepted Solution

Here's my attempt

CREATE PROCEDURE GetParentData ( @ParentTypeID INT)
AS
DECLARE @SQL VARCHAR(4000)

SET @SQL = 'SELECT a.ParentID, a.ParentName'
SELECT @SQL = @SQL + ', MAX(case when ChildName = ''' + ChildName + ''' then b.ChildValue end) AS [' + ChildName + ']'
FROM tblChild
GROUP BY ChildName

SET @SQL = @SQL + ' FROM tblParent a INNER JOIN tblChild b ON a.ParentID = b.ParentID' +
' WHERE ParentTypeID=' + CAST(@ParentTypeID AS VARCHAR(20)) +
' GROUP BY a.ParentID, a.ParentName'
EXEC (@SQL)
0

Author Comment

I am totally in awe of SQL gurus like you. Thank you so very much !!!!!
0

Featured Post

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.