• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Dynamic Table Function - Part 2

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
rmk
Asked:
rmk
1 Solution
 
ispalenyCommented:
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
 
HilaireCommented:
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
 
rmkAuthor Commented:
I am totally in awe of SQL gurus like you. Thank you so very much !!!!!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now