Pivot Table Issue

ravininave · July 22, 2020, 5:48pm

I have a data like this in my TestTable

GO
CREATE TABLE [dbo].[TestTable](
	[AppMstName] [nvarchar](50) NULL,
	[JoinDate] [nvarchar](50) NULL,
	[TotalBus] [int] NULL,
	[TotalMem] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER1', N'18/07/2020', 100000, 1)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER1', N'19/07/2020', 80000, 1)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER1', N'20/07/2020', 10000, 1)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER1', N'21/07/2020', 100000, 1)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER1', N'22/07/2020', 40000, 1)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER2', N'18/07/2020', 60000, 5)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER2', N'19/07/2020', 20000, 2)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER2', N'21/07/2020', 75000, 8)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER3', N'18/07/2020', 110000, 7)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER3', N'19/07/2020', 510000, 34)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER3', N'20/07/2020', 105000, 15)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER3', N'21/07/2020', 55000, 6)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER3', N'22/07/2020', 135000, 7)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER4', N'19/07/2020', 40000, 2)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER4', N'20/07/2020', 15000, 2)
GO
INSERT [dbo].[TestTable] ([AppMstName], [JoinDate], [TotalBus], [TotalMem]) VALUES (N'MANAGER4', N'21/07/2020', 10000, 1)
GO

and I have to show Datewise Total Business + TotalMem Horizontally
AppMstName 22/07/2020 21/07/2020 20/07/2020 19/07/2020 18/07/2020
MANAGER1 40000 - 1 100000 - 1 10000 - 1 80000 - 1 100000 - 1
MANAGER2 0 - 0 75000 - 8 0 - 0 20000 - 2 60000 - 5
MANAGER3 135000 - 7 55000 - 6 105000 - 15 510000 - 34 110000 - 7
MANAGER4 0 - 0 10000 - 1 15000 - 2 40000 - 2 0 - 00

and I have to order it on firstdate (22/07/2020) desc Means whose business is more he will
come on top.
Actually I am generating daily business + business count report for today and previous 4 days

I tried with pivot but I was unable to do it.

mike01 · July 22, 2020, 7:04pm

Not sure what Total Business + TotalMem is when you show Total Business then hyphen then Total Mem

  DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT JoinDate  
FROM TestTable 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
EXEC ('SELECT *  
FROM   
(  
        SELECT AppMstName, JoinDate, TotalBus + TotalMem as Total 
        FROM TestTable
) t  
PIVOT (sum(Total) FOR JoinDate IN (' + @ColsList + ')) PVT')

ravininave · July 22, 2020, 7:17pm

Yes I tried same like
Sum(total) for JoinDate
But I need another column besides totsl which is business count like
Total and TotalMem field
And first field in desc order

And it doesn't mean
TotalBus + TotalMem
It's is two different values which I have to show in one column.
TotalBus / TotalMem

mike01 · July 22, 2020, 7:51pm

How's this?

DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT JoinDate  
FROM TestTable 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
order by Head desc
 
EXEC ('SELECT *  
FROM   
(  
        SELECT AppMstName, JoinDate, cast(TotalBus as varchar(10)) + ''-'' + cast(TotalMem as varchar(10)) as Total 
        FROM TestTable
) t  
PIVOT (max(Total) FOR JoinDate IN (' + @ColsList + ')) PVT')

ravininave · July 22, 2020, 8:55pm

What about Order by field

mike01 · July 23, 2020, 12:36pm

Did that not order it correctly? When I used your data, the first column was 22/7/2020, then 21/7/2020 etc.. What order were you looking for?

ravininave · July 23, 2020, 1:03pm

On first date field ,i.e. 22/7/2020 I need total of that column should be in desc order
Means I have to order desc all data on first date field.
It shows today's business, so user will come to know who is topper of the day

ravininave · July 23, 2020, 10:54pm

From query we will get this output. But I have to sort it on first date column
in this case first date column is 22/07/2020

so as per business we will get first row of MANAGER3 as he has highest business
2nd will be MANAGER1 AND THEN MANAGER2 and MANAGER4

mike01 · July 24, 2020, 12:55pm

DECLARE @Colslist VARCHAR(MAX) ,
		@ColMax varchar(max)
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT JoinDate  
FROM TestTable 
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
order by Head desc
 
select top (1) @ColMax =  '[' + Head + ']'
FROM @Cols t  
order by Head desc

exec ('SELECT *  
FROM   
(  
        SELECT AppMstName, JoinDate, cast(TotalBus as varchar(10)) + ''-'' + cast(TotalMem as varchar(10)) as Total 
        FROM TestTable
) t  
PIVOT (max(Total) FOR JoinDate IN (' + @ColsList + ')) PVT 
Order by Cast(left(' + @ColMax + ', charIndex(''-'',' + @ColMax + ') - 1) as int) Desc')

ravininave · July 24, 2020, 1:56pm

Perfect! Superb. Working as I need.
Thank you mate.!