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 #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(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 @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

Answer 1

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 

instead of EXECUTE (@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 blog

My TechNet articles

Answer 2

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

Answer 3

Hi Akhterhussain，

Please try:

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 !
For more information, please refer to the sticky post.

Answer 4

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 #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),(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)
, (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

Answer 5

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 !
For more information, please refer to the sticky post.

Answer 6

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 blog

My TechNet articles

Answer 7

Hi echo liuz ,

my answer still not coming correct

---

akhter

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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 blog

My TechNet articles

Answer 13

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

Answer 14

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

Answer 15

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 blog

My TechNet articles

Answer 16

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

Answer 17

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'.

Add this line:

PRINT @GrandTotals

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

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 18

i add below

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

Answer 19

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, [e-mail]

Answer 20

@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

Answer 21

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

Answer 22

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

Answer 23

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

Answer 24

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

Answer 25

Thanks Alot,

My requirement is,

Row and Column wise Total,with date filter

---

akhter

Answer 26

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, [e-mail]

Answer 27

HI Erland Sommkarskog,

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

---

akhter

Answer 28

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

Answer 29

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);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),
(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

Answer 30

Hi Naomi,

last requirement for column wise total .please 

---

akhter

Answer 31

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

My blog

My TechNet articles

Answer 32

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, [e-mail]

Answer 33

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);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),
(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

Answer 34

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

Answer 35

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

Answer 36

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);
INSERT INTO #Sections VALUES(1,'HR'),(2,'Baby'),(3,'Ladies'),
(4,'Mix Rammage'),(5,'T-Shirst'),(6,'Scrap'),(7,'Scrit'),(8,'Men'),(9,'HHR'),
(10,'Sports'),(11,'m-HR'),(12,'M-Baby'),(13,'M-Ladies'),(14,'M-Mix Rammage'),
(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

Answer 37

Thanks Alot Naomi,

for bearing me......

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

---

akhter