SQL Concatenate multiple rows after Case statement inside LEFT JOIN statement-CodePudding

I have separate tables

Table1:
| AppID        | Desc | 
|--------------|------|
| 1            | App1 |
| 2            | App2 |

Table2:
| CollateralID | Type  | AppID |
|--------------|-------|-------|
| 1            | Home  | 1     |
| 2            | Condo | 1     |
| 3            | Condo | 2     |

Table3:
| TitleID | Address1 | Address2 | Address3 | CollateralID  |
|---------|----------|----------|----------|---------------|
| 1       | Add1a    | Add2a    | Add3a    |   1           |
| 2       | Add1b    | Add2b    | Null     |   2           |
| 3       | Add1c    | Add2c    | Null     |   3           |


Desired Results:
| AppID  | Desc | Type        | Complete Address |
|--------|------|-------------|------------------|
| 1      | App1 | Home; Condo | Add1a   Add2a   Add3a; Add1b   Add2b |
| 2      | App2 | Condo       | Add1c   Add2c |

So far, this is only what I got

SELECT  app.AppID                       AS AppID,
        app.Desc                        AS Desc,
        details.type                    AS Type,
        details.compadd                 AS "Complete Address"

FROM    Table1    app
LEFT JOIN (SELECT   tab2.AppID,
                    tab2.Type, 
                    
                    CASE WHEN tab2.Type = 'HOME' 
                         THEN tab3.Address1 || ' ' || tab3.Address2 || ' ' || tab3.Address3

                         WHEN tab2.Type = 'CONDO' 
                         THEN tab3.Address1 || ' ' || tab3.Address2 

                    END AS compadd      
            
            FROM  Table2 tab2 
            JOIN  Table3 tab3 ON tab2.CollateralID = tab3.CollateralID
            ) details ON app.AppdID = tab2.AppID

WHAT I AM MISSING and cannot understand how to apply:

How can I concatenate the Type and the Complete Address for an AppID as shown above?

Appreciate the help in advanced. Let me know if it isn't clear. Thank you!

CodePudding user response：

First, you don't need a subquery to achieve what you need. Second, use upper() function to make sure you have the exact match

select app.AppID as AppID
    , app.Desc as "Desc"
    , tab2.type as "Type"
    , case 
        when upper(tab2.Type) = 'HOME'  
            then tab3.Address1 || ' ' ||  tab3.Address2 ||  ' ' ||  tab3.Address3
        when upper(tab2.Type) = 'CONDO'
            then tab3.Address1 ||  ' ' ||  tab3.Address2 end as "Complete Address"
from Table1 app
left join Table2 tab2 on tab2.AppID = app.AppID
left join Table3 tab3 on tab3.CollateralID = tab2.CollateralID

CodePudding user response：

You need to use GROUP BY clause with STRING_AGG function. like below

SELECT  app.AppID AS AppID, app.Desc AS Desc, details.type AS Type, details.compadd AS "Complete Address"
FROM    Table1    app
LEFT JOIN 
(
SELECT   tab2.AppID, STRING_AGG(tab2.Type,';') Type, 
STRING_AGG(CASE WHEN tab2.Type = 'HOME' 
THEN tab3.Address1 || ' ' || tab3.Address2 || ' ' || tab3.Address3
WHEN tab2.Type = 'CONDO' 
THEN tab3.Address1 || ' ' || tab3.Address2 
END,';') AS compadd      
FROM  Table2 tab2 
JOIN  Table3 tab3 ON tab2.CollateralID = tab3.CollateralID
Group By tab2.AppID
) details ON app.AppdID = details.AppID