Rows and column wise total in pivot

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I want row wise and column wise Total

SQL

Create table #Sections (SecID int, Secnam varchar(50))
create table #ItemMasterFile(CodeItem int, Descriptionitem varchar(max),SecID int,CID int)
create table #Probale (BID int, CodeItem int,   prdqtyint,EntryDate date)


insert into #Sections 
values
(1, 'HR'),
(2, 'Baby'),
(3, 'Ladies'),
(4, 'Mix Rammage'),

insert into #ItemMasterFile
values
(1, 'A',  '1'),
(2, 'B',  '2'),
(3, 'C',  '3'), 
(4, 'D' ,'4')


insert into #Probale
values
(1, '1', '1', '5/01/2019 '),
(2, '2', '1', '5/01/2019 '),
(3, '3', '1', '5/01/2019 '),
(4, '2', '1', '5/02/2019 '),
(5, '3', '1', '5/02/2019 '),
(6, '4', '1', '5/02/2019 '),
(6, '1', '1', '5/03/2019 '),
(7, '2', '1', '5/04/2019 '),
(8, '4', '1', '5/05/2019 '),

What I have tried:

here is my store procedure

SQL

Create Procedure [dbo].[Pivot_Item1] @StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT Distinct Entrydate INTO #Dates26 FROM Probale WHERE EntryDate BETWEEN @StartDate AND @Enddate ORDER BY EntryDate

SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')


SET @query =
N'SELECT *
FROM (SELECT Descriptionitem,Probale.prdqty,
DATEPART(DAY, Probale.EntryDate)as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

END

Posted 19-Jun-19 21:11pm

akhter86

Updated 19-Jun-19 22:51pm

Maciej Los

v2

Add a Solution

Comments

OriginalGriff 20-Jun-19 3:13am

And?
What does it do that you didn't expect, or not do that you did?
What help do you need?

akhter86 20-Jun-19 3:37am

Dear OriginalGriff,
I want Row and Column wise Total ,,above store procedure just display date wise Total of item ,,Now i want Rows Total or each item and Column wise Total of each Date.

OriginalGriff 20-Jun-19 3:44am

And what have *you* tried in order to do that?
What did that produce, and what did you do to try and fix it?
What errors did you get?

akhter86 20-Jun-19 4:09am

Dear OriginalGriff,
i tried this ( ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT Distinct Entrydate INTO #Dates26
FROM Probale
WHERE EntryDate BETWEEN @StartDate AND @Enddate
ORDER BY EntryDate

SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate) )
--FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')

SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where CONVERT(varchar(10), EntryDate) =stat.ddate ) as total

FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),Probale.EntryDate) as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from cte'

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

END
)

error is coming ( Invalid column name 'ddate'.)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2019-06-19T22:52:00

Solution 1

Firstly you need to fix the code you are using to create the tables - currently it produces these errors

Msg 173, Level 15, State 1, Line 3
The definition for column 'prdqtyint' must include a data type.
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'insert'.
Msg 102, Level 15, State 1, Line 31
Incorrect syntax near ','.

Once you fixed those you will then get a further error

Msg 213, Level 16, State 1, Line 13
Column name or number of supplied values does not match table definition.

Because the values you are inserting into #ItemMasterFile do not match the definition of the table. You've also done that with #Probale where you are passing varchar values into int columns but SQL has converted that for you.

I commend you for giving us code to recreate your data tables, but for future reference your lack of care on this will make many members just give up.

The problem persists when I start to examine your dynamic query -

Msg 207, Level 16, State 1, Line 78
Invalid column name 'DelID'.

I just deleted that from my version.

Incidentally, I also dislike the inconsistency of

SQL

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

EntryDate is a date so why start using datetime. So I finally got your results of

Descriptionitem	1	2	3	4	5
A	1	NULL	1	NULL	NULL
B	1	1	NULL	1	NULL
C	1	1	NULL	NULL	NULL
D	NULL	1	NULL	NULL	1

This is where @OriginalGriff's comments come into play. You haven't made any effort to do this for yourself. So …

To get the row total you will need to add all of the columns together. Use the same technique you used to get @cols but note that you will have to deal with those NULL values - I suggest using something like

SELECT Descriptionitem, ISNULL([1],0) AS [1], … etc

To get the column totals you will need to UNION a query that just totals the columns. I put your pivot into a Common Table Expression to make this easier to do and added

UNION 
SELECT 'Total', SUM([1]), SUM([2]),SUM([3]),sum([4]),sum([5]), SUM(([1] + [2] + [3] + [4] + [5])) AS RowTotal
from CTE

Note I don't have to worry about ISNULL because SUM will handle null values for me.
But also note that I had to include the sum of the rowtotals to make the union work

Posted 19-Jun-19 22:52pm

CHill60

Comments

akhter86 20-Jun-19 4:58am

Dear Chill60

I have done UNION QUERY here is my in store procedure ...

i tried this ( ALTER Procedure [dbo].[Pivot_Item2]
@StartDate Date,
@Enddate Date

AS
BEGIN
DECLARE @cols AS NVARCHAR(MAX)
DECLARE @query AS NVARCHAR(MAX)
declare @sum nvarchar(max);
SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(prdqty)+')') from Probale;
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.

SET NOCOUNT ON;

SELECT Distinct Entrydate INTO #Dates26
FROM Probale
WHERE EntryDate BETWEEN @StartDate AND @Enddate
ORDER BY EntryDate

SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(10),EntryDate) )
--FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

--SET @cols = STUFF(@cols, 1, 1, '')

SET @query =
N'with cte as (SELECT *,(select sum(prdqty) from Probale where CONVERT(varchar(10), EntryDate) =stat.ddate ) as total

FROM (SELECT Descriptionitem,Probale.prdqty,

CONVERT(varchar(10),Probale.EntryDate) as DDate
FROM Probale left JOIN ItemMasterFile on ItemMasterFile.Codeitem=Probale.Codeitem
where Probale.DelID is Null and Probale.EntryDate BETWEEN @StartDate AND @Enddate )prd
PIVOT (Sum(prdqty) FOR DDate IN (' + @cols + ')) AS stat)

select * from cte

union all
select 0,'+@sum+', (select sum(prdqty ) from Probale ) from cte'

exec sp_executesql @query , N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

END
)
but
error is coming ( Invalid column name 'ddate'.)

CHill60 20-Jun-19 7:55am

Firstly have a look at your variable @sumIt's value is

sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1]),sum([1])

. Not what you want at all. Move that down to after you have populated #Dates26 and use the correct table i.e.

SELECT @sum= COALESCE(@sum + ',','') + ('sum('+QUOTENAME(Entrydate)+')') FROM (SELECT DISTINCT EntryDate AS EntryDate FROM #Dates26 ) TORDER BY EntryDate

which will give you

[2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05]

You will also need another variable which will give you

ISNULL([2019-05-01],0) AS [2019-05-01]...etc

Use the same technique. You have also completely ignored the point about ISNULL and having to add the columns together to get the row total
Having solved all of that put your originalpivot back into the body of the CTE - I have no idea what you are trying to achieve with those sub-queries. Get rid of them.
Your final query should look something like

;with cte as 
(	
     SELECT Descriptionitem, ISNULL([2019-05-01],0) AS [2019-05-01], ISNULL([2019-05-02],0) AS [2019-05-02],
	 		ISNULL([2019-05-03],0) AS [2019-05-03], ISNULL([2019-05-04],0) AS [2019-05-04],
 		ISNULL([2019-05-05],0) AS [2019-05-05]	
 	FROM 	
 	(		
 	 	SELECT Descriptionitem,#Probale.prdqty, #Probale.EntryDate as DDate		
 	 	FROM #Probale 		
 	 	left JOIN #ItemMasterFile on #ItemMasterFile.Codeitem=#Probale.Codeitem		
 	 	where --#Probale.DelID is Null and 
 	 	#Probale.EntryDate BETWEEN @StartDate AND @Enddate 	
 	)prd	
 	PIVOT 	
 	(		
 	 	Sum(prdqty) FOR DDate IN ([2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05])	
 	) AS stat
)
select Descriptionitem, [2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05],  		
([2019-05-01]+[2019-05-02]+[2019-05-03]+[2019-05-04]+[2019-05-05]) AS RowTotal
from cte
union
select 'Total',sum([2019-05-01]),sum([2019-05-02]),sum([2019-05-03]),sum([2019-05-04]),sum([2019-05-05]), 		
SUM([2019-05-01]+[2019-05-02]+[2019-05-03]+[2019-05-04]+[2019-05-05]) as ColumnTotal
from cte

Use

PRINT @query

instead of the exec until you have the query right and use a lot more CRLF and whitespace - it's a lot easier to see what is going on

akhter86 21-Jun-19 14:15pm

thanks for your guidance alot ,, i never used hardcore values in query,,no idea that how it will get done [2019-05-01],[2019-05-02],[2019-05-03],[2019-05-04],[2019-05-05], so still looking for solution ,,,

CHill60 25-Jun-19 7:41am

You already created that list of column names in your original post!

SELECT @cols = COALESCE(@cols + ',','') + QUOTENAME( CONVERT(varchar(2),EntryDate) )
FROM (SELECT DISTINCT DATEPART(DAY, EntryDate) AS EntryDate FROM #Dates26 ) T
ORDER BY EntryDate

The only difference is in your 2nd attempt in the comments you no longer use DATEPART and have used the entire date as a column name. I don't understand why you are still looking for a solution

Maciej Los 20-Jun-19 5:08am

5ed!