Union & Transpose Query

[mihir777]

Hello,

i am using this query

SELECT count(CAMPAIGN_ID) as CAMPAIGN_ID
FROM sp_sms_view
Where CAMPAIGN_ID in (3600,3603)
Group By Campaign_id
union

SELECT count(DELIVERY_STATUS) as DELIVERY_STATUS
FROM sp_sms_view
Where CAMPAIGN_ID in (3600,3603) and DELIVERY_STATUS = 'Delivered'
Group By Campaign_id,DELIVERY_STATUS
union

SELECT count(DELIVERY_STATUS) as CLICKED_STATUS
FROM sp_sms_view
Where CAMPAIGN_ID in (3600,3603) and CLICKED_STATUS = 'Clicked'
Group By Campaign_id,DELIVERY_STATUS,CLICKED_STATUS

Output Received


555


24659


535


23983


118



7207

Required Output



Campaign Id
Count Campaign ID
Count Delivery Status
Count Clicked Status


3600
555
535
118


3603
24659
23983
7207

[RET]

@mihir777 (https://www.excelguru.ca/forums/member.php?60753-mihir777) ,

The best option would be to use PowerQuery. Anyway, if you need to use SQL, you can get the result from the query in the for of:



Campaing ID
Data
Result


3600
Count Campaign ID
555


3603
Count Campaign ID
24659


3600
Count Delivery Status
535


3603
Count Delivery Status
23983


3600
Count Clicked Status
118


3603
Count Clicked Status
7207



and then create a Pivot Table

Try with

SELECT CAMPAIGN_ID, ‘Count CAMPAIGN Id’, count(CAMPAIGN_ID) as Result
FROM sp_sms_view
Where CAMPAIGN_ID in (3600,3603)
Group By Result
union

SELECT CAMPAIGN_ID, ‘Count Delivery status, count(DELIVERY_STATUS) as Result
FROM sp_sms_view
Where CAMPAIGN_ID in (3600,3603) and DELIVERY_STATUS = 'Delivered'
Group By Result
union

SELECT CAMPAIGN_ID, ‘Count Clicked status, count(DELIVERY_STATUS) as Result
FROM sp_sms_view
Where CAMPAIGN_ID in (3600,3603) and CLICKED_STATUS = 'Clicked'
Group By Result