Best way to handle pivot data that gets updated..

[jtrembla]

Our company wants to view data from a normalized table in a pivoted grid. Not only that, they need to be able to update the values as well. Thus, I created a pivoted stored procedure which uses dynamic sql b/c the crap pivoted function doesn't read the column values.

Now updating the grid in the application requires looking up the field text value to get an id for that pivoted field.

Something like

DrugID DrugName 1/1/2007 2/1/2007 3/1/2007
=======================================================
1 Drug1 .58

the dates are normalized and have their ownid in the database, thus the lookup for the dateid if they want to update the values.

My question is this..Does anyone know of a better solution? Should I build my pivot all on the client side? If so, are there any tools that would make this easier. If you look at my pivot query it might be daunting to do on the client.

ALTER PROCEDURE [MarketForecaster].[ForecastAdjustmentPivot_SelectByVersionID] 

/**********************************************************************
 * SP Name:  ForecastAdjustmentPivot_SelectByVersionID
 * Author:   Patrick Ryan
 * Date:     Dec 17th 2008
 *
 * Purpose:
 * Returns Pivoted Forecast Adjustments along the entire ForecastTimeline
 * This is a dynamic query because the pivot function does not read column
 * values. 
 *
 * In Time: This pivot can be built in the application
 *
 * Date - Changed By
 * Changes
 **********************************************************************/
    @VersionID INT,
    @BrandGeneric VARCHAR(7),
    @ForecastAdjustmentTypeID TINYINT
AS 
    BEGIN

        SET NOCOUNT ON ;


        DECLARE @columns VARCHAR(2000),
            @query VARCHAR(4000)	
        SET @columns = ''

        SELECT  @columns = @columns + '['
                + convert(varchar(12), Cast(DatePart(mm, PeriodTimelineDate) as varchar)
                + '/' + Cast(DatePart(dd, PeriodTimelineDate) as varchar)
                + '/' + Cast(DatePart(yyyy, PeriodTimelineDate) as varchar))
                + '],'
        FROM    dbo.ForecastPeriodTimeline
                INNER JOIN dbo.ForecastPeriod ON dbo.ForecastPeriodTimeline.ForecastPeriodID = dbo.ForecastPeriod.ForecastPeriodID
        WHERE   dbo.ForecastPeriod.VersionID = @VersionID


        SET @columns = LEFT(@columns, LEN(@columns) - 1)   
				

        SET @query = '
SELECT  *
FROM    ( SELECT  a.PricingDrugGroupID,
        a.PricingDrugName,
        a.GeographyName,
        a.PricingDrugGroupTypeName,
        a.PeriodTimelineDate,
        a.SequenceNumber,
        a.PeriodTypeID,
        a.PricingDrugSequenceNumber,
        a.PricingDrugGroupTypeID,
        AdjustmentValue
FROM    ( SELECT    dbo.PricingDrugGroup.PricingDrugGroupID,
                    dbo.PricingDrug.PricingDrugName,
                    dbo.PricingDrug.SequenceNumber As PricingDrugSequenceNumber,
                    dbo.Geography.GeographyName,
                    dbo.Geography.SequenceNumber,
                    dbo.PricingDrugGroupType.PricingDrugGroupTypeID,
                    dbo.PricingDrugGroupType.PricingDrugGroupTypeName,
                    dbo.ForecastPeriodTimeline.PeriodTimelineDate,
                    dbo.ForecastPeriodTimeline.ForecastPeriodTimelineID,
                    dbo.ForecastPeriod.PeriodTypeID
          FROM      dbo.ForecastPeriod
                    INNER JOIN dbo.ForecastPeriodTimeline ON dbo.ForecastPeriod.ForecastPeriodID = dbo.ForecastPeriodTimeline.ForecastPeriodID,
                    dbo.PricingDrugGroup
                    INNER JOIN dbo.PricingDrug ON dbo.PricingDrugGroup.PricingDrugID = dbo.PricingDrug.PricingDrugID
                    INNER JOIN dbo.VersionGeography ON dbo.PricingDrugGroup.VersionGeographyID = dbo.VersionGeography.VersionGeographyID
                    INNER JOIN dbo.Geography ON dbo.VersionGeography.GeographyID = dbo.Geography.GeographyID
                    INNER JOIN dbo.PricingDrugGroupType ON dbo.PricingDrugGroup.PricingDrugGroupTypeID = dbo.PricingDrugGroupType.PricingDrugGroupTypeID
          WHERE     dbo.PricingDrug.VersionID ='
            + CONVERT(VARCHAR(10), @VersionID)
            + ' AND dbo.ForecastPeriod.VersionID ='
            + CONVERT(VARCHAR(10), @VersionID)
            + ' AND dbo.PricingDrugGroup.BrandGeneric = ''' + @brandgeneric
            + ''') AS A
        LEFT JOIN dbo.ForecastAdjustment ON a.PricingDrugGroupID = dbo.ForecastAdjustment.PricingDrugGroupID
                                            AND A.ForecastPeriodTimelineID = dbo.ForecastAdjustment.ForecastPeriodTimelineID 
                                             WHERE dbo.ForecastAdjustment.ForecastAdjustmentTypeID ='
            + CONVERT(VARCHAR(3), @ForecastAdjustmentTypeID)
            + ' OR dbo.ForecastAdjustment.ForecastAdjustmentTypeID IS NULL) AS T 
            
            PIVOT ( Min(AdjustmentValue) FOR PeriodTimelineDate IN ('
            + @columns + ') ) AS p
    ORDER BY p.PricingDrugSequenceNumber,
         p.SequenceNumber'

 
        EXECUTE ( @query
               )

  
        RETURN ;
  
    END

 

[Crowley16]

your front end application should do the updating by calling a stored procedure.

you can use the drugid in conjunction to the column name in your front end. You should be able to get the name of the columns in a dataset quite easily, in .net just iterate through the columns collection and use the .Name property...

--------------------
Procrastinate Now!