Results 1 to 2 of 2

Thread: Union & Transpose Query

  1. #1
    Acolyte mihir777's Avatar
    Join Date
    May 2016
    Posts
    23
    Articles
    0
    Excel Version
    2016

    Union & Transpose Query



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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


  2. #2
    Acolyte RET's Avatar
    Join Date
    Nov 2020
    Location
    Spain
    Posts
    24
    Articles
    0
    Excel Version
    2019
    @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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •