I need query for three table with join

Tamil Selvan

6y875

Dear all,

Kindly find below table records for sample data.

An I need result as below:

Please find below image for your ref.

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Kiran Mohanty6y ago
You can user PIVOT as below.
select * from (
select * from (
select t1.dept , t1.CodesCount as 'Todatl tests', isnull( t3.value1, 0) as 'value1',  t3.TestDate
from table1 t1
left outer join table2 t2 on t2.Dept  =  t1.Dept
left outer join table3  t3 on t2.UserName = t3.UserName
) t
pivot (
sum(value1)
for TestDate in ( [2020-06-10],[2020-06-09], [2020-05-10],[2020-07-10] )
) as pivot_table
) result
order by dept;
Mageshwaran R6y ago
Hi Tamil Selvan,
please refer this link:
https://www.geeksforgeeks.org/joining-three-tables-sql/

This is for Query:

SELECT B.dept, A.req_date1, COUNT(A.req_no) as [Total] FROM TABLE-03 A left join TABLE-02 B on A.user_id=B.user_id
where A.status NOT IN('D','R') GROUP BY b.dept,A.req_date1 ORDER BY B.dept desc

Result as Below:

Abhishek Chadha6y ago
you can use CTE to get the dynamic column or
if you have a query you made for this you can post here i can made changes in that
Tamil Selvan6y ago
I need dynamic data for column field and row field also.

Like date will change day by day. So row filed is date, column field is dept.
Abhishek Chadha6y ago
You can use the pivot to get the expected output.

for reference - https://www.c-sharpcorner.com/UploadFile/8911c4/pivot-examples-in-sql-server/