Morgs Morgs - 6 months ago 38
SQL Question

SQL Server - T-SQL UNION ALL + PIVOT

Is there a way that I can PIVOT the following SQL Query so I can retain the 3 different column names in each individual query as 1 row. At the moment it gives me the results as 3 rows under 1 column name from the first query.

SELECT COUNT(sdo.Id) AS [TotalNew] FROM [SubscriberDebitOrder] sdo WHERE sdo.[CampaignId]=@p0 AND sdo.[CreatedOn] >=
(
SELECT CAST(MAX(sdo.[CreatedOn]) AS DATE) FROM [SubscriberDebitOrder] sdo WHERE sdo.[CampaignId]=@p0
)
UNION ALL
SELECT COUNT(sdo.Id) AS [TotalNewSuccess] FROM [SubscriberDebitOrder] sdo
INNER JOIN [Collections] c ON c.[DebitOrderId]=sdo.[Id]
WHERE sdo.[CampaignId]=@p0 AND sdo.[CreatedOn] >=
(
SELECT CAST(MAX(sdo.[CreatedOn]) AS DATE) FROM [SubscriberDebitOrder] sdo WHERE sdo.[CampaignId]=@p0
) AND c.TxnStatus='S'
UNION ALL
SELECT COUNT(sdo.Id) AS [TotalNewFailed] FROM [SubscriberDebitOrder] sdo
INNER JOIN [Collections] c ON c.[DebitOrderId]=sdo.[Id]
WHERE sdo.[CampaignId]=@p0 AND sdo.[CreatedOn] >=
(
SELECT CAST(MAX(sdo.[CreatedOn]) AS DATE) FROM [SubscriberDebitOrder] sdo WHERE sdo.[CampaignId]=@p0
) AND c.TxnStatus='U'


Thanks in advance.

vkp vkp
Answer

You can simplify the query to be the one below.

SELECT 
 COUNT(*) AS [TotalNew]
,sum(case when c.TxnStatus='S' then 1 else 0 end) as [TotalNewSuccess]
,sum(case when c.TxnStatus='U' then 1 else 0 end) as [TotalNewFailed]
FROM [SubscriberDebitOrder] sdo 
INNER JOIN [Collections] c ON c.[DebitOrderId]=sdo.[Id]
WHERE sdo.[CampaignId]=@p0 
AND sdo.[CreatedOn] >= 
(
 SELECT CAST(MAX([CreatedOn]) AS DATE) 
 FROM [SubscriberDebitOrder]
 WHERE [CampaignId] = @p0
)