# Row wise Total in Pivot table ,coming wrong

• ### Question

• i am trying to calculating total row wise in Pivot,which is coming wrong.

```CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);

INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);

INSERT INTO #Probale VALUES
(1,1,1,001,100,'2020-01-13',null)
, (2,3,1,001,200,'2020-01-15',null)
, (3,11,1,002,200,'2020-01-15',null)
, (5,10,1,002,200,'2020-01-16',null)
, (6,1,1,003,200,'2020-01-16',null)
, (7,3,1,003,200,'2020-01-17',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

declare @fromdate date='2020-01-13'
declare @todate date='2020-01-15'
declare @columns varchar(max)
declare @convert varchar(max)
declare @columns1 varchar(max)

select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null for xml path('')), 1, 2, '') + ']'

select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null   for xml path(''))

set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

set @convert =
('select codeitem, '+ @columns1+','+ @GrandTotalCol + ' from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno
where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
and P.DelID is null

) smallbale
pivot(sum(prdqty) for Order_Ref_No
in ('+@columns+')) as pivottable')

execute (@convert)

drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale```

akhter

Friday, August 14, 2020 4:18 AM

• Also, it's is not a nice idea to change requirements or code and not even try to make the correct changes yourself:

```CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),
(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');

INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);

INSERT INTO #Probale VALUES
(1,1,1,001,100,'2020-01-13',null)
, (2,3,1,001,200,'2020-01-15',null)
, (3,11,1,002,200,'2020-01-15',null)
, (5,10,1,002,200,'2020-01-16',null)
, (6,1,1,003,200,'2020-01-16',null)
, (7,3,1,003,200,'2020-01-17',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

---------------------
declare @fromDate date='2020-01-13';
declare @toDate date='2020-01-15';

declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);

drop table if exists #TempResults;

SELECT p.prdqty,s.order_ref_no, i.Descriptionitem
into #TempResults
from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno
inner join #itemmasterfile i on i.codeitem=p.codeitem
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' +
quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)'
from #TempResults
for xml path('')), 1, 3, '');

set @sqlQuery =
'select ISNULL(DescriptionItem, ''Total:'')  as DescriptionItem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from

(select DescriptionItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
GROUP BY order_Ref_No, ROLLUP(DescriptionItem)) sm
pivot(sum(Qty) for Order_Ref_No
in (' + @pvtColumns + ')) as pivottable
order by case when PivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem';

--print @sqlQuery;

execute (@sqlQuery)

drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale
drop table if exists #TempResults;```

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Wednesday, August 19, 2020 6:47 AM
Wednesday, August 19, 2020 5:33 AM

### All replies

• Well, I see the problem in your code, but if you want to see it yourself and also being able to debug it, start from

PRINT @Convert

---------------------------------------

Hint - why did you repeat the same expression twice in your GrandTotal variable?

Also, I suggest to use QUOTENAME function - it will make your code more readable. And use the same approach you use for COLUMNS (e.g. use XML PATH('') to get the value concatenated).

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My TechNet articles

• Edited by Friday, August 14, 2020 4:52 AM
Friday, August 14, 2020 4:52 AM
• HI Naomi,

what does it mean?

Hint - why did you repeat the same expression twice in your GrandTotal variable?

i am getting below on Print

```(20 row(s) affected)

(11 row(s) affected)

(6 row(s) affected)

(3 row(s) affected)

(9 row(s) affected)
select codeitem, ISNULL([A_01],0) [A_01],ISNULL([B_01],0) [B_01],ISNULL([A_01],0) + ISNULL ([A_01],0) + ISNULL ([B_01],0)  from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno
where  P.Entrydate between '2020-01-13' and '2020-01-15'
and P.DelID is null

) smallbale
pivot(sum(prdqty) for Order_Ref_No
in ([A_01],[B_01])) as pivottable
```

akhter

• Edited by Friday, August 14, 2020 5:40 AM more addition
Friday, August 14, 2020 5:38 AM
• Hi Akhterhussain，

```declare @fromdate date='2020-01-13'
declare @todate date='2020-01-15'
declare @columns varchar(max)
declare @convert varchar(max)
declare @columns1 varchar(max)

select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null for xml path('')), 1, 2, '') + ']'

select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null   for xml path(''))

set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null
SET @GrandTotalCol = right(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1),len(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1))-17)

set @convert =
('select codeitem, '+ @columns1+','+ @GrandTotalCol + ' as total  from (select S.Order_Ref_No,P.codeitem,P.prdqty from #Probale P inner join #salesorder S on S.OrderNo=P.orderno
where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
and P.DelID is null

) smallbale
pivot(sum(prdqty) for Order_Ref_No
in ('+@columns+')) as pivottable')

execute (@convert)```

Best Regards
Echo

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

• Edited by Friday, August 14, 2020 6:06 AM
Friday, August 14, 2020 5:56 AM
• Hi Echo Liuz,

please check that row total still coming wrong,second thing i do not want to sum codeitem into row total

below is data

```CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);

INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);

INSERT INTO #Probale VALUES
(1,1,1,001,100,'2020-01-13',null)
, (2,3,1,001,200,'2020-01-15',null)
, (3,11,1,002,200,'2020-01-15',null)
, (5,10,1,002,200,'2020-01-16',null)
, (6,1,1,003,200,'2020-01-16',null)
, (7,3,1,003,200,'2020-01-17',null)
, (8,1,1,003,200,'2020-01-13',null)
, (9,3,1,003,200,'2020-01-13',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

declare @fromdate date='2020-01-13'
declare @todate date='2020-01-15'
declare @columns varchar(max)
declare @convert varchar(max)
declare @columns1 varchar(max)

select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null for xml path('')), 1, 2, '') + ']'

select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null   for xml path(''))

set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null
SET @GrandTotalCol = right(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1),len(LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1))-17)

set @convert =
('select Name, '+ @columns1+','+ @GrandTotalCol + '  from (select S.Order_Ref_No,P.codeitem,i.Descriptionitem as Name,P.prdqty from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno inner join #ItemMasterFile i on i.codeitem=p.codeitem
where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
and P.DelID is null

) smallbale
pivot(sum(prdqty) for Order_Ref_No
in ('+@columns+')) as pivottable')

execute (@convert)
drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale```

Output

akhter

• Edited by Friday, August 14, 2020 6:36 AM image upload
Friday, August 14, 2020 6:30 AM
• Hi Akhterhussain，

I expanded the time range to show column C_01：

```declare @fromdate date='2019-01-13'
declare @todate date='2021-01-15'
declare @columns varchar(max)
declare @convert varchar(max)
declare @columns1 varchar(max)

select   @columns = stuff (( select distinct'],[' +  Cast(S.Order_Ref_No AS Varchar(10))
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null for xml path('')), 1, 2, '') + ']'

select   @columns1 = (SELECT  distinct ',ISNULL([' + Cast(S.Order_Ref_No AS Varchar(10))+'],0)'  +  ' ['+Cast(S.Order_Ref_No AS Varchar(10))+']'
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate
and P.DelID is null   for xml path(''))

set @columns1=SUBSTRING(@columns1,2,len(@columns1)-1)

/* GRAND TOTAL COLUMN */
DECLARE @GrandTotalCol	NVARCHAR (MAX)
SELECT @GrandTotalCol = COALESCE (@GrandTotalCol + 'ISNULL ([' + CAST (S.Order_Ref_No AS Varchar(10)) +'],0) + ', 'ISNULL([' + CAST(S.Order_Ref_No AS Varchar(10))+ '],0) + ')
from #Probale P inner join #salesorder S on S.OrderNo=P.orderno where  P.Entrydate between @fromdate and  @todate   and P.DelID is null
SET @GrandTotalCol = LEFT (@GrandTotalCol, LEN (@GrandTotalCol)-1)

set @convert =
('select Name, '+ @columns1+',('+ @GrandTotalCol + ')/2 as total  from (select S.Order_Ref_No,P.codeitem,i.Descriptionitem as Name,P.prdqty from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno inner join #ItemMasterFile i on i.codeitem=p.codeitem
where  P.Entrydate between ''' + CONVERT(VARCHAR(20),@fromdate,121) + ''' and ''' + CONVERT(VARCHAR(20), @todate, 121) + '''
and P.DelID is null

) smallbale
pivot(sum(prdqty) for Order_Ref_No
in ('+@columns+')) as pivottable')

execute (@convert)```

Best Regards
Echo

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !

Friday, August 14, 2020 8:31 AM
• Well, here is what you got for your GrandTotal and obviously it's wrong:

`ISNULL([A_01],0) + ISNULL ([A_01],0) + ISNULL ([B_01],0) `

I can fix your code for you, but I was hoping you'll do an effort yourself. Anyway, here is how you can get your GrandTotal correctly -

```if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults

SELECT * -- list columns you need only here
into #TempResults
from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults
for xml path('')), 1, 2, '')

select @columns1 = stuff((SELECT  distinct ', ISNULL(' + quotename(CAST(Order_Ref_No AS Varchar(10))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults for xml path('')), 1, 2,'')

/* GRAND TOTAL COLUMN */

select @GrandTotals = stuff((SELECT  distinct ' + ISNULL(' + quotename(CAST(Order_Ref_No AS Varchar(10))) + ', 0)'
from #TempResults for xml path('')), 1, 3,'')

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total```

This is from the top of my head - you just need to first figure out what do you want to get as a static query and then may work backwards to get it. That's my usual approach for complex dynamic queries and it may take practice.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My TechNet articles

• Edited by Sunday, August 16, 2020 3:06 AM
Friday, August 14, 2020 2:38 PM
• Hi echo liuz ,

my answer still not coming correct

akhter

Friday, August 14, 2020 7:12 PM
• Did you see my response to you? Did you make an effort to use it?

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Friday, August 14, 2020 7:18 PM
• hi Naomi N,

I am working on your provided query,trying to solve it

```Msg 141, Level 15, State 1, Line 19
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 102, Level 15, State 1, Line 19
Incorrect syntax near ')'.
Msg 156, Level 15, State 10, Line 26
Incorrect syntax near the keyword 'AS'.```

akhter

Friday, August 14, 2020 7:31 PM
• I made a correction in my original reply, looks like STUFF function got missing from 2 last statements. Try again and if you can not make it post what you have so far, I'll try to correct it.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Friday, August 14, 2020 7:39 PM
• Here is my current query,but how i will sum prdqty from Probale table

```if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults
declare @fromdate date='2019-01-13'
declare @todate date='2021-01-15'
declare @columns varchar(max)
declare @columns1 varchar(max)
DECLARE @GrandTotals	NVARCHAR (MAX)

SELECT * -- list columns you need only here
into #TempResults
from Probale P
inner join salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults
for xml path('')), 1, 2, '')

select @columns1 = (SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(10))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults
for xml path('')), 1, 2, '')

/* GRAND TOTAL COLUMN */

select @GrandTotals = (SELECT  distinct ' + ISNULL(' + quotename(Order_Ref_No AS Varchar(10))) + ', 0)'
from #TempResults for xml path('')), 1, 3,'')

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total```

akhter

Friday, August 14, 2020 7:44 PM
• 1. Do you see my comment - List your columns here - this is what you actually need to do instead of *

2. Try this change and let's do one step at a time:

```if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults
declare @fromdate date='2019-01-13'
declare @todate date='2021-01-15'
declare @columns nvarchar(max)
declare @columns1 nvarchar(max)
DECLARE @GrandTotals	NVARCHAR (MAX)

-------  1 ---- in the statement below use the actual column names instead of *

SELECT * -- list columns you need only here
into #TempResults
from Probale P
inner join salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

------
SELECT * from #TempResults --- examine what you got, if it's OK, comment out

select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults
for xml path('')), 1, 2, '')

print @Columns -- this is to examine and verify it's OK, once you looked and verified, you can comment out

select @columns1 = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(10))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults
for xml path('')), 1, 2, '')

print @columns1 --- Again, to verify, once satisfied, comment out

/* GRAND TOTAL COLUMN */

select @GrandTotals = STUFF((SELECT  distinct ' + ISNULL(' + quotename(CAST(Order_Ref_No AS Varchar(10))) + ', 0)'
from #TempResults for xml path('')), 1, 3,'')

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total

print @GrandTotals -- show here what you get before we proceed with next steps```

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My TechNet articles

• Edited by Friday, August 14, 2020 8:35 PM
Friday, August 14, 2020 7:50 PM
• getting Below error

Msg 156, Level 15, State 10, Line 38
Incorrect syntax near the keyword 'AS'.
``` /* GRAND TOTAL COLUMN */

select @GrandTotals = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Order_Ref_No AS Varchar(10))) + ', 0)'
from #TempResults for xml path('')), 1, 3,'')```

akhter

Friday, August 14, 2020 8:00 PM
• Msg 2705, Level 16, State 3, Line 11
Column names in each table must be unique. Column name 'OrderNo' in table '#TempResults' is specified more than once.

if i execute below code..

```if object_id('#TempResults', N'U') iS NOT NULL DROP TABLE #TempResults
declare @fromdate date='2019-01-13'
declare @todate date='2021-01-15'
declare @columns nvarchar(max)
declare @columns1 nvarchar(max)
DECLARE @GrandTotals	NVARCHAR (MAX)

-------  1 ---- in the statement below use the actual column names instead of *

SELECT * -- list columns you need only here
into #TempResults
from Probale P
inner join salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

------
SELECT * from #TempResults --- examine what you got, if it's OK, comment out

select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(10)))
from #TempResults
for xml path('')), 1, 2, '')

print @Columns -- this is to examine and verify it's OK, once you looked and verified, you can comment out
```

akhter

Friday, August 14, 2020 8:20 PM
• CAST is missing here -

```select @GrandTotals = STUFF((SELECT  distinct ' + ISNULL(' + quotename(CAST(Order_Ref_No AS Varchar(10))) + ', 0)'  from #TempResults for xml path('')), 1, 3,'')

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
```

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My TechNet articles

• Edited by Friday, August 14, 2020 8:34 PM
Friday, August 14, 2020 8:33 PM
• Hi Naomi,

i tried to figure out solution ,but could not resolve

Error

(88489 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TH-43'.

```Drop table #tempResults
declare @fromdate date='2019-03-16'
declare @todate date='2021-03-16'
declare @columns nvarchar(max)
declare @columns1 nvarchar(max)
DECLARE @GrandTotals	NVARCHAR (MAX)

-------  1 ---- in the statement below use the actual column names instead of *

SELECT p.prdqty,s.order_ref_no
into #TempResults
from Probale P
inner join salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

------
--SELECT * from #TempResults --- examine what you got, if it's OK, comment out

select @columns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '')

--print @Columns -- this is to examine and verify it's OK, once you looked and verified, you can comment out

select @columns1 = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' + quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '')

--print @columns1 --- Again, to verify, once satisfied, comment out

select @GrandTotals = STUFF((SELECT  distinct ' , ISNULL(' + quotename(CAST(Order_Ref_No AS Varchar(50))) + ', 0) '

from #TempResults for xml path('')), 1, 3,'')

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total

execute (@GrandTotals) -- show here what you get before we proceed with next steps```

akhter

Saturday, August 15, 2020 7:15 AM
• i tried to figure out solution ,but could not resolve

Error

(88489 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TH-43'.

PRINT @GrandTotals

Looking at the output, you may even be able to understand the error yourself.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Saturday, August 15, 2020 8:16 AM

select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
PRINT (@GrandTotals)
execute (@GrandTotals)

but issue is same

```(88489 row(s) affected)
ISNULL([TH-43], 0)  , ISNULL([RK-05], 0)  , ISNULL([FZ-12], 0)  , ISNULL([TH-47], 0)  , ISNULL([SH-30], 0)  , ISNULL([SG-32], 0)  , ISNULL([ML-15], 0)  , ISNULL([KS-04], 0)  , ISNULL([HP-18], 0)  , ISNULL([SH-33], 0)  , ISNULL([JM-47], 0)  , ISNULL([NK-05], 0)  , ISNULL([AC-10], 0)  , ISNULL([AZ-74], 0)  , ISNULL([AZ-78], 0)  , ISNULL([ML-EX], 0)  , ISNULL([AP-02], 0)  , ISNULL([AZ-79], 0)  , ISNULL([SH-37], 0)  , ISNULL([PH-17], 0)  , ISNULL([FZ-07], 0)  , ISNULL([TH-36], 0)  , ISNULL([MT-01], 0)  , ISNULL([AG-05], 0)  , ISNULL([DK-10], 0)  , ISNULL([AZ-76], 0)  , ISNULL([BNV], 0)  , ISNULL([KS-SP], 0)  , ISNULL([DK-12], 0)  , ISNULL([FZ-19], 0)  , ISNULL([AT-03], 0)  , ISNULL([JM-45], 0)  , ISNULL([AZ-71], 0)  , ISNULL([JM-39], 0)  , ISNULL([MT-03], 0)  , ISNULL([PH-18], 0)  , ISNULL([RK-07], 0)  , ISNULL([BL-14], 0)  , ISNULL([JM-EX], 0)  , ISNULL([FG], 0)  , ISNULL([HP-19], 0)  , ISNULL([TH-44], 0)  , ISNULL([HP-21], 0)  , ISNULL([BL-EX], 0)  , ISNULL([AZ-80], 0)  , ISNULL([FG-10], 0)  , ISNULL([FZ-06], 0)  , ISNULL([ML-16], 0)  , ISNULL([FG-08], 0)  , ISNULL([TH-45], 0)  , ISNULL([FZ-05], 0)  , ISNULL([HP-20], 0)  , ISNULL([AG-02], 0)  , ISNULL([RK-04], 0)  , ISNULL([NK-03], 0)  , ISNULL([FG-09], 0)  , ISNULL([TH-37], 0)  , ISNULL([B-32], 0)  , ISNULL([TH-42], 0)  , ISNULL([AZ-73], 0)  , ISNULL([JM-38], 0)  , ISNULL([JM-40], 0)  , ISNULL([DK-09], 0)  , ISNULL([JM-42], 0)  , ISNULL([AG-04], 0)  , ISNULL([RK-SP], 0)  , ISNULL([FZ-08], 0)  , ISNULL([KS-03], 0)  , ISNULL([SH-31], 0)  , ISNULL([KS-07], 0)  , ISNULL([AZ-72], 0)  , ISNULL([FZ-13], 0)  , ISNULL([JM-44], 0)  , ISNULL([FZ-17], 0)  , ISNULL([B-30], 0)  , ISNULL([AZ-70], 0)  , ISNULL([ML-17], 0)  , ISNULL([KS-05], 0)  , ISNULL([FZ-14], 0)  , ISNULL([PH-23], 0)  , ISNULL([FZ-10], 0)  , ISNULL([BL-15], 0)  , ISNULL([MX-P], 0)  , ISNULL([ZML], 0)  , ISNULL([AZ-77], 0)  , ISNULL([AZ-75], 0)  , ISNULL([TH-41], 0)  , ISNULL([HP-23], 0)  , ISNULL([SH-36], 0)  , ISNULL([PH-19], 0)  , ISNULL([TZ-01], 0)  , ISNULL([FZ-16], 0)  , ISNULL([DK-13], 0)  , ISNULL([KS-06], 0)  , ISNULL([AP-03], 0)  , ISNULL([KS-02], 0)  , ISNULL([ML-19], 0)  , ISNULL([SH-38], 0)  , ISNULL([JM-43], 0)  , ISNULL([PH-21], 0)  , ISNULL([SH-34], 0)  , ISNULL([AZ-81], 0)  , ISNULL([MT-04], 0)  , ISNULL([CH-02], 0)  , ISNULL([KS-01], 0)  , ISNULL([PK], 0)  , ISNULL([F-J], 0)  , ISNULL([NK-04], 0)  , ISNULL([SH-35], 0)  , ISNULL([FZ-11], 0)  , ISNULL([HP-24], 0)  , ISNULL([FZ-18], 0)  , ISNULL([NK-06], 0)  , ISNULL([TH-46], 0)  , ISNULL([JM-41], 0)  , ISNULL([NK-07], 0)  , ISNULL([AZ-69], 0)  , ISNULL([MT-02], 0)  , ISNULL([SG-33], 0)  , ISNULL([NK-02], 0)  , ISNULL([JM-SP], 0)  , ISNULL([NK-01], 0)  , ISNULL([AT-04], 0)  , ISNULL([B-31], 0)  , ISNULL([TH-39], 0)  , ISNULL([SH-32], 0)  , ISNULL([SD], 0)  , ISNULL([AC-11], 0)  , ISNULL([RK-06], 0)  , ISNULL([PH-20], 0)  , ISNULL([AG-01], 0)  , ISNULL([DK-14], 0)  , ISNULL([ML-18], 0)  , ISNULL([DK-08], 0)  , ISNULL([BS-EX], 0)  , ISNULL([HP-17], 0)  , ISNULL([DK-11], 0)  , ISNULL([TH-35], 0)  , ISNULL([TZ-02], 0)  , ISNULL([TH-40], 0)  , ISNULL([AG-03], 0)  , ISNULL([PH-22], 0)  , ISNULL([TH-48], 0)  , ISNULL([TH-38], 0)  , ISNULL([FZ-15], 0)  , ISNULL([HP-22], 0)  , ISNULL([CH-EX], 0)  , ISNULL([BL-13], 0)  , ISNULL([JM-46], 0)  , ISNULL([SP], 0)  , ISNULL([FZ-09], 0)  AS [Total]  AS [Total]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'TH-43'.
```

akhter

Saturday, August 15, 2020 8:49 AM
• select @GrandTotals = @GrandTotals + ' AS [Total] ' -- row total
PRINT (@GrandTotals)
execute (@GrandTotals)

but issue is same

Of course. The purpose of the PRINT is to see what you have produced. And you have produced nonsense. I mean, does that look like valid SQL to you?

You need to go back and try to understand what you are trying to do.

However, I'm kinda curious. Where do you aim do display all this pivot data? You don't have to do the pivoting in SQL Server. In fact, pivoting is a presentation device, so it is often best done in the presentation layer.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Saturday, August 15, 2020 9:23 PM
• @GrandTotals is part of the select, you cannot execute it. Why did you even try if I asked you to print it to verify first what you got?

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Sunday, August 16, 2020 2:43 AM
• I'm sorry, but did you even try to read what I wrote and see the comments I put for you? What do you think these comments meant?

```-------  1 ---- in the statement below use the actual column names instead of *

SELECT * -- list columns you need only here```

They are instructions what YOU SHOULD DO by yourself.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Sunday, August 16, 2020 2:46 AM
• BTW, why did you change what we had in @GrandTotals? Do you see that this is not what we wanted to get after you changed '+' into ',' ? Do you understand the idea behind the @Columns1 and @GrandTotals?

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Sunday, August 16, 2020 2:50 AM
• hi Naomi N,

i tried alot ,but not getting done,your humbly requested to do favour to create whole query ,to sum rows and columns wise total please.

akhter

Tuesday, August 18, 2020 2:00 AM
• Ok, I'll do that later tonight since I'm going for a walk now.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Tuesday, August 18, 2020 2:15 AM
• Thanks Alot,

My requirement is,

Row and Column wise Total,with date filter

akhter

Tuesday, August 18, 2020 2:46 AM
• hi Naomi N,

i tried alot ,but not getting done,your humbly requested to do favour to create whole query ,to sum rows and columns wise total please.

I repeat my question: where do  you intend to consume this result? There are very good chances that you are better off performing the pivoting elsewhere and return a regular result set from SQL Server.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Tuesday, August 18, 2020 10:44 AM
• HI Erland Sommkarskog,

where do  you intend to consume this result?   i do not understand you.

akhter

Tuesday, August 18, 2020 10:53 AM
• Erland is asking - once you got the results, how are you going to use them? Are you writing a stored procedure or direct query - which application is going to use the results and how?

And sorry for not replying last night - after the walk I went right to bed (it was after 10pm). So I'm going to start my day from trying to help you now.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Tuesday, August 18, 2020 1:32 PM
• Ok, here is your whole query - I changed names of the variables to give them better meaning:

```CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),
(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');

INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);

INSERT INTO #Probale VALUES
(1,1,1,001,100,'2020-01-13',null)
, (2,3,1,001,200,'2020-01-15',null)
, (3,11,1,002,200,'2020-01-15',null)
, (5,10,1,002,200,'2020-01-16',null)
, (6,1,1,003,200,'2020-01-16',null)
, (7,3,1,003,200,'2020-01-17',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

---------------------
declare @fromDate date='2020-01-13';
declare @toDate date='2020-01-15';

declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);

drop table if exists #TempResults;

SELECT p.prdqty,s.order_ref_no, p.CodeItem
into #TempResults
from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' +
quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)'
from #TempResults
for xml path('')), 1, 3, '');

set @sqlQuery =
'select codeItem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from #TempResults
pivot(sum(prdqty) for Order_Ref_No
in (' + @pvtColumns + ')) as pivottable';

print @sqlQuery;

execute (@sqlQuery)

drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale
drop table if exists #TempResults;```

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Tuesday, August 18, 2020 1:44 PM
• Hi Naomi,

last requirement for column wise total .please

akhter

Tuesday, August 18, 2020 7:52 PM
• So, do you want to add one extra final row for the totals column based (because I already have one row based)? Can you show the desired output?

The reason I am asking is that it's not very easy using PIVOT syntax, I think. Much easier using case based pivot (for the actual PIVOT syntax I need to figure out the best solution - especially for dynamic PIVOT we're doing). Need a bit of research.

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

• Edited by Tuesday, August 18, 2020 8:41 PM
Tuesday, August 18, 2020 8:36 PM
• where do  you intend to consume this result?   i do not understand you.

You are not running queries out of thin air, but presumably this is do be displayed in an application of some sort. That would be a web application, a report in Reporting Services, an Excel file, Access, or even be exported to the file.

So what will happnen with the result set when it leaves SQL Server?

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Tuesday, August 18, 2020 9:17 PM
• Try

```CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),
(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');

INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);

INSERT INTO #Probale VALUES
(1,1,1,001,100,'2020-01-13',null)
, (2,3,1,001,200,'2020-01-15',null)
, (3,11,1,002,200,'2020-01-15',null)
, (5,10,1,002,200,'2020-01-16',null)
, (6,1,1,003,200,'2020-01-16',null)
, (7,3,1,003,200,'2020-01-17',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

---------------------
declare @fromDate date='2020-01-13';
declare @toDate date='2020-01-15';

declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);

drop table if exists #TempResults;

SELECT p.prdqty,s.order_ref_no, p.CodeItem
into #TempResults
from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' +
quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)'
from #TempResults
for xml path('')), 1, 3, '');

select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
GROUP BY order_Ref_No, ROLLUP(CodeItem)

set @sqlQuery =
'select ISNULL(CAST(codeItem as varchar(100)), ''Total:'')  as Item, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from

(select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
GROUP BY order_Ref_No, ROLLUP(CodeItem)) sm
pivot(sum(Qty) for Order_Ref_No
in (' + @pvtColumns + ')) as pivottable
order by ISNULL(CodeItem, 99999999)';

--print @sqlQuery;

execute (@sqlQuery)

drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale
drop table if exists #TempResults;```

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Wednesday, August 19, 2020 3:08 AM
• Unmarked as answer by Wednesday, August 19, 2020 3:14 AM
Tuesday, August 18, 2020 10:00 PM
• hi Naomi,

i little modified my query which is below,i used descriptionitem instead of codeitem,then row total coming in middle

```declare @fromDate date='2020-01-13';
declare @toDate date='2020-01-15';

declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);

SELECT p.prdqty,s.order_ref_no, i.Descriptionitem
into #TempResults
from Probale P
inner join salesorder S on S.OrderNo=P.orderno
inner join itemmasterfile i on i.codeitem=p.codeitem
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' +
quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)'
from #TempResults
for xml path('')), 1, 3, '');

--select CodeItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
--GROUP BY order_Ref_No, ROLLUP(CodeItem)

set @sqlQuery =
'select ISNULL(CAST(Descriptionitem as varchar(100)), ''Total:'')  as Descriptionitem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from

(select Descriptionitem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
GROUP BY order_Ref_No, ROLLUP(Descriptionitem)) sm
pivot(sum(Qty) for Order_Ref_No
in (' + @pvtColumns + ')) as pivottable
order by ISNULL(Descriptionitem, 99999999)';

--print @sqlQuery;

execute (@sqlQuery)
drop table #TempResults;```

ouput

akhter

Wednesday, August 19, 2020 3:17 AM
• Change ORDER BY this way:

case when pivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

Wednesday, August 19, 2020 5:28 AM
• Also, it's is not a nice idea to change requirements or code and not even try to make the correct changes yourself:

```CREATE TABLE #SalesOrder (OrderNo INT,Order_Ref_No VARCHAR(50), Order_date date,Status Varchar(50));
CREATE TABLE #OrderDetail (IDOD INT,OrderNO int,CodeItem int,orderqty int);
CREATE TABLE #Sections (SecID INT,Secnam VARCHAR(50));
CREATE TABLE #ItemMasterFile(CodeItem INT,Descriptionitem VARCHAR(50),SecID INT,weight int);
CREATE TABLE #Probale(BID INT,CodeItem INT,prdQTY INT,Orderno int,weight int,Entrydate DATETIME,DelID int);
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(15,'m--Shirst'),(16,'M-Scrap'),(17,'M-Scrit'),
(18,'M-Men'),(19,'M-HHR'),(20,'M-Sports');

INSERT INTO #ItemMasterFile VALUES
(1,'A',1,100)
, (2,'B',2,100)
, (3,'C',3,100)
, (4,'D',4,100)
, (5,'e',5,100)
, (6,'f',6,100)
, (7,'g',4,100)
, (8,'h',4,100)
, (9,'K',2,100)
, (10,'L',4,100)
, (11,'M',2,100);

INSERT INTO #Probale VALUES
(1,1,1,001,100,'2020-01-13',null)
, (2,3,1,001,200,'2020-01-15',null)
, (3,11,1,002,200,'2020-01-15',null)
, (5,10,1,002,200,'2020-01-16',null)
, (6,1,1,003,200,'2020-01-16',null)
, (7,3,1,003,200,'2020-01-17',null);

INSERT INTO #SalesOrder VALUES(001,'A_01','01-05-2019','Open')
, (002,'B_01','01-05-2019','Open')
, (003,'C_01','01-05-2019','Open');

INSERT INTO #OrderDetail VALUES
(1,001,1,5)
, (2,001,2,3)
, (3,001,3,2)
, (4,002,10,4)
, (5,002,11,3)
, (6,002,3,2)
, (7,003,1,4)
, (8,003,2,3)
, (9,003,3,2);

---------------------
declare @fromDate date='2020-01-13';
declare @toDate date='2020-01-15';

declare @pvtColumns nvarchar(max), @sqlQuery nvarchar(max), @qryColumns nvarchar(max), @totalsExpression nvarchar(max);

drop table if exists #TempResults;

SELECT p.prdqty,s.order_ref_no, i.Descriptionitem
into #TempResults
from #Probale P
inner join #salesorder S on S.OrderNo=P.orderno
inner join #itemmasterfile i on i.codeitem=p.codeitem
where P.Entrydate between @fromdate and  @todate
and P.DelID is null;

select @pvtColumns = stuff (( select distinct ', ' +  quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @qryColumns = STUFF((SELECT  distinct ', ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0) AS ' +
quotename(Cast(Order_Ref_No AS Varchar(50)))
from #TempResults
for xml path('')), 1, 2, '');

select @totalsExpression = STUFF((SELECT  distinct ' + ISNULL(' + quotename(Cast(Order_Ref_No AS Varchar(50))) +', 0)'
from #TempResults
for xml path('')), 1, 3, '');

set @sqlQuery =
'select ISNULL(DescriptionItem, ''Total:'')  as DescriptionItem, '+ @qryColumns +' , '+ @totalsExpression + ' as [Row Total] from

(select DescriptionItem, sum(prdQty) as Qty, Order_Ref_No from #TempResults
GROUP BY order_Ref_No, ROLLUP(DescriptionItem)) sm
pivot(sum(Qty) for Order_Ref_No
in (' + @pvtColumns + ')) as pivottable
order by case when PivotTable.DescriptionItem IS NULL then 1 else 0 end, DescriptionItem';

--print @sqlQuery;

execute (@sqlQuery)

drop table #SalesOrder
drop table #OrderDetail
drop table #Sections
drop table #ItemMasterFile
drop table #Probale
drop table if exists #TempResults;```

Looking for new opportunities

For every expert, there is an equal and opposite expert. - Becker's Law

My blog

My TechNet articles

• Marked as answer by Wednesday, August 19, 2020 6:47 AM
Wednesday, August 19, 2020 5:33 AM
• Thanks Alot Naomi,

for bearing me......

Thanks Alot again,,,,,next query is soon post

akhter

Wednesday, August 19, 2020 6:47 AM