Dynamic query Pivot values change

Question

Dynamic query Pivot values change

ramrajan

SSCommitted

Points: 1737
More actions
August 6, 2015 at 1:05 am

#301151

Hello I have below script
CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20
UNION
SELECT '4/2/2015','Benz' , -10
UNION
SELECT '4/3/2015','Toyota' , 10
UNION
SELECT '4/1/2015','Toyota' , -5
UNION
SELECT '5/1/2015','Benz' , 200
UNION
SELECT '5/6/2015','Benz' , -1000
UNION
SELECT '6/1/2015','Toyota' , -200
UNION
SELECT '6/3/2015','Toyota' , 20
DECLARE
@MonthCount TINYINT,
@Counter TINYINT,
@PivotColumns VARCHAR(512),
@DynamicSql NVARCHAR(MAX),
@FromDate DATE = '4/1/2015',
@ToDate DATE = '6/1/2015';
SELECT @ToDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@ToDate)+1,0)) -- End Day of the month
SELECT
@MonthCount = DATEDIFF(MM,@FromDate,@ToDate) + 1,
@Counter = 0,
@PivotColumns = ''
WHILE @Counter < @MonthCount
BEGIN
SELECT
@PivotColumns = @PivotColumns
+ QUOTENAME(LEFT(DATENAME(MONTH, DATEADD(MM,@Counter,@FromDate)),3) + ' ' + DATENAME(YEAR, DATEADD(MM,@Counter,@FromDate))) + ',',
@Counter = @Counter + 1
END
SELECT @PivotColumns = SUBSTRING(@PivotColumns,0,LEN(@PivotColumns))
SELECT @PivotColumns
SELECT @FromDate , @ToDate
SET @DynamicSql = 'SELECT * FROM
(SELECT
Products,
CONVERT(CHAR(4), CollectionDate, 100) + CONVERT(CHAR(4), CollectionDate, 120) AS Month,
Samples = SUM(distinct ItemCount)
FROM dbo.TestPivot
WHERE (CollectionDate BETWEEN @startDate AND @endDate)
GROUP BY Products,
CONVERT(CHAR(4), CollectionDate, 100) + CONVERT(CHAR(4), CollectionDate, 120)) MDEOutput
PIVOT
(AVG(Samples) FOR MONTH IN (' + @PivotColumns + ')
) AS OUTPUT
ORDER BY OUTPUT.Products'
DECLARE @ParameterList NVARCHAR(200) = N'@startDate DATETIME,
@endDate DATETIME'
EXEC sp_executesql @DynamicSql, @ParameterList, @FromDate, @ToDate
/*
-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180
****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows
ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid
*/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

sgmunson SSC Guru Points: 110640 More actions · Answer 1

Okay, two things:

1.) By just throwing out "Invalid" instead of a negative result, you end up hiding the true cause of the problem, AND you might well entirely miss any sufficiently small negative value that doesn't reduce the overall SUM by Product to less than zero. However, as you haven't detailed exactly WHY the "Invalid" string must appear instead of a number, it may be that only a negative SUM is invalid, and not necessarily any one individual negative value in the source data.

2.) How do you plan to handle differing data types in the same column?

If you can accept that all of your numeric values are going to get CAST to varchar strings, and the potential consequences of item 1 above, then the following should give you what you seek:

CREATE TABLE dbo.TestPivot(

CollectionDate DATETIME,

Products VARCHAR(40),

ItemCount INT

);

INSERT INTO dbo.TestPivot

SELECT '4/1/2015','Benz' , 20

UNION

SELECT '4/2/2015','Benz' , -10

UNION

SELECT '4/3/2015','Toyota' , 10

UNION

SELECT '4/1/2015','Toyota' , -5

UNION

SELECT '5/1/2015','Benz' , 200

UNION

SELECT '5/6/2015','Benz' , -1000

UNION

SELECT '6/1/2015','Toyota' , -200

UNION

SELECT '6/3/2015','Toyota' , 20;

DECLARE

@MonthCount TINYINT,

@Counter TINYINT,

@PivotColumns VARCHAR(512),

@CastPivotCols VARCHAR(1024),

@DynamicSql NVARCHAR(MAX),

@FromDate DATE = '4/1/2015',

@ToDate DATE = '6/1/2015';

SELECT @ToDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@ToDate)+1,0)); -- End Day of the month

SELECT

@MonthCount = DATEDIFF(MM,@FromDate,@ToDate) + 1,

@Counter = 0,

@PivotColumns = '';

WHILE @Counter < @MonthCount

BEGIN

SELECT

@PivotColumns = @PivotColumns

+ QUOTENAME(LEFT(DATENAME(MONTH, DATEADD(MM,@Counter,@FromDate)),3) + ' ' + DATENAME(YEAR, DATEADD(MM,@Counter,@FromDate))) + ',',

@Counter = @Counter + 1;

END

SELECT @PivotColumns = SUBSTRING(@PivotColumns,0,LEN(@PivotColumns));

SELECT

@Counter = 0,

@CastPivotCols = '';

WHILE @Counter < @MonthCount

BEGIN

SELECT

@CastPivotCols = @CastPivotCols + 'CASE WHEN '

+ QUOTENAME(LEFT(DATENAME(MONTH, DATEADD(MM,@Counter,@FromDate)),3) + ' ' +

DATENAME(YEAR, DATEADD(MM,@Counter,@FromDate))) +

' < 0 THEN ''Invalid'' ELSE CAST(' +

QUOTENAME(LEFT(DATENAME(MONTH, DATEADD(MM,@Counter,@FromDate)),3) + ' ' +

DATENAME(YEAR, DATEADD(MM,@Counter,@FromDate))) + ' AS varchar(10)) END AS ' +

QUOTENAME(LEFT(DATENAME(MONTH, DATEADD(MM,@Counter,@FromDate)),3) + ' ' +

DATENAME(YEAR, DATEADD(MM,@Counter,@FromDate))) + CHAR(13) + CHAR(10) + CHAR(9) + ',',

@Counter = @Counter + 1;

END

SELECT @CastPivotCols = SUBSTRING(@CastPivotCols,0,LEN(@CastPivotCols) - 1);

SELECT @PivotColumns AS PivotColumns, @FromDate AS FromDate, @ToDate AS ToDate, @CastPivotCols AS CastPivotCols, LEN(@CastPivotCols) AS LEN_PVT_COLS;

SET @DynamicSql = 'SELECT Products, ' + @CastPivotCols + 'FROM

(

SELECT

Products,

CONVERT(CHAR(4), CollectionDate, 100) + CONVERT(CHAR(4), CollectionDate, 120) AS Month,

Samples = SUM(distinct ItemCount)

FROM dbo.TestPivot

WHERE (CollectionDate BETWEEN @startDate AND @endDate)

GROUP BY Products,

CONVERT(CHAR(4), CollectionDate, 100) + CONVERT(CHAR(4), CollectionDate, 120)

) AS MDEOutput

PIVOT (AVG(Samples) FOR MONTH IN (' + @PivotColumns + ')

) AS OUTPUT

ORDER BY OUTPUT.Products';

PRINT @DynamicSql;

DECLARE @ParameterList NVARCHAR(200) = N'@startDate DATETIME, @endDate DATETIME';

EXEC sp_executesql @DynamicSql, @ParameterList, @FromDate, @ToDate;

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I just thought on giving a shorter solution that uses a tally table.

CREATE TABLE dbo.TestPivot(

CollectionDate DATETIME,

Products VARCHAR(40),

ItemCount INT

);

INSERT INTO dbo.TestPivot

SELECT '4/1/2015','Benz' , 20

UNION

SELECT '4/2/2015','Benz' , -10

UNION

SELECT '4/3/2015','Toyota' , 10

UNION

SELECT '4/1/2015','Toyota' , -5

UNION

SELECT '5/1/2015','Benz' , 200

UNION

SELECT '5/6/2015','Benz' , -1000

UNION

SELECT '6/1/2015','Toyota' , -200

UNION

SELECT '6/3/2015','Toyota' , 20;

DECLARE

@MonthCount TINYINT,

@Counter TINYINT,

@PivotColumns VARCHAR(512),

@CastPivotCols VARCHAR(1024),

@DynamicSql NVARCHAR(MAX),

@FromDate DATE = '4/1/2015',

@ToDate DATE = '6/1/2015';

SELECT @ToDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@ToDate)+1,0)); -- End Day of the month

SELECT @CastPivotCols = '';

WITH E(n) AS(

SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)

),

cteTally AS(

SELECT TOP ( DATEDIFF(MM,@FromDate,@ToDate) + 1)

DATEADD(MM, ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) - 1, @FromDate) calMonth

FROM E a, E b, E c -- 1000 rows enough for 83 years

)

SELECT

@PivotColumns = ISNULL( @PivotColumns + ',', '') + QUOTENAME(RIGHT( CONVERT( varchar(11), calMonth, 106),8)),

@CastPivotCols = @CastPivotCols + ', CASE WHEN ' + QUOTENAME(RIGHT( CONVERT( varchar(11), calMonth, 106),8)) +

' < 0 THEN ''Invalid'' ELSE CAST(' +

QUOTENAME(RIGHT( CONVERT( varchar(11), calMonth, 106),8)) + ' AS varchar(10)) END AS ' +

QUOTENAME(RIGHT( CONVERT( varchar(11), calMonth, 106),8)) + CHAR(13) + CHAR(10) + CHAR(9)

FROM cteTally;

SELECT @PivotColumns AS PivotColumns,

@FromDate AS FromDate,

@ToDate AS ToDate,

@CastPivotCols AS CastPivotCols,

LEN(@CastPivotCols) AS LEN_PVT_COLS;

SET @DynamicSql = 'SELECT Products ' + @CastPivotCols + 'FROM

(

SELECT

Products,

CONVERT(CHAR(4), CollectionDate, 100) + CONVERT(CHAR(4), CollectionDate, 120) AS Month,

Samples = SUM(distinct ItemCount)

FROM dbo.TestPivot

WHERE (CollectionDate BETWEEN @startDate AND @endDate)

GROUP BY Products,

CONVERT(CHAR(4), CollectionDate, 100) + CONVERT(CHAR(4), CollectionDate, 120)

) AS MDEOutput

PIVOT (AVG(Samples) FOR MONTH IN (' + @PivotColumns + ')

) AS OUTPUT

ORDER BY OUTPUT.Products';

PRINT @DynamicSql;

DECLARE @ParameterList NVARCHAR(200) = N'@startDate DATETIME, @endDate DATETIME';

EXEC sp_executesql @DynamicSql, @ParameterList, @FromDate, @ToDate;

GO

DROP TABLE dbo.TestPivot

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2