Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Cross Tab/Pivot table/UDF?

    I have the following tables:

    Code:
    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.

    Code:
    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:
    Code:
    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:
    Code:
    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 B

  2. #2
    Join Date
    Feb 2004
    Posts
    134

    Thumbs up

    Yeah, I did it!!!

    Here is the code:
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •