SQL Server : show campaigns that a phone number appears in

Question

I'm trying to get a report done within SQL Server that shows each sales campaign that a phone number appears in.

The desired result:

Phonenumber | Area Code | Campaign 1 | Campaign 2 (if applicable) etc...

I have the following code:

with cids as 
(
    select distinct 
        a.outbound_cid as [CID],
        a.areacode as [AreaCode],
        a.campaign_id as [Campaign]
    from 
        vicislave...vicidial_campaign_cid_areacodes a
    join 
        LDS_SALES_CAMPAIGNS b on a.campaign_id = b.campaign_id
)
select * 
from cids
order by cid

Which returns this result (data example):

In this example, I would get

2012444340 201 ZEPTR  ZEACC ZBEASTC ZEPRR InBnd2 ZEPSC ZEJCC ZEJSC ZEPCC ZEASC

I've not worked with cross-tabbing before, but it appears to be something that may work, but I have not been able to make it work like I can. Obviously, I can do the MIN and MAX of each of these, but I don't know of a way to get the data into the one row, even using things like Excel, without manually doing it, which with 1110 results with these, I don't really have time to do it.

Not to ape the help that I got from below, here is the exact code that worked:

select distinct 
    a.outbound_cid as [CID],
    a.areacode as [AreaCode],
    a.campaign_id as [Campaign]
into 
    #CIDs
from 
    vicislave...vicidial_campaign_cid_areacodes a
join 
    LDS_SALES_CAMPAIGNS b on a.campaign_id = b.campaign_id


select distinct 
    CID,
    areacode,
    Campaign = stuff((select ', ' + c2.campaign 
                      from #CIDs c2 
                      where c2.CID = c.CID 
                      for xml path(''), type).value('.', 'nvarchar(MAX)'), 1, 1, '')
from 
    #cids c

drop table #CIDs

[This](https://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field) should help. You basically have to use `GROUP_CONCAT` — AzaDee, Jul 24 '18 at 14:31

score 1 · Accepted Answer · answered Jul 24 '18 at 14:54

If you use SQL server, you can use STUFF function. You can follow my sample. Hope to help, my friend!

Create table Test(CID nvarchar(10)
                , AreaCode nvarchar(10)
                , Campaign nvarchar(10)) 
Insert into Test(CID, AreaCode, Campaign) 
            values('2012', '201', 'ABC')
Insert into Test(CID, AreaCode, Campaign) 
            values('2012', '201', 'XYZ')
Insert into Test(CID, AreaCode, Campaign) 
            values('2014', '201', 'aaa')
Insert into Test(CID, AreaCode, Campaign) 
            values('2014', '201', 'bbb')

-----------
SELECT distinct CID      
    , Campaign = STUFF((
          SELECT ',' + t2.Campaign
          FROM Test t2
          WHERE t.CID = t2.CID
          FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Test t
ORDER BY t.CID

This worked with the small change that I added. Thank you! – dragos_kai Jul 24 '18 at 15:07 — dragos_kai, Jul 24 '18 at 15:07
@dragos_kai: Yeah! You're so great :)) – Tomato32 Jul 24 '18 at 15:10 — Tomato32, Jul 24 '18 at 15:10

score 0 · Answer 2 · answered Jul 24 '18 at 15:02

To obtain a single record from any table "TABLE" with a field "field" you have to do

SELECT ';'+rtrim(field)
FROM TABLE FOR XML PATH('')

Tha will return a single record with all the fields concatenated with ";"

in your case you should do :

    select ';'+campaign from cids FOR XML PATH('') 
    order by cid

of course you can even group by CID Column if you need...

SELECT C.CID, (
    select ';'+campaign from cids
    where cids.CID=C.CID
    FOR XML PATH(''))
from CIDS C
group by CID

that would do the trick

SQL Server : show campaigns that a phone number appears in

2 Answers2