November 27, 2012 at 11:59 am
I've been banging my head on this for a couple of days now, and I feel the answer is just out of my grasp. I need to replace this query:
Declare @ChainCode int
Set @ChainCode = 970
Select EffectiveDate, CanDrinks, Bottle20CSD, Snacks, Coffee, Food,
Juice, Gatorade, IceCream, Sundry
From CommRates
Where ChainCode = @ChainCode
Which produces this result set,
EffectiveDate CanDrinks Bottle20CSD SnacksCoffeeFood Juice GatoradeIceCreamSundry
2006-12-18 0.19000.19000.11000.10000.10000.10000.1000NULL NULL
2008-04-01 0.17000.17000.11000.15000.15000.15000.17000.1500NULL
with a query that will produce this...
Category 2006-12-18 2008-04-01
CanDrinks 0.1900 0.1700
BottleCSD 0.1900 0.1700
Snacks 0.1100 0.1100
Coffee 0.1000 0.1500
Food 0.1000 0.1500
Juice 0.1000 0.1500
Gatorade 0.1000 0.1700
IceCream Null 0.1500
Sundry Null Null
Can anyone point me in the right direction? Please note that the number of columns in the transposed table will be dynamic based on how many records are found that match the where clause in the first query. The name of the first column in my desired result could be anything. I inserted the word "category" here for illustration. Also, I do not want to perform any aggregation on any of the data. I have to write a number of reports that need this same type of logic where column names are arranged as a single column in the first column of a new table, and the number of columns in the final table being dynamic based on dates.
November 27, 2012 at 12:01 pm
I would recommend reading Jeff Moden's Cross tab and Pivot articles. Number 1 is http://www.sqlservercentral.com/articles/T-SQL/63681/
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 27, 2012 at 12:14 pm
I read the article completely, but I can't see how it applies to the specifics of my problem. I need a dynamic number of columns, with the first columns rows being made up of the column names of the original table.
November 27, 2012 at 12:22 pm
part two of the group of articles that cpn hector mentioned is doing it dynamically, when the # of values is unknown.
the link to part two is here:
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
Lowell
November 27, 2012 at 12:25 pm
Lowell, that link just points back to this forum post.
November 27, 2012 at 12:53 pm
lynn.huff (11/27/2012)
Lowell, that link just points back to this forum post.
whoops! link fixed, i built the tag wrong, sorry.
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]
Lowell
November 27, 2012 at 1:18 pm
That all applies to aggregating, which doesn't apply to my problem.
Here's some code that ALMOST does what I want. It will supply the dynamic columns, BUT only give me the values for ONE of my rows. But, it is missing the first column I need in the result set, which should contain the column names from the original table.
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(EffectiveDate as varchar) + ']',
'[' + cast(EffectiveDate as varchar)+ ']'
)
FROM TCAP.dbo.CommRates
Where ChainCode = 970
Print @PivotColumnHeaders
DECLARE @PivotTableSQL NVARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
SELECT
P.EffectiveDate,
P.CanDrinks
FROM CommRates P
Where ChainCode = 970
) AS PivotData
PIVOT (
SUM(CanDrinks)
FOR EffectiveDate IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable
'
EXECUTE(@PivotTableSQL)
Can anyone suggest how to finish this to get my desired result? Thanks!
November 27, 2012 at 2:51 pm
I will try to work something up but with out sample data it will take a little longer as i will need to generate some. As far as aggregating you can always aggregate a set of 1 (MAX(1) is 1)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 27, 2012 at 3:52 pm
Looking at the original query it is very de-normalized and almost looks like a pivot table in its own right so the first thing i did to accomplish the task was to UNPIVOT the data (i used a cross apply) First i generated some sample data
CREATE TABLE SampleData (
EffectiveDate DATE,
CanDrinks INT, --ints are easy to generate
Bottle20CSD INT,
Snacks INT,
Coffee INT,
Food INT
--..... this gives enough columns to get the point accross
)
INSERT INTO SampleData
SELECT DATEADD(DD,(ABS(CHECKSUM(NEWID())) % 25) * -1,GETDATE()),
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000,
ABS(CHECKSUM(NEWID())) % 1000
FROM Tally
WHERE N < 20
;WITH Dupes AS (SELECT ROW_NUMBER() OVER (PARTITION BY EffectiveDate ORDER BY CanDrinks) AS RN, *
FROM SampleData)
DELETE FROM Dupes WHERE RN > 1
SELECT * FROM SampleData
Then with data that is close to what i think yours is i normalized the data on the fly with
SELECT EffectiveDate, ItemName, Ammount
FROM SampleData
CROSS APPLY (VALUES ('CanDrinks',CanDrinks),('Bottle20CSD',Bottle20CSD),('Snacks',Snacks),('Coffee',Coffee),('Food',Food))x(ItemName,Ammount)
This will allow us to make a nice pivot table in the output you want. With the unpivot you can then write your dynamic pivot table (or cross tab) in the same manor you were attacking the problem before. if you have any other questions let me know.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 28, 2012 at 8:08 am
Outstanding! :-D:-D
I had not used Cross Apply before. Your idea of using Cross Apply, along with dynamically creating the column list for the pivot table solved my problem. 🙂
Thank you very much!
November 28, 2012 at 9:00 am
so i came up with the following which is completly dynamic.
DECLARE @SQL NVARCHAR(MAX) = ''
DECLARE @PivotColumn NVARCHAR(MAX) = ''
DECLARE @Start DATE = '2012-11-12'
DECLARE @End DATE = '2012-11-27'
DECLARE @SampleDataColumns NVARCHAR(MAX)
SET @SampleDataColumns = (
SELECT STUFF((
SELECT ',(''' + name + ''', ' + name + ')'
from sys.columns
WHERE object_id = object_id('dbo.SampleData')
AND column_id > 1
FOR XML PATH('')),1,1,'')
)
SET @PivotColumn = (
SELECT STUFF((
SELECT ', MAX(CASE WHEN EffectiveDate = ''' + CAST(EffectiveDate AS VARCHAR) + ''' THEN Ammount ELSE 0 END) AS ''' + CAST(EffectiveDate AS VARCHAR) + ''''
FROM SampleData
WHERE EffectiveDate BETWEEN @Start AND @End
ORDER BY EffectiveDate
FOR XML PATH('')),1,2,''))
SELECT @PivotColumn
SET @SQL = '
;WITH normalizeData AS (
SELECT EffectiveDate, ItemName, Ammount
FROM SampleData
CROSS APPLY (VALUES ' + @SampleDataColumns + ')x(ItemName,Ammount)
WHERE EffectiveDate BETWEEN @Start AND @End)
SELECT ItemName, ' + @PivotColumn + '
FROM normalizeData
GROUP BY ItemName'
exec sp_executesql @SQL, N'@Start DATE, @End DATE', @Start, @End
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
November 28, 2012 at 2:51 pm
lynn.huff (11/27/2012)
That all applies to aggregating, which doesn't apply to my problem.
Actually, it does. Use MAX instead of SUM to transpose character based items.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply