How to join multiple table (getting mutiple values like array) using single ID?

muhammed arif 1 Reputation point
2021-07-09T10:19:19.233+00:00

How to get multiple values for same ID in row by row for a join multiple table using single ID. Here is sample,
DB Postgresql.
Table 1 :emp_tbl

ID   dep_id  name             dept. 

1    10001   name1            xxxx  
2    10001   name2            yyyy          
3    10002   name3            zzzz
4    10003   name4            zzzz
5    10004   name5            ssss

Table 2: sal_tbl

       ID   emp_id         sal.

        1    10001        10000
        2    10002        20000
        3    10003        30000
        4    10004        40000

Need to join two tables using query, and get below response

Response:

   ID     emp_id   name      dept.   sal.

    1      10001    name1     xxxx    10000
                    name2     yyyy     
    2      10002    name3     zzzz    20000
    3      10003    name4     zzzz    30000
    4      10004    name5     ssss    40000
Azure Database for PostgreSQL
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Viorel 111.5K Reputation points
    2021-07-09T11:10:02.937+00:00

    Try something like this:

    ; with Q as
    (
        select *, 
        row_number() over (partition by dep_id order by ID ) n
        from emp_tbl
    )
    select
        iif(n=1, cast(s.ID as varchar(max)), '') ID,
        iif(n=1, cast(Q.dep_id as varchar(max)), '') emp_id,
        Q.[name],
        Q.[dept.],
        iif(n=1, cast(s.[sal.] as varchar(max)), '') [sal.]
    from Q
    inner join sal_tbl s on s.emp_id = Q.dep_id
    order by Q.ID, n