Azure Database for PostgreSQL
An Azure managed PostgreSQL database service for app development and deployment.
513 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
For new expectation, check this query:
Thanks, it's working for me.
I got this error when i run in SQL Editor, SQL Error [42601]ERROR: syntax error at or near "case" Position: 1
Show the tried query, if you modified the example.
It's working, some space issues in the query.
Is it possible for add multiple values 5 table same like this
If you have several tables that looks like emp_tbl, then you can use a UNION ALL to combine the rows. If your case is different, then give more details. (You can also start a new question with enough details, because the comment size seems limited here).
Ok, I asked new question for this https://learn.microsoft.com/en-us/answers/questions/474144/how-to-display-mutiple-values-from-multiple-table.html
Can You share UNION ALL sample query for several tables join in a Single id(emp_id). Every tables have a emp_id for join.
Sign in to comment