Post reply

CONCAT and CASE WHEN combined

  • December 31, 2020 at 9:04 pm

    #3826010

    Hi everyone,

    I'm trying to create a query that concatenates the directions field that were involved in each transaction_id.

    I've tried to do it by creating a flag table with multiple case whens, and then querying on it, but as you can see in the image below, I'm having trouble to get a multiple 1's flag in one single row ID.

    Any ideas on how to do it?

    Here are the code and the tables I'm using:

        SELECT 
        t2.trans_id
        CASE WHEN t2.dept_nbr IN (SELECT dept_nbr FROM t3 with WHERE dir = 'dir1')THEN 1 ELSE 0 END AS flag_dir1,
        CASE WHEN t2.dept_nbr IN (SELECT dept_nbr FROM t3 with WHERE dir = 'dir2')THEN 1 ELSE 0 END AS flag_dir2
        FROM 
        t2

    Tables

    Thanks in advance and happy new year!

  • December 31, 2020 at 9:43 pm

    #3826024 

     SELECT 
    t2.trans_id
    MAX(CASE WHEN t3.dir = 'dir1' THEN t3.dir ELSE Null END ) AS flag_dir1,
    MAX(CASE WHEN t3.dir = 'dir2' THEN t3.dir ELSE Null END ) AS flag_dir2
    FROM t2 Left join t3 on t3.dept_nbr =t2.dept_nbr

    GROUP BY t2.trans_id

    _____________
    Code for TallyGenerator

  • December 31, 2020 at 11:24 pm

    #3826044

    Thank you! This worked.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply