Union & Transpose Query

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

@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:


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