SQLTeam.com | Weblogs | Forums

How to Row total and column total in pivot table to do?

i want row and column total in pivot table ,,please guide...

here my store procedure

`
ALTER Procedure [dbo].[Sp_Ragsdatewise] @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 ConIssuance 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 ContNo,ConIssuance.IWeight,
DATEPART(DAY, ConIssuance.EntryDate)as DDate
FROM ConIssuance inner JOIN Containerno on Containerno.CID=ConIssuance.CID
)prd
PIVOT (Sum(IWeight) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query, N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate

END`

provide some sample data for ConIssuance and Containerno

here is my both table data

Containerno
CID ContNo ConWeight Date
|1| ABC234 | 2200 | 2019-05-28|
|2| SFKLFJD3423 | 700 | 2019-05-28|
|3| SFKLFJ | 500 | 2019-05-29|
|4| sdfsdfsdf | 100 | 2019-05-29|
|5| fghfh | 200 | 2019-05-29|
|6| ABC123 | 20000 | 2019-05-29|

ConIssuance
IID CID EntryDate QTY IWeight FID
|1| 2 |2019-05-29| 1 | 100 | 1|

|2| 5 |2019-05-29| 1 |100 | 2|
|3| 3 |2019-05-29| 1 |100 | 1|
|4| 2 |2019-05-29| 1 |100 | 2|
|5| 3 |2019-05-29| 1 |100 | 1|

1 Like

thanks. but now present your data in a standard consumable format

create table #Containerno(CID int,  ContNo varchar(50), ConWeight int, ConDate date)
insert into #Containerno
select 1, 'ABC234', 2200, '2019-05-28' union
select 2, 'SFKLFJD3423',  700, '2019-05-28'

Nobody got time to parse your data and do all of the above for you. Help us help you

2 Likes

here i did as you told

create table #Containerno(CID int, ContNo varchar(50), ConWeight int, ConDate date)
insert into #Containerno
select 1, 'ABC234', 2200, '2019-05-28' union
select 2, 'SFKLFJD3423', 700, '2019-05-28'
select 3, 'ABC222', 600,'2019-05-28'
select 4, 'ABC33', 650,'2019-05-28'
select 5, 'ADD432', 340,'2019-05-28'

create table #ConIssuance(IID int, CID int,EntryDate date,QTY int,Iweight int, FID int)
insert into #ConIssuance
select 1, '5', '2019-05-28' ,'1','100' union
select 2, '2', '2019-05-28','1','100'
select 3, '4', '2019-05-28','1','100'
select 4, '1', '2019-05-28','1','100'
select 5, '3', '2019-05-28','1','100'

You are almost there. Please provide your expected results based on your sample data. This helps people confirm the are giving you the correct solution.

First, is this something that you could do in the presentation layer?

Second, I get very nervous providing output that combines data AND totals.... I have seen some horrible instances where the data is moved into another program (looking at you excel) and then a total for a row/column is taken, forgetting that it already includes the total.... Oh, how we laughed at those sales figures

Finally...
Try this as your starter for 10, the insert scripts you provided try and insert strings into ints and dont have all the required columns -- #ConIssuance --) so feel free to adapt the following as well as providing expected output

use tempdb
go

drop table if exists #Containerno
drop table if exists #ConIssuance
drop table if exists #Dates26

go

create table #Containerno(CID int, ContNo varchar(50), ConWeight int, ConDate date)
insert into #Containerno
select 1, 'ABC234', 2200, '2019-05-28' union
select 2, 'SFKLFJD3423', 700, '2019-05-28' union
select 3, 'ABC222', 600,'2019-05-28' union
select 4, 'ABC33', 650,'2019-05-28' union
select 5, 'ADD432', 340,'2019-05-28' 

create table #ConIssuance(IID int, CID int,EntryDate date,QTY int,Iweight int, FID int)
insert into #ConIssuance
select 1, 5, '2019-05-28' ,1,100, null union
select 2, 2, '2019-05-28',1,100, null union
select 3, 4, '2019-05-28',1,100, null union
select 4, 1, '2019-05-28',1,100, null union
select 5, 3, '2019-05-28',1,100 , null

DECLARE @StartDate DATE = '20190528'
DECLARE @EndDate DATE = '20190528'
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 #ConIssuance 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 ContNo, #ConIssuance.IWeight,
DATEPART(DAY, #ConIssuance.EntryDate)as DDate
FROM #ConIssuance inner JOIN #Containerno on #Containerno.CID=#ConIssuance.CID
)prd
PIVOT (Sum(IWeight) FOR DDate IN (' + @cols + ')) AS stat'
exec sp_executesql @query, N'@StartDate datetime,@enddate datetime', @StartDate=@StartDate,@Enddate=@Enddate



SET @query =
N'
SELECT contno, sum([28]) AS [28] FROM (
SELECT *
FROM (SELECT ContNo, #ConIssuance.IWeight,
DATEPART(DAY, #ConIssuance.EntryDate)as DDate
FROM #ConIssuance inner JOIN #Containerno on #Containerno.CID=#ConIssuance.CID
)prd
PIVOT (Sum(IWeight) FOR DDate IN (' + @cols + ')) AS stat
) AS D
GROUP BY ROLLUP (ContNo)'

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

thanks...how to mark this as answer