December 31, 2020 at 9:04 pm
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
Thanks in advance and happy new year!
December 31, 2020 at 9:43 pm
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
Thank you! This worked.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy