CT Healthcare CT Healthcare - 3 months ago 19
SQL Question

Query column twice with union all

I am trying to do a union all to produce data for reporting, below is what I have so far, it shows all the data I want but I cannot get the data in the same rows, it produces the two rows at a minimum with null in the corresponding column. I am hoping that there is a way so that I can get the data in the same row?

select account, campaign, sale, date
from
(
SELECT CHACCOUNTNO as account, CONTSUPREF as campaign,null as sale, ONDATE as date
FROM dbo.MKTDW
WHERE (RESULTCODE = 'D01') and CONTACT IN ('Campaign ID')
group by CHACCOUNTNO, CONTSUPREF, ONDATE

UNION ALL

SELECT CHACCOUNTNO as account, null as campaign, CONTSUPREF as sale, ONDATE as date
FROM dbo.MKTDW
WHERE (RESULTCODE = 'D01') and CONTACT IN ('Order')
group by CHACCOUNTNO, CONTSUPREF, ONDATE
)account
group by account,campaign,sale,date
order by account


Current Result:

account campaign sale date
A2043056003(2IJUMI M NULL N177618 2014-07-21 00:00:00.000
A2043056003(2IJUMI M LT08704 NULL 2014-07-21 00:00:00.000


Expected result:

A2043056003(2IJUMI M) LT08704 N177618 2014-07-21 00:00:00.000

Answer

The answer will be straightforward. If you want something shown in horizontal way, use JOINs instead of SET operators. The code is listed below, tested, works perfect in SSMS. :)

--create table structure
create table dbo.MKTDW 
(   CHACCOUNTNO varchar(100), 
    CONTSUPREF varchar(10), 
    RESULTCODE varchar(10), 
    CONTACT varchar(50), 
    ONDATE datetime)
go

--insert sample data
insert dbo.MKTDW 
select 'A2043056003(2IJUMI M)', 'N177618', 'D01', 'Order', '2014-07-21 00:00:00.000'
union all
select 'A2043056003(2IJUMI M)', 'LT08704', 'D01', 'Campaign ID', '2014-07-21 00:00:00.000'
union all
select 'B2043056003(2IJUMI M)', 'M000000', 'D01', 'Order', '2014-07-21 00:00:00.000'
union all
select 'B2043056003(2IJUMI M)', 'X111111', 'D01', 'Campaign ID', '2014-07-21 00:00:00.000'


--below is the solution
select  a.CHACCOUNTNO as account, 
        a.CONTSUPREF as campaign, 
        b.CONTSUPREF as sale, 
        a.ondate as date        
from dbo.MKTDW as a
join dbo.MKTDW as b
on a.CHACCOUNTNO = b.CHACCOUNTNO
where   a.CONTACT = 'campaign id' 
        and b.CONTACT = 'order' 
        and a.RESULTCODE = 'D01' 
        and b.RESULTCODE = 'D01'

RESULT:

enter image description here