Dynamic Table Function - Part 2

Posted on 2005-05-09
Last Modified: 2012-05-05
Here’s a simplified version of the problem:

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

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
ParentID           ParentName   ParentTypeID
1                     MyPond          1
2                     YourPond       1
3                     MyTrashCan   2

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


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))
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.
    LVL 26

    Accepted Solution

    Here's my attempt

    CREATE PROCEDURE GetParentData ( @ParentTypeID INT)

    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)

    Author Comment

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Free Trending Threat Insights Every Day

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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 ( 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.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now