Cross Tab/Pivot table/UDF?

I have the following tables:

tbTemplateShapeProperties
fkTemplate | fkProperty | PropertyValue
---------------
1 | 1 | 192
1 | 2 | 36
1 | 3 | 4
1 | 4 | 5
1 | 5 | 2

tbShapeProperties
Property | PropertyName | fkShape
--------------
1 | Width | 1
2 | Height | 1
3 | Flange | 1
4 | Avg. Leg Width | 5
5 | Leg Count | 2

From the above I wanted to create a pivot table, from there I want to pass the column values through to a UDF

XSection (Width, Height, Flange, Leg, LegCount)

I tried the following to get a pivot table but it does not give a single row but 5.

SELECT CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ELSE 0 END AS Width,
CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ELSE 0 END AS Height,
CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ELSE 0 END AS Flange,
CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ELSE 0 END AS Leg,
CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ELSE 0 END AS LegCount
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = 1

The following results are returned:

Width | Height | Flange | Leg | LegCount
--------------
192 | 0 | 0 | 0 | 0
0 | 36 | 0 | 0 | 0
0 | 0 | 6 | 0 | 0
0 | 0 | 0 | 5 | 0
0 | 0 | 0 | 0 | 2

The desired result as you could guess is:

Width | Height | Flange | Leg | LegCount
--------------
192 | 36 | 6 | 5 | 2

So, this leaves me with one question, even if I was to get this to work, is is possible to then extract the values and pass them through to the UDF within the same stored proc?

Any hints?

Mike BYeah, I did it!!!

Here is the code:

CREATE PROCEDURE usp_GetDoubleTCrossSection

@.iTemplate int,
@.fXSection float OUTPUT

AS

declare @.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float

set @.iTemplate = 1
SELECT @.fWidth = SUM(CASE sp.PropertyName WHEN 'Width' THEN tsp.PropertyValue ElSE 0 END),
@.fHeight = SUM(CASE sp.PropertyName WHEN 'Height' THEN tsp.PropertyValue ElSE 0 END),
@.fFlange = SUM(CASE sp.PropertyName WHEN 'Flange' THEN tsp.PropertyValue ElSE 0 END),
@.fLegWidth = SUM(CASE sp.PropertyName WHEN 'Avg. Leg Width' THEN tsp.PropertyValue ElSE 0 END),
@.fLegs = SUM(CASE sp.PropertyName WHEN 'Leg Count' THEN tsp.PropertyValue ElSE 0 END)
FROM tbTemplateShapeProperties AS tsp INNER JOIN tbShapeProperties AS sp
ON tsp.fkProperty = sp.Property
WHERE tsp.fkTemplate = @.iTemplate

SELECT @.fXSection = [dbo].[TEE_XSECTION](@.fWidth, @.fHeight, @.fFlange, @.fLegWidth, @.fLegs)
GO

//UDF
CREATE FUNCTION TEE_XSECTION
(@.fWidth float, @.fHeight float, @.fFlange float, @.fLegWidth float, @.fLegs float)

RETURNS float

AS
BEGIN
RETURN (@.fWidth * @.fFlange + (((@.fHeight - @.fFlange)*@.fLegWidth)*@.fLegs))
END

No cursor!!! Whew!

Mike B