SQL Server Performance Forum – Threads Archive

advice/suggestions re: pivot usage

Hi all, 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. <pre id="code"> SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[DBO].[ProductPriceComparison]’) AND type in (N’U’)) DROP TABLE [DBO].[ProductPriceComparison] CREATE TABLE [DBO].[ProductPriceComparison]( [CatalogueItemID][nchar](<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> NOT NULL, [RetailerName][nvarchar](100) NOT NULL, [RetailerPrice][money] NOT NULL, [RetailerPriceCheckDate][datetime] NOT NULL, CONSTRAINT [PK_ProductPriceComparison] PRIMARY KEY CLUSTERED ([CatalogueItemID] ASC, [RetailerName] ASC, [RetailerPriceCheckDate] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] 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’,20.00,getdate() UNION ALL SELECT ‘100-0004′,’retailer4’,20.00,getdate() UNION ALL SELECT ‘100-0005′,’retailer1’,20.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() set nocount on – declare @SqlCmdvarchar(2000), @RetailerListvarchar(1000) set @SQLCMD = ” set @RetailerList = ” [email protected][email protected]+ ‘[‘ + rt.RetailerName + ‘]’ from(select distinct(RetailerName) as RetailerName from [DBO].ProductPriceComparison) Rt –select @RetailerList select @RetailerList=replace(@RetailerList,’][‘,’],[‘) –select @RetailerList Set @SQLCMD = ‘selectCatalogueItemId,’ + @RetailerList Set @SQLCMD = @SQLCMD + ‘ FROM (SELECT CatalogueItemId,RetailerName,RetailerPrice' Set @SQLCMD = @SQLCMD + ‘ FROM [DBO].ProductPriceComparison' –Set @SQLCMD = @SQLCMD + ‘ WHERE RetailerPriceCheckDate < = getdate() –add date limitation Set @SQLCMD = @SQLCMD + ‘) ppc PIVOT (max( RetailerPrice) FOR RetailerName in (‘ Set @SQLCMD = @SQLCMD + @RetailerList + ‘)) as Pvt ORDER BY CatalogueItemId' select @SQLCMD exec (@SQLCMD) </pre id="code"> –example results: <pre id="code"> CatalogueItemId retailer1 retailer2 retailer3 retailer4 ————— ——————— ——————— ——————— ——————— 100-0001 20.00 20.00 20.00 NULL 100-0002 20.00 NULL 20.00 NULL 100-0003 20.00 20.00 20.00 NULL 100-0004 NULL NULL NULL 20.00 100-0005 20.00 20.00 20.00 20.00 </pre id="code"> 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… Panic, Chaos, Disorder … my work here is done –unknown

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/

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

]]>