SQL Server Performance

advice/suggestions re: pivot usage

Discussion in 'SQL Server 2005 General Developer Questions' started by SQL_Guess, Jul 6, 2006.

  1. SQL_Guess New Member

    Hi all,<br /><br />Please look at the attached - I think it works, but I'm looking for some comments/advice. Included is table creation, samepl data and the t-sql statement.<br /><br /><pre id="code"><font face="courier" size="2" id="code"><br />SET ANSI_NULLS ON<br />SET QUOTED_IDENTIFIER ON<br />IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[ProductPriceComparison]') AND type in (N'U'))<br />DROP TABLE [DBO].[ProductPriceComparison]<br />CREATE TABLE [DBO].[ProductPriceComparison](<br />[CatalogueItemID][nchar](<img src='/community/emoticons/emotion-11.gif' alt='8)' /> NOT NULL,<br />[RetailerName][nvarchar](100) NOT NULL,<br />[RetailerPrice][money] NOT NULL,<br />[RetailerPriceCheckDate][datetime] NOT NULL,<br /> CONSTRAINT [PK_ProductPriceComparison] PRIMARY KEY CLUSTERED <br />([CatalogueItemID] ASC,<br />[RetailerName] ASC,<br />[RetailerPriceCheckDate] ASC<br />)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]<br />) ON [PRIMARY]<br /><br />INSERT INTO [DBO].[ProductPriceComparison] ([CatalogueItemID],[RetailerName],[RetailerPrice],[RetailerPriceCheckDate])<br />SELECT '100-0001','retailer1',20.00,getdate() UNION ALL<br />SELECT '100-0001','retailer2',20.00,getdate() UNION ALL<br />SELECT '100-0001','retailer3',20.00,getdate() UNION ALL<br />SELECT '100-0002','retailer1',20.00,getdate() UNION ALL<br />SELECT '100-0002','retailer3',20.00,getdate() UNION ALL<br />SELECT '100-0003','retailer1',20.00,getdate() UNION ALL<br />SELECT '100-0003','retailer2',20.00,getdate() UNION ALL<br />SELECT '100-0003','retailer3',20.00,getdate() UNION ALL<br />SELECT '100-0004','retailer4',20.00,getdate() UNION ALL<br />SELECT '100-0005','retailer1',20.00,getdate() UNION ALL<br />SELECT '100-0005','retailer2',20.00,getdate() UNION ALL<br />SELECT '100-0005','retailer3',20.00,getdate() UNION ALL<br />SELECT '100-0005','retailer4',20.00,getdate()<br /><br />set nocount on<br />--<br />declare<br />@SqlCmdvarchar(2000),<br />@RetailerListvarchar(1000)<br /><br />set @SQLCMD = ''<br />set @RetailerList = ''<br /><br />SELECT@RetailerList=@RetailerList+ '[' + rt.RetailerName + ']' <br />from(select distinct(RetailerName) as RetailerName from [DBO].ProductPriceComparison) Rt<br />--select @RetailerList<br />select @RetailerList=replace(@RetailerList,'][','],[')<br />--select @RetailerList<br /><br /><br />Set @SQLCMD = 'selectCatalogueItemId,' + @RetailerList<br />Set @SQLCMD = @SQLCMD + ' FROM (SELECT CatalogueItemId,RetailerName,RetailerPrice'<br />Set @SQLCMD = @SQLCMD + ' FROM [DBO].ProductPriceComparison'<br /> --Set @SQLCMD = @SQLCMD + ' WHERE RetailerPriceCheckDate &lt; = getdate() --add date limitation<br />Set @SQLCMD = @SQLCMD + ') ppc PIVOT (max( RetailerPrice) FOR RetailerName in ('<br />Set @SQLCMD = @SQLCMD + @RetailerList + ')) as Pvt ORDER BY CatalogueItemId'<br /><br />select @SQLCMD<br />exec (@SQLCMD)<br /></font id="code"></pre id="code"><br /><br />--example results:<br /><pre id="code"><font face="courier" size="2" id="code"><br />CatalogueItemId retailer1 retailer2 retailer3 retailer4<br />--------------- --------------------- --------------------- --------------------- ---------------------<br />100-0001 20.00 20.00 20.00 NULL<br />100-0002 20.00 NULL 20.00 NULL<br />100-0003 20.00 20.00 20.00 NULL<br />100-0004 NULL NULL NULL 20.00<br />100-0005 20.00 20.00 20.00 20.00<br /></font id="code"></pre id="code"><br /><br />That all looks good (imho). I fear I may have some issues trying to modify this when/if I need to cater for mutliple rows for the same product, same retailer and different date...<br /><br />Panic, Chaos, Disorder ... my work here is done --unknown
  2. dineshasanka Moderator

    Why do you need nvarchar and nchar. instead you can use varchar and char.
    your primary key has composite key. it will be better if you can introduce some other number instead this


    ----------------------------------------
    http://dineshasanka.blogspot.com/
  3. SQL_Guess New Member

    the N datatype usage is deliberate, since the organization requires the ability to use this in many different locations worldwide, and some of those will have different collations that require N datatypes.

    the table represents a table imported from an excel source, hence I have stucture a listed, mapping exactly to that structure. This structure is a temporary/staging structure - I created the primary to enforce the business rule.

    If you were dealing with the above, and expanded the sample data to:


    --yesterday
    INSERT INTO [DBO].[ProductPriceComparison] ([CatalogueItemID],[RetailerName],[RetailerPrice],[RetailerPriceCheckDate])
    SELECT '100-0001','retailer1',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0001','retailer2',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0001','retailer3',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0002','retailer1',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0002','retailer3',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0003','retailer1',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0003','retailer2',19.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0003','retailer3',21.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0004','retailer4',21.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0005','retailer1',21.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0005','retailer2',21.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0005','retailer3',21.00,dateadd(day,-1,getdate()) UNION ALL
    SELECT '100-0005','retailer4',21.00,dateadd(day,-1,getdate())
    --today
    INSERT INTO [DBO].[ProductPriceComparison] ([CatalogueItemID],[RetailerName],[RetailerPrice],[RetailerPriceCheckDate])
    SELECT '100-0001','retailer1',20.00,getdate() UNION ALL
    SELECT '100-0001','retailer2',20.00,getdate() UNION ALL
    SELECT '100-0001','retailer3',20.00,getdate() UNION ALL
    SELECT '100-0002','retailer1',20.00,getdate() UNION ALL
    SELECT '100-0002','retailer3',20.00,getdate() UNION ALL
    SELECT '100-0003','retailer1',20.00,getdate() UNION ALL
    SELECT '100-0003','retailer2',20.00,getdate() UNION ALL
    SELECT '100-0003','retailer3',18.00,getdate() UNION ALL
    SELECT '100-0004','retailer4',20.00,getdate() UNION ALL
    SELECT '100-0005','retailer1',18.00,getdate() UNION ALL
    SELECT '100-0005','retailer2',20.00,getdate() UNION ALL
    SELECT '100-0005','retailer3',20.00,getdate() UNION ALL
    SELECT '100-0005','retailer4',20.00,getdate()
    --tomorrow
    INSERT INTO [DBO].[ProductPriceComparison] ([CatalogueItemID],[RetailerName],[RetailerPrice],[RetailerPriceCheckDate])
    SELECT '100-0001','retailer1',20.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0001','retailer2',21.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0001','retailer3',21.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0002','retailer1',21.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0002','retailer3',21.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0003','retailer1',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0003','retailer2',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0003','retailer3',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0004','retailer4',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0005','retailer1',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0005','retailer2',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0005','retailer3',19.00,dateadd(day,1,getdate()) UNION ALL
    SELECT '100-0005','retailer4',19.00,dateadd(day,1,getdate())


    I'm struggling to see how I can adapt the SQL to only apply the pivot to valid data -for example:
    where CONVERT(char(10), RetailerPriceCheckDate, 111) = CONVERT(char(10), getdate(), 111))

    I'm not certain yet whether the spreadsheet sources will have data across different dates - I'm trying to be ready to tackle the challenge if it comes about...

    Panic, Chaos, Disorder ... my work here is done --unknown

Share This Page