SQLite. How to merge two cases in just one column, from a SQL Case function?

Question

I'm using SQLite.

I have this query:

    SELECT Customer.contract_number,
       Customer.name,
       Customer.last,
       Customer.phone,
       Contrato.desc,

       CASE Contract.sinc WHEN 1 THEN 'Sincro' ELSE 'NO sincro' END Status1,
       CASE Customer.rev WHEN 1 THEN 'Rev' ELSE 'No rev' END Status2

  FROM Customer,
       Contract
 WHERE Customer.contract_number = Contract.contract_number
UNION ALL
SELECT HandCo.contract_number,
       HandCo.name,
       HandCo.last,
       HandCo.phone,
       HandCo.des,

       CASE HandCon.tradit WHEN 1 THEN 'Tradit' ELSE NULL END Status3

  FROM HandContract;

I want to merge the result from Status1 and result from Status2 in just one column. How I do that? Thank you.

I'm using a UNION for the query and that is the reason why I want to merge the Case Funtion.

Thank you for respond.

Is there any reason why you can't just concatenate the two `CASE` statements? — Tim Biegeleisen, Oct 21 '16 at 01:37
What do you mean with "merge"? Please provide some sample data and your desired output. — CL., Oct 21 '16 at 07:08

score 0 · Answer 1 · edited May 23 '17 at 12:06

0

You can concatenate strings with || in sqllite.

SELECT Customer.contract_number, Customer.name, Customer.last, Customer.phone, Contract.desc,

   CASE Contract.sinc WHEN 1 THEN 'Sincro' ELSE 'No sincro' END
   ||
   CASE Customer.rev WHEN 1 THEN 'Rev' ELSE 'No Rev' END Status
FROM 
    Customer, Contract;

Similar topic: How to concatenate strings with padding in sqlite

edited May 23 '17 at 12:06

Community

1
1

answered Oct 21 '16 at 01:38

arkki

104
6

Well, I'm a little bit newbie here, I'm not explained it well. This is my full query: – Octavio Santiesteban Oct 21 '16 at 01:56
SELECT Customer.contract_number, Customer.name, Customer.last, Customer.phone, Contrato.desc, CASE Contract.sinc WHEN 1 THEN 'Sincro' ELSE 'NO sincro' END Status1, CASE Customer.rev WHEN 1 THEN 'Rev' ELSE 'No rev' END Status2 FROM Customer, Contract WHERE Customer.contract_number = Contract.contract_number UNION ALL SELECT HandCo.contract_number, HandCo.name, HandCo.last, HandCo.phone, HandCo.des, CASE HandCon.tradit WHEN 1 THEN 'Tradit' ELSE NULL END Status3 FROM HandContract; – Octavio Santiesteban Oct 21 '16 at 01:58

score 0 · Answer 2 · answered Oct 21 '16 at 01:39

Perhaps with string concatenation?

SELECT cu.contract_number, cu.name, cu.last, cu.phone, co.desc,
       ((CASE co.sinc WHEN 1 THEN 'Sincro' ELSE 'No sincro' END) || '-' ||
        (CASE cu.rev WHEN 1 THEN 'Rev' ELSE 'No Rev' END)
       ) as Status
FROM Customer cu JOIN
     Contract co
     ON co.?? = cu.??;

Note: I removed the , from the FROM clause. I really doubt that is the intention of this query. You need to put the proper JOIN condition in the ON clause, replacing the ?? with the appropriate column names.

SQLite. How to merge two cases in just one column, from a SQL Case function?

2 Answers2