Query one table multiple times for specific data based Day/Month/Year and show results on single line

Transact-SQL
,
1

I am using the following database table and am pulling data for all results based on Day/Month/Year which returns the rows of all the matching criteria.

USE [tmdsDatabaseStatistics]
GO

/****** Object:  Table [dbo].[tblStatisticsLocationDay]    Script Date: 09/08/2022 07:54:47 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[tblStatisticsLocationDay](
[ControlPointName] [nvarchar](50) NULL,
[StationNumber] [int] NULL,
[codeline] [int] NULL,
[SubName] [nvarchar](50) NULL,
[Year] [int] NULL,
[Month] [int] NULL,
[Day] [int] NULL,
[OutOfSyncCount] [int] NULL,
[RecallLocalCount] [int] NULL,
[RecallRemoteCount] [int] NULL,
[IndicationCount] [int] NULL,
[ControlFailCount] [int] NULL,
[ControlPointStatusDownCount] [int] NULL,
[ControlSentCount] [int] NULL,
[FailureCount] [int] NULL,
[ResyncCount] [int] NULL,
[ControlDeliveryCount] [int] NULL,
[RecallRetryCount] [int] NULL,
[ResendCount] [int] NULL,
[FowardFlushCount] [int] NULL
) ON [PRIMARY]
GO

This will return the data I want;

USE tmdsDatabaseStatistics

DECLARE @year int
DECLARE @month int

DECLARE @year1 int
DECLARE @month1 int

DECLARE @year2 int
DECLARE @month2 int
DECLARE @day2 int

SET @year = 2022
SET @month = 8

SET @year1 = 2022
SET @month1 = 8

SET @year2 = 2022
SET @month2 = 8
SET @day2 = 31

SELECT T1.ControlPointName AS 'CP Name',
        T1.codeline AS Codeline,
T1.Type AS Type,
        T1.SubName AS 'Sub Division',
T1.Day As Day,
T1.Month AS Month,
T1.Year as Year,
        SUM(T1.ControlFailCount) AS 'Control Failures',
        SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down',
        T1.FailureCount AS 'Failures'
        FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
        FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
        WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = '1' and month = @month and year = @year and FailureCount < '500' and FailureCount > '99') AS T1
        GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount

UNION ALL

SELECT T2.ControlPointName AS 'CP Name',
        T2.codeline AS Codeline,
T2.Type AS Type,
        T2.SubName AS 'Sub Division',
T2.Day As Day,
T2.Month AS Month,
T2.Year as Year,
        SUM(T2.ControlFailCount) AS 'Control Failures',
        SUM(T2.ControlPointStatusDownCount) AS 'Control Point Down',
        T2.FailureCount AS 'Failures'
        FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
        FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
        WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = '15' and month = @month1 and year = @year1 and FailureCount < '500' and FailureCount > '99') AS T2
        GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount

UNION ALL

SELECT T3.ControlPointName AS 'CP Name',
        T3.codeline AS Codeline,
T3.Type AS Type,
        T3.SubName AS 'Sub Division',
T3.Day As Day,
T3.Month AS Month,
T3.Year as Year,
        SUM(T3.ControlFailCount) AS 'Control Failures',
        SUM(T3.ControlPointStatusDownCount) AS 'Control Point Down',
        T3.FailureCount AS 'Failures'
        FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
        FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
        WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day = @day2 and month = @month2 and year = @year2 and FailureCount < '500' and FailureCount > '99') AS T3
        GROUP BY ControlPointName, Codeline, Type, SubName, Day, Month, Year, FailureCount

Order by 'Failures' desc

This is how I want it to output;

ControlPointName1 Codeline1 Type1 Sub Division1 Day1 Month1 Year1 Control Failures1 Control Point Down1 Failures1 ControlPointName2 Codeline2 Type2 Sub Division2 Day2 Month2 Year2 Control Failures2 Control Point Down2 Failures2 ControlPointName3 Codeline3 Type3 Sub Division3 Day3 Month3 Year3 Control Failures3 Control Point Down3 Failures3
ZITA 6113 WSS 220 ZAP LINE 31 8 2022 5 4 491 ZITA 6113 WSS 220 ZAP LINE 31 8 2022 5 4 491 ZITA 6113 WSS 220 ZAP LINE 31 8 2022 5 4 491

This is the query I have tried to use to get the data row results I want displayed in the above format. The problem is it only returns a single line of data.

USE tmdsDatabaseStatistics

DECLARE @day2 int
DECLARE @month int
DECLARE @month1 int
DECLARE @month2 int

DECLARE @year int
DECLARE @year1 int
DECLARE @year2 int

SET @day2 = 31
SET @month = 8
SET @month1 = 8
SET @month2 = 8

SET @year = 2022
SET @year1 = 2022
SET @year2 = 2022

SELECT
    MAX(CASE WHEN month = @month AND year = @year THEN "CP Name" END) AS ControlPointName1,
    MAX(CASE WHEN month = @month AND year = @year THEN Codeline END) AS Codeline1,
    MAX(CASE WHEN month = @month AND year = @year THEN Type END) AS Type1,
    MAX(CASE WHEN month = @month AND year = @year THEN "Sub Division" END) AS "Sub Division1",
    MAX(CASE WHEN month = @month AND year = @year THEN Day END) AS Day1,
    MAX(CASE WHEN month = @month AND year = @year THEN Month END) AS Month1,
    MAX(CASE WHEN month = @month AND year = @year THEN Year END) AS Year1,
    MAX(CASE WHEN month = @month AND year = @year THEN "Control Failures" END) AS "Control Failures1",
    MAX(CASE WHEN month = @month AND year = @year THEN "Control Point Down" END) AS "Control Point Down1",
    MAX(CASE WHEN month = @month AND year = @year THEN "Failures" END) AS "Failures1",

    MAX(CASE WHEN month = @month1 AND year = @year1 THEN "CP Name" END) AS ControlPointName2,
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN Codeline END) AS Codeline2,
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN Type END) AS Type2,
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Sub Division" END) AS "Sub Division2",
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN Day END) AS Day2,
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN Month END) AS Month2,
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN Year END) AS Year2,
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Control Failures" END) AS "Control Failures2",
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Control Point Down" END) AS "Control Point Down2",
    MAX(CASE WHEN month = @month1 AND year = @year1 THEN "Failures" END) AS "Failures2",

    MAX(CASE WHEN month = @month2 AND year = @year2 THEN "CP Name" END) AS ControlPointName3,
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN Codeline END) AS Codeline3,
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN Type END) AS Type3,
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Sub Division" END) AS "Sub Division3",
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN Day END) AS Day3,
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN Month END) AS Month3,
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN Year END) AS Year3,
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Control Failures" END) AS "Control Failures3",
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Control Point Down" END) AS "Control Point Down3",
    MAX(CASE WHEN month = @month2 AND year = @year2 THEN "Failures" END) AS "Failures3"
FROM (
SELECT T1.ControlPointName AS 'CP Name',
        T1.codeline AS Codeline,
        T1.Type AS Type,
        T1.SubName AS 'Sub Division',
        T1.Day As Day,
        T1.Month AS Month,
        T1.Year as Year,
        SUM(T1.ControlFailCount) AS 'Control Failures',
        SUM(T1.ControlPointStatusDownCount) AS 'Control Point Down',
        T1.FailureCount AS 'Failures'
        FROM(SELECT ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type, SubName, Day, Month, Year, ControlFailCount, ControlPointStatusDownCount, FailureCount
        
		FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
		WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and ( (month = @month and year = @year and day = 1) or (month = @month1 and year = @year1 and day = 15) or (month = @month2 and year = @year2 and day = @day2) ) and FailureCount < '500' and FailureCount > '99') AS T1
        GROUP BY T1.ControlPointName, T1.Codeline, T1.Type, T1.SubName, T1.Day, T1.Month, T1.Year, T1.FailureCount
) AS TAll

Thanks,

2
3

No. that returns individual rows for each queried item. I want;

CP Name|Codeline|Type|Sub Division|Day1|Month1|Year1|Control Failures1|Control Point Down1|Failures1|Day2|Month2|Year2|Control Failures2|Control Point Down2|Failures2|Day3|Month3|Year3|Control Failures3|Control Point Down3|Failures3|

All on one row.