Pivot Report
-
SET NOCOUNT ON
DECLARE @Analysis TABLE
(
RowID INT IDENTITY,
CallCount INT,
OpenCalls INT,
CompletedCalls INT,
CancelledCalls INT,
DataDate DATETIME
)
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 0, 100, 25, '06/30/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 100, 0, '07/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (350, 100, 200, 50, '08/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (220, 100, 100, 20, '09/30/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (150, 50, 80, 20, '10/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (300, 25, 200, 75, '11/30/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (200, 40, 100, 60, '12/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (325, 25, 280, 20, '01/31/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (225, 125, 80, 20, '02/28/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (100, 25, 60, 15, '03/31/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (120, 25, 75, 20, '04/30/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (124, 24, 80, 20, '05/31/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 80, 20, '06/30/2005')
/* This table is updated once in a month only has 18 columns */
/* For the passed parameter month I have to make report like */
/*
If '06/30/2005' is passed results needed is for a report. I will do it in front end if it is ASP. For asp.net I just like to use datagrid.bind(). So is there an easy way to get this query done SQL.
Category
Month1
Month2
Month3
Month4
Month5
month6
CallCount
325
225
100
120
124
125
OpenCalls
25
125
25
25
25
25
CompletedCalls
280
80
60
75
80
80
CancelledCalls
20
20
15
20
20
20
Thanks for your help and or comments
*/
Regards,
gova -
SET NOCOUNT ON
DECLARE @Analysis TABLE
(
RowID INT IDENTITY,
CallCount INT,
OpenCalls INT,
CompletedCalls INT,
CancelledCalls INT,
DataDate DATETIME
)
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 0, 100, 25, '06/30/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 100, 0, '07/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (350, 100, 200, 50, '08/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (220, 100, 100, 20, '09/30/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (150, 50, 80, 20, '10/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (300, 25, 200, 75, '11/30/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (200, 40, 100, 60, '12/31/2004')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (325, 25, 280, 20, '01/31/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (225, 125, 80, 20, '02/28/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (100, 25, 60, 15, '03/31/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (120, 25, 75, 20, '04/30/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (124, 24, 80, 20, '05/31/2005')
INSERT INTO @Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 80, 20, '06/30/2005')
select Category
, sum(case when month(DataDate) = 1 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month1
, sum(case when month(DataDate) = 2 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month2
, sum(case when month(DataDate) = 3 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month3
, sum(case when month(DataDate) = 4 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month4
, sum(case when month(DataDate) = 5 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month5
, sum(case when month(DataDate) = 6 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month6
, sum(case when month(DataDate) = 7 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month7
, sum(case when month(DataDate) = 8 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month8
,sum(case when month(DataDate) = 9 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month9
, sum(case when month(DataDate) = 10 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month10
, sum(case when month(DataDate) = 11 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month11
, sum(case when month(DataDate) = 12 then
case when Category = 'CallCount' then CallCount
when Category = 'OpenCalls' then OpenCalls
when Category = 'CompletedCalls' then CompletedCalls
when Category = 'CancelledCalls' then CancelledCalls
else 0 end else 0 end ) Month12
from
@Analysis
cross join
(select 'CallCount' as Category
union all select 'OpenCalls'
union all select 'CompletedCalls'
union all select 'CancelledCalls' ) Cat
-- you may use a where clause here if you need to
-- where DateDate between @start and @end
group by Category
* Noel -
Cool. Thanks Noel.
With 18 columns (Report will have 18 rows) that will be a big query. But if I want to use SQL to compute I have to do it.
Added
I am using
SELECT TOP 100 PERCENT [name] FROM syscolumns
WHERE
id = object_id(N'[dbo].[myTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
ORDER BY colorder
for the subquery
Regards,
gova -
If you can pivot on the client this is less typing
select month(DataDate) as [Month]
,sum( case when category = 'CallCount' then CallCount else 0 end) as CallCount
,sum( case when category = 'OpenCalls' then OpenCalls else 0 end) as OpenCalls
,sum( case when category = 'CompletedCalls' then CompletedCalls else 0 end) as CompletedCalls
,sum( case when category = 'CancelledCalls' then CancelledCalls else 0 end) as CancelledCalls
from
@Analysis
cross join
(select 'CallCount' as Category
union all select 'OpenCalls'
union all select 'CompletedCalls'
union all select 'CancelledCalls' ) Cat
group by month(DataDate)
* Noel -
Are you trying to do this dynamically ?
* Noel -
I agree that pivoting on the client might be better. You might be able to do something elegant with XSLT.
But if you are determined to get the report you asked for in SQL, this will work.
declare @ReportDate datetime
set @ReportDate = '2005-06-30'
select Category,
sum(case when monthnbr=1 then
case Category when 'CallCount' then CallCount
when 'OpenCalls' then OpenCalls
when 'CompletedCalls' then CompletedCalls
when 'CancelledCalls' then CancelledCalls end else 0 end) as Month1,
sum(case when monthnbr=2 then
case Category when 'CallCount' then CallCount
when 'OpenCalls' then OpenCalls
when 'CompletedCalls' then CompletedCalls
when 'CancelledCalls' then CancelledCalls end else 0 end) as Month2,
sum(case when monthnbr=3 then
case Category when 'CallCount' then CallCount
when 'OpenCalls' then OpenCalls
when 'CompletedCalls' then CompletedCalls
when 'CancelledCalls' then CancelledCalls end else 0 end) as Month3,
sum(case when monthnbr=4 then
case Category when 'CallCount' then CallCount
when 'OpenCalls' then OpenCalls
when 'CompletedCalls' then CompletedCalls
when 'CancelledCalls' then CancelledCalls end else 0 end) as Month4,
sum(case when monthnbr=5 then
case Category when 'CallCount' then CallCount
when 'OpenCalls' then OpenCalls
when 'CompletedCalls' then CompletedCalls
when 'CancelledCalls' then CancelledCalls end else 0 end) as Month5,
sum(case when monthnbr=6 then
case Category when 'CallCount' then CallCount
when 'OpenCalls' then OpenCalls
when 'CompletedCalls' then CompletedCalls
when 'CancelledCalls' then CancelledCalls end else 0 end) as Month6
from (
select CallCount, OpenCalls, CompletedCalls, CancelledCalls, Datediff(m,@ReportDate,DataDate) + 6 as MonthNbr
from @Analysis
) x
cross join (
select 'CallCount' as Category
union all select 'OpenCalls'
union all select 'CompletedCalls'
union all select 'CancelledCalls'
) cat
where MonthNbr between 1 and 6
group by Category
-
No Noel. Instead of typing 18 columns I can get the columns for category like that. I would do it in client if it is simple asp report. This needs an Export to Excel and XML. Also with our ASP.NET user control, if I use grid.bind all these are available automatically.
Regards,
gova -
Thanks Scott. Noel gave the idea and I did something close to what you did to get my result set. Yes with xslt it would be elegant and easy. But I have to do this Export to Excel and XML thro our data grid. So I just went with SQL pivot.
Regards,
gova -
This is how I made it with syscolumns. I don't want to ype 18 columns (original table has 18 columns and 18 row report). Also it helps to sort the result set.
Thanks for your help.
SET NOCOUNT ON
CREATE TABLE Analysis
(
RowID INT IDENTITY,
CallCount INT,
OpenCalls INT,
CompletedCalls INT,
CancelledCalls INT,
DataDate DATETIME
)
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 0, 100, 25, '06/30/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 100, 0, '07/31/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (350, 100, 200, 50, '08/31/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (220, 100, 100, 20, '09/30/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (150, 50, 80, 20, '10/31/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (300, 25, 200, 75, '11/30/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (200, 40, 100, 60, '12/31/2004')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (325, 25, 280, 20, '01/31/2005')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (225, 125, 80, 20, '02/28/2005')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (100, 25, 60, 15, '03/31/2005')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (120, 25, 75, 20, '04/30/2005')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (124, 24, 80, 20, '05/31/2005')
INSERT INTO Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 80, 20, '06/30/2005')
DECLARE @pDataDate DATETIME SET @pDataDate = '06/30/2005'
SELECT * FROM Analysis
SELECT Category, colorder,
SUM(case WHEN DATEDIFF(MONTH, DataDate, @pDataDate) = 5 THEN
case WHEN Category = 'CallCount' THEN CallCount
WHEN Category = 'OpenCalls' THEN OpenCalls
WHEN Category = 'CompletedCalls' THEN CompletedCalls
WHEN Category = 'CancelledCalls' THEN CancelledCalls
ELSE 0 END ELSE 0 END) Month6,
SUM(case WHEN DATEDIFF(MONTH, DataDate, @pDataDate) = 4 THEN
case WHEN Category = 'CallCount' THEN CallCount
WHEN Category = 'OpenCalls' THEN OpenCalls
WHEN Category = 'CompletedCalls' THEN CompletedCalls
WHEN Category = 'CancelledCalls' THEN CancelledCalls
ELSE 0 END ELSE 0 END) Month5,
SUM(case WHEN DATEDIFF(MONTH, DataDate, @pDataDate) = 3 THEN
case WHEN Category = 'CallCount' THEN CallCount
WHEN Category = 'OpenCalls' THEN OpenCalls
WHEN Category = 'CompletedCalls' THEN CompletedCalls
WHEN Category = 'CancelledCalls' THEN CancelledCalls
ELSE 0 END ELSE 0 END) Month4,
SUM(case WHEN DATEDIFF(MONTH, DataDate, @pDataDate) = 2 THEN
case WHEN Category = 'CallCount' THEN CallCount
WHEN Category = 'OpenCalls' THEN OpenCalls
WHEN Category = 'CompletedCalls' THEN CompletedCalls
WHEN Category = 'CancelledCalls' THEN CancelledCalls
ELSE 0 END ELSE 0 END) Month3,
SUM(case WHEN DATEDIFF(MONTH, DataDate, @pDataDate) = 1 THEN
case WHEN Category = 'CallCount' THEN CallCount
WHEN Category = 'OpenCalls' THEN OpenCalls
WHEN Category = 'CompletedCalls' THEN CompletedCalls
WHEN Category = 'CancelledCalls' THEN CancelledCalls
ELSE 0 END ELSE 0 END) Month2,
SUM(case WHEN DATEDIFF(MONTH, DataDate, @pDataDate) = 0 THEN
case WHEN Category = 'CallCount' THEN CallCount
WHEN Category = 'OpenCalls' THEN OpenCalls
WHEN Category = 'CompletedCalls' THEN CompletedCalls
WHEN Category = 'CancelledCalls' THEN CancelledCalls
ELSE 0 END ELSE 0 END) Month1
FROM
Analysis
CROSS JOIN
(
SELECT [name] Category, colorder FROM syscolumns
WHERE
id = object_id(N'[dbo].[Analysis]') and OBJECTPROPERTY(id, N'IsUserTable') = 1
AND colorder NOT IN (1, 6)) CAT
GROUP BY Category, colorder
ORDER BY colorder
DROP TABLE Analysis
Regards,
gova -
The syscolumns Idea is nice! Just keep in mind that there is no documented way to control the colorder value and if someone modifies the table design through a drop/recreate (EM) you may have to come back to this query and fix it
* Noel -
I'm trying to do something similar, and I'm searching the forums for suggestions. I don't understand the purpose of the cross join in these posts. Could someone break it down for me? Couldn't you just SUM() up the columns and do an AS to name each?
-
Great code one and all. I've got a slightly different idea and it's pretty flexible for making other reports, as well. It also includes columnar and row totals as well as the grand total at the intersection of the two totals...
First, here's the data setup I used... I used a permanent table to hold the data... same stuff that everyone else used...
--drop table dbo.Analysis
SET NOCOUNT ON
CREATE TABLE dbo.Analysis
(
RowID INT IDENTITY,
CallCount INT,
OpenCalls INT,
CompletedCalls INT,
CancelledCalls INT,
DataDate DATETIME
)
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 0, 100, 25, '06/30/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 100, 0, '07/31/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (350, 100, 200, 50, '08/31/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (220, 100, 100, 20, '09/30/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (150, 50, 80, 20, '10/31/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (300, 25, 200, 75, '11/30/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (200, 40, 100, 60, '12/31/2004')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (325, 25, 280, 20, '01/31/2005')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (225, 125, 80, 20, '02/28/2005')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (100, 25, 60, 15, '03/31/2005')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (120, 25, 75, 20, '04/30/2005')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (124, 24, 80, 20, '05/31/2005')
INSERT INTO dbo.Analysis (CallCount, OpenCalls, CompletedCalls, CancelledCalls, DataDate)
VALUES (125, 25, 80, 20, '06/30/2005')
... and here's my "flexible" solution... change the content of a couple of variables and, bingo, new report... might even be able to turn it into a stored procedure with a bit of SQL prestidigitation...
--===== Create a table variable to hold desired column names to report on
DECLARE @Columns TABLE (ColumnName VARCHAR(50) PRIMARY KEY NONCLUSTERED)
--===== Populate the table variable with the desired column names to report on
INSERT INTO @Columns (ColumnName)
SELECT 'CallCount' UNION ALL
SELECT 'CancelledCalls' UNION ALL
SELECT 'CompletedCalls' UNION ALL
SELECT 'OpenCalls'
--===== Declare and set a variable for the first month to report on
DECLARE @StartMonth VARCHAR(10)
SET @StartMonth = '06/01/2005' --Can be any day within a given month but I recommend the first
--===== Declare and set a variable to hold the table name to exploit
DECLARE @TableName VARCHAR(50)
SET @TableName = 'dbo.Analysis'
--===== Declare and set a variable with the name of the "date break" column
DECLARE @BreakCol VARCHAR(50)
SET @BreakCol = 'DataDate'
--===== Declare variables to hold the dynamic SQL constructs
DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)
--===== This variable holds the "crosstab" construct
SELECT @SQL1 = '
SELECT CASE WHEN GROUPING(ColumnName) = 1 THEN ''** Total **'' ELSE ColumnName END AS ColumnName,
STR(SUM(CASE WHEN MonthX = 1 THEN Value ELSE 0 END),6) AS Month1,
STR(SUM(CASE WHEN MonthX = 2 THEN Value ELSE 0 END),6) AS Month2,
STR(SUM(CASE WHEN MonthX = 3 THEN Value ELSE 0 END),6) AS Month3,
STR(SUM(CASE WHEN MonthX = 4 THEN Value ELSE 0 END),6) AS Month4,
STR(SUM(CASE WHEN MonthX = 5 THEN Value ELSE 0 END),6) AS Month5,
STR(SUM(CASE WHEN MonthX = 6 THEN Value ELSE 0 END),6) AS Month6,
STR(SUM(Value),11) AS [** Total **]
FROM (--==== Derived table "d" creates list by column name
'
--===== This variable holds the "columnizer" derived table constuct
SELECT @SQL2 = ISNULL(@SQL2+'UNION ALL','')+
'
SELECT '''+c.ColumnName+''' AS ColumnName,
DATEDIFF(mm,'''+@StartMonth+''','+@BreakCol+')+6 AS MonthX,
SUM('+c.ColumnName+') AS Value
FROM '+@TableName+'
GROUP BY DATEDIFF(mm,'''+@StartMonth+''','+@BreakCol+')+6
HAVING DATEDIFF(mm,'''+@StartMonth+''','+@BreakCol+')+6 BETWEEN 1 AND 6
'
FROM @Columns c
--===== This variable holds the "Group By" construct for the crosstab
SELECT @SQL3 = '
) d
GROUP BY ColumnName
WITH ROLLUP
'
--===== Create the report using the dynamic SQL
EXEC (@SQL1+@SQL2+@SQL3)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Oh yeah... almost forgot... if you want to see the code the script above generates, just change the "EXEC" to a "PRINT".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply