March 29, 2012 at 12:35 pm
I have the following query
SELECT * FROM (
SELECT Product
,[201101],[201102],[201103],[201104],[201105],[201106],[201107],[201108],[201109],[201110],[201111],[201112]
FROM
(SELECT Product, [TransactionYYYYMM1]
FROM ##tmpTable T1 LEFT JOIN Products T2 ON T1.ID = T2.ID WHERE Sold = 1
) AS source
PIVOT (
COUNT(TransactionYYYYMM1) FOR [TransactionYYYYMM1] IN ([201101],[201102],[201103],[201104],[201105],[201106],[201107],[201108],[201109],[201110],[201111],[201112])) AS pvt
) T1
ORDER BY 1
which produces the data in the "current" section of the attached speadsheet. What I want is to do totalling within my query (see "desired" section). Any ideas? I have been messing with it for a few days and no luck.
Thanks,
Michael
March 29, 2012 at 12:47 pm
GROUP BY with ROLLUPs may be?
March 29, 2012 at 12:52 pm
I have tried that, I didn't have a whole lot of success...but odds are, I am doing it incorrectly.
March 29, 2012 at 12:55 pm
Can you post the query you tried ?
And also, can you post the data in readily-consumable format? I cannot download content at my office.
Please read here on how ro format data to be readily consumable - http://qa.sqlservercentral.com/articles/Best+Practices/61537/
March 30, 2012 at 6:34 am
Thanks for the information....lets see if this helps
IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable
CREATE TABLE #mytable(
[Product] [varchar](1) NULL,
[TransactionYYYYMM1] [varchar](6) NULL
) ON [PRIMARY]
GO
INSERT INTO #mytable (TransactionYYYYMM1, Product)
SELECT '201108', 'B' UNION ALL
SELECT '201108', 'C' UNION ALL
SELECT '201106', 'A' UNION ALL
SELECT '201101', 'C' UNION ALL
SELECT '201103', 'A' UNION ALL
SELECT '201101', 'A' UNION ALL
SELECT '201101', 'A' UNION ALL
SELECT '201104', 'B' UNION ALL
SELECT '201107', 'A' UNION ALL
SELECT '201107', 'B' UNION ALL
SELECT '201103', 'C' UNION ALL
SELECT '201112', 'C' UNION ALL
SELECT '201112', 'A' UNION ALL
SELECT '201111', 'A' UNION ALL
SELECT '201103', 'A' UNION ALL
SELECT '201110', 'A' UNION ALL
SELECT '201103', 'A' UNION ALL
SELECT '201106', 'A' UNION ALL
SELECT '201108', 'A' UNION ALL
SELECT '201110', 'A'
SELECT *
FROM ( SELECT Product ,
[201101] ,
[201102] ,
[201103] ,
[201104] ,
[201105] ,
[201106] ,
[201107] ,
[201108] ,
[201109] ,
[201110] ,
[201111] ,
[201112]
FROM ( SELECT Product,
[TransactionYYYYMM1]
FROM #mytable
GROUP BY Product, TransactionYYYYMM1 WITH ROLLUP
) AS source PIVOT ( COUNT(TransactionYYYYMM1) FOR [TransactionYYYYMM1] IN ( [201101],
[201102],
[201103],
[201104],
[201105],
[201106],
[201107],
[201108],
[201109],
[201110],
[201111],
[201112] ) ) AS pvt
) T1
ORDER BY 1
March 30, 2012 at 2:52 pm
Will this do the cut?
; WITH CTE AS
(
SELECT Product
,[TransactionYYYYMM1]
,Ct = COUNT(*)
FROM #mytable
GROUP BY Product, TransactionYYYYMM1
UNION ALL
SELECT 'TOTAL' Product
, [TransactionYYYYMM1]
, Ct = COUNT(*)
FROM #mytable
GROUP BY TransactionYYYYMM1
--ORDER BY Product , [TransactionYYYYMM1]
)
SELECT C.Product
,[201101] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201101' THEN C.Ct ELSE 0 END)
,[201102] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201102' THEN C.Ct ELSE 0 END)
,[201103] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201103' THEN C.Ct ELSE 0 END)
,[201104] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201104' THEN C.Ct ELSE 0 END)
,[201105] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201105' THEN C.Ct ELSE 0 END)
,[201106] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201106' THEN C.Ct ELSE 0 END)
,[201107] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201107' THEN C.Ct ELSE 0 END)
,[201108] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201108' THEN C.Ct ELSE 0 END)
,[201109] = MAX( CASE WHEN C.TransactionYYYYMM1 = '201109' THEN C.Ct ELSE 0 END)
,[2011010] = MAX( CASE WHEN C.TransactionYYYYMM1 = '2011010' THEN C.Ct ELSE 0 END)
,[2011011] = MAX( CASE WHEN C.TransactionYYYYMM1 = '2011011' THEN C.Ct ELSE 0 END)
,[2011012] = MAX( CASE WHEN C.TransactionYYYYMM1 = '2011012' THEN C.Ct ELSE 0 END)
FROM CTE C
GROUP BY C.Product
ORDER BY C.Product
March 30, 2012 at 11:19 pm
Just to make it a bit more flexible so that you don't have to change the code every year, try the following against the test data you provided. I also added in a "Total" column and that automatically produces a "Grand Total", as well (heh... you just know they're going to ask for such things).
--===== Declare some obviously named variables
DECLARE @Year DATETIME, --@Year could be a parameter for a stored procedure
@SQL VARCHAR(MAX);
--===== Preset the variables
SELECT @Year = '2011';
SELECT @SQL = ' SELECT Product = CASE WHEN GROUPING(Product) = 0 THEN Product ELSE ''Total'' END,'
+ CHAR(10);
--===== Build the dynamic SELECT list
WITH
cte12(N) AS
(
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL
SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL
SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11
),
cteDateParts AS
(
SELECT N,
ColName = QUOTENAME(CONVERT(CHAR(6),DATEADD(mm,N,@Year),112)),
TDate = QUOTENAME(CONVERT(CHAR(6),DATEADD(mm,N,@Year),112),'''')
FROM cte12
)
SELECT @SQL = @SQL + SPACE(8) + ColName
+ '=SUM(CASE WHEN TransactionYYYYMM1=' + TDate + ' THEN 1 ELSE 0 END),'
+ CHAR(10)
FROM CteDateParts
ORDER BY N
;
--===== Build the rest of the dynamic SQL
SELECT @SQL = @SQL + SPACE(8) + 'Total = COUNT(*)
FROM #MyTable
GROUP BY Product WITH ROLLUP;'
;
--===== Print the Dynamic SQL so we can see what it looks like.
-- This section may be commented out for production.
-- PRINT @SQL
;
--===== Produce the report
EXEC (@SQL)
;
--Jeff Moden
April 2, 2012 at 7:20 am
Thank you both....they work beautifully!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply