How to join two dynamic pivot(table data after pivot) in SQL Server-CodePudding

Explanations

I have a table and I have pivot the table of them in order to get only 1 table with all the data together. I've attached an image with the tables I have and the result that I would like to get.

Table:

After doing dynamic pivot using below query we will get record as we want but in two table

DECLARE @cols nvarchar(max)
DECLARE @cols1 nvarchar(max)

DECLARE @stmt nvarchar(max)
DECLARE @stmt1 nvarchar(max)

SELECT @cols = STRING_AGG(QUOTENAME([YearMonth]), ',') WITHIN GROUP (ORDER BY [YearMonth])
FROM (
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), CreatedOn, 6), 6), ' ', '/') AS [YearMonth]
FROM [dbo].[TblDemoData]
UNION
SELECT NULL
WHERE 1 = 0
) t
SELECT @cols1 = STRING_AGG(QUOTENAME([YearMonth1]), ',') WITHIN GROUP (ORDER BY [YearMonth1])
FROM (
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), CreatedOn, 6), 6), ' ', '_') AS [YearMonth1]
FROM [dbo].[TblDemoData]
UNION
SELECT NULL
WHERE 1 = 0
) t1

print @cols1


SELECT * INTO #Data
FROM
(
select AgentId,
case
when sum( MaximumScore)=0
then 0
else
sum(ISNULL(AchievedScore,0)*100)/sum(ISNULL(MaximumScore,0))
end as Score,
COUNT(T.Id) as NumberOfAudit,
REPLACE(RIGHT(CONVERT(VARCHAR(9), T.CreatedOn, 6), 6), ' ', '/') AS [YearMonth]
from [dbo].[TblDemoData] T
where T.AgentId=49
group by AgentId,REPLACE(RIGHT(CONVERT(VARCHAR(9), T.CreatedOn, 6), 6), ' ', '/')
)TAB



SELECT * INTO #Data1
FROM
(
select T.AgentId,
case
when sum( MaximumScore)=0
then 0
else
sum(ISNULL(AchievedScore,0)*100)/sum(ISNULL(MaximumScore,0))
end as Score,
COUNT(T.Id) as NumberOfAudit,
REPLACE(RIGHT(CONVERT(VARCHAR(9), T.CreatedOn, 6), 6), ' ', '_') AS [YearMonth1]
from [dbo].[TblDemoData] T

where T.AgentId=49
group by T.AgentId,REPLACE(RIGHT(CONVERT(VARCHAR(9), T.CreatedOn, 6), 6), ' ', '_')
)TAB
SELECT @stmt =
'SELECT * from
(
SELECT AgentId,NumberOfAudit, YearMonth FROM #Data
) x

PIVOT
(
sum(NumberOfAudit)
FOR [YearMonth] IN ('   @cols   ')
) as p1
 

SELECT * from
(
SELECT AgentId,Score, YearMonth1 FROM #Data1
) x

PIVOT
(
sum(Score)
FOR [YearMonth1] IN ('   @cols1   ')
) as p2
'
exec sp_executesql @stmt

Output from above query

Our Need

TABLE WITH DATA

CREATE TABLE [dbo].[TblDemoData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [AgentId] [int] NULL,
    [MaximumScore] [int] NULL,
    [AchievedScore] [int] NULL,
    [CreatedOn] [datetime] NULL,
 CONSTRAINT [PK_TblDemoData] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[TblDemoData] ON 
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (1, 49, 110, 110, CAST(N'2021-11-01T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (2, 49, 102, 99, CAST(N'2021-11-02T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (3, 49, 110, 102, CAST(N'2021-11-09T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (4, 49, 102, 102, CAST(N'2021-11-17T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (5, 49, 105, 105, CAST(N'2021-11-17T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (6, 49, 89, 89, CAST(N'2021-11-24T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (7, 49, 102, 102, CAST(N'2021-10-05T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (8, 49, 102, 102, CAST(N'2021-10-07T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (9, 49, 102, 102, CAST(N'2021-10-15T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (10, 49, 102, 102, CAST(N'2021-10-18T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (11, 49, 118, 118, CAST(N'2021-10-19T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (12, 49, 102, 102, CAST(N'2021-10-26T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (13, 49, 84, 79, CAST(N'2021-10-26T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[TblDemoData] ([Id], [AgentId], [MaximumScore], [AchievedScore], [CreatedOn]) VALUES (14, 49, 96, 0, CAST(N'2022-01-21T13:10:00.000' AS DateTime))
GO
SET IDENTITY_INSERT [dbo].[TblDemoData] OFF
GO

CodePudding user response：

Honestly, this should be something for your presentation layer not the SQL layer; especially as you want merged cells (a concept that does not exist in SQL).

I would personally switch over to conditional aggregation, rather than the restrictive PIVOT operator, and then do something like this:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

DECLARE @Delimiter nchar(3) = N','   @CRLF;

WITH DateRanges AS(
    SELECT DISTINCT
           DATEADD(MONTH, DATEDIFF(MONTH,0,CreatedOn),0) AS StartDate,
           DATEADD(MONTH, DATEDIFF(MONTH,0,CreatedOn) 1,0) AS EndDate
    FROM dbo.TblDemoData)
SELECT @SQL = N'SELECT DD.AgentID,'   @CRLF   
              STRING_AGG(N'       COUNT(CASE WHEN DD.CreatedOn >= '   QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''')   N' AND DD.CreatedOn < '   QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''')   N' THEN 1 END) AS '   QUOTENAME(CONCAT(YEAR(DR.StartDate),'-',DATENAME(MONTH,DR.StartDate),N'-Audits'))   N','   @CRLF  
                         N'       SUM(CASE WHEN DD.CreatedOn >= '   QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''')   N' AND DD.CreatedOn < '   QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''')   N' THEN AchievedScore* 1. ELSE 0 END) / '   @CRLF  
                         N'       SUM(CASE WHEN DD.CreatedOn >= '   QUOTENAME(CONVERT(varchar(8),DR.StartDate,112),'''')   N' AND DD.CreatedOn < '   QUOTENAME(CONVERT(varchar(8),DR.EndDate,112),'''')   N' THEN MaximumScore ELSE 0 END) AS '   QUOTENAME(CONCAT(YEAR(DR.StartDate),'-',DATENAME(MONTH,DR.StartDate),N'-Score')),@Delimiter) WITHIN GROUP (ORDER BY DR.StartDate)   @CRLF  
              N'FROM dbo.TblDemoData DD'   @CRLF   
              N'GROUP BY DD.AgentId;'
FROM DateRanges DR;

--PRINT @SQL; Your debugging best friend

EXEC sys.sp_executesql @SQL;

Note this doesn't give the columns in the order you asked, however, the order of the columns is meaningless, and (again) should be controlled in your presentation layer.