combined 2cols into 1 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

combined 2cols into 1

Author

Topic

mkool
Starting Member

25 Posts

Posted - 2008-03-24 : 13:31:29

create view vwchannel

as

select distinct s2.soptype,

s2.sopnumbe , --internet orders/information center orders

s2.custnmbr , --amazon.com orders

s3,itemnmbr, sum(s3.quantity) from salestab s2

left outer join (select distinct soptype,sopnumbe,itemnmbr,quantity from salesdisttab) s3
on s2.soptype = s3.soptype and s2.sopnumbe = s3.sopnumbe

where (s2.CUSTNMBR LIKE 'amazon%')

AND ((s2.SOPNUMBE LIKE 'net%') OR (s2.SOPNUMBE LIKE 'inv%'))

AND s2.soptype = 3

group by s3.itemnmbr,s2.sopnumbe,s2.custnmbr,s2.soptype

go

i m getting 70 rows in output which is correct but..
i have to combine sopnumbe and custnmbr into one column name as channel

how can i do that?

i tried like:

case when (s2.SOPNUMBE LIKE 'net%' OR s2.SOPNUMBE LIKE 'inv%') then s2.sopnumbe
when s2.CUSTNMBR LIKE 'amazon%' then s2.custnmbr
end as channel

but i m getting 0rows affected in ouput.. instead of 70rows...

can anyone help me?
thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-03-24 : 13:51:42

where do you want to combine columns? in WHERE condition?