Newbie here. I’m sure the answer is out there, but I don’t know what to look for…
See example table I am dealing with – I have multiple numbered items under the same encounter, where some of the items where tagged (Tag 1, Tag 2, etc.). All non-tagged item numbers are marked as paired, since they were on the same encounter as a tagged item. Each item number has a dollar amount returned and a corresponding HasReturn column.
| Encounter | ItemNum | AType | Returned | HasReturn |
|---|---|---|---|---|
| 12345 | 1 | Paired | $0.00 | No |
| 12345 | 2 | Paired | $0.00 | No |
| 12345 | 3 | Tag1 | $200.00 | Yes |
| 12345 | 4 | Paired | $0.00 | No |
| 12345 | 5 | Paired | $0.00 | No |
| 12345 | 6 | Tag2 | $0.00 | No |
| 12345 | 7 | Paired | $0.00 | No |
| 12345 | 8 | Paired | $159.49 | Yes |
| 12345 | 9 | Paired | $159.49 | Yes |
| 12345 | 10 | Paired | $0.00 | No |
| 12345 | 11 | Paired | $35.25 | Yes |
| 12345 | 12 | Paired | $0.00 | No |
| 12345 | 13 | Paired | $0.00 | No |
| 12345 | 14 | Paired | $26.44 | Yes |
| 12345 | 15 | Paired | $7.55 | Yes |
| 12345 | 16 | Paired | $0.00 | No |
| 12345 | 17 | Paired | $4,391.74 | Yes |
| 12345 | 18 | Paired | $0.00 | No |
I am trying to get to the following end result – where I am displaying all the rows with a positive return value, but I also need to add a ‘PrimaryTag’ column that shows all ATypes present on that Encounter (concatenated), regardless of whether than line had a return value above $0 or not.
| Encounter | ItemNum | AType | PrimaryTag | Returned |
|---|---|---|---|---|
| 12345 | 3 | Tag1 | Tag1, Tag2 | $200.00 |
| 12345 | 8 | Paired | Tag1, Tag2 | $159.49 |
| 12345 | 9 | Paired | Tag1, Tag2 | $159.49 |
| 12345 | 11 | Paired | Tag1, Tag2 | $35.25 |
| 12345 | 14 | Paired | Tag1, Tag2 | $26.44 |
| 12345 | 15 | Paired | Tag1, Tag2 | $7.55 |
| 12345 | 17 | Paired | Tag1, Tag2 | $4,391.74 |
Please help me figure out what ??? needs to be.
select Encounter, ItemNum, AType, ??? AS PrimaryTag, Returned from Table1
WHERE HasReturn = ‘Yes’
Using Oracle PL/SQL if it matters. Thank you.
Truey is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.