SQLTeam.com | Weblogs | Forums

Pivot Table Issue

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.

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

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

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

What about Order by field

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?

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

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

image

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

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')
1 Like

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