SQLTeam.com | Weblogs | Forums

How togetparentchildstring in sql server

hi i have one doubt in sql server.
how to convert json format using multiple tables.

empid | city|state|country|doj
1 | bang|KA |Ind |2018-08-03
2 | chen|Tn |Ind |2018-02-04

empdid |addressline |addresscode
1 | street1 |street1
1 | street2 |taluka
1 | street2 |district
2 | street1 |street1
2 | street3 |district
empdid |prooftype
1 |aadhar
2 |voterid

based on above data i want output like below .

empid |city |doj |empjson
1 |bang |2018-08-03 |{"city":"bang","state":"ka","country":"ind","addreslineinfo":{"street1":"stree1","taluka":"street2","district":street2"},"prooftype":"aadhar"}
1 |chen |2018-02-04 |{"city":"bang","state":"ka","country":"ind","addreslineinfo":{"street1":"stree1","district":street3"},"prooftype":"voterid"}

I tried like below :
select empid,city,doj,(select city,state,country, addresscode,addressline,prooftype for json path ,include_null_values,without_array_wrapper)empjson

from emp_addr a inner join emp_addressline b on a.empid=b.empid
inner join emp_proof c on a.empid=c.empid

above query not given expected result ,could you please tell me how to write a query to achive this task in sql server .

You can try Concat:

select a.empid,city,doj,
	Concat((select city,state,country for JSON path),
				(select addresscode,addressline,prooftype for json path, Root('addreslineinfo') ,include_null_values)
from #emp_addr a 
inner join #emp_addressline b on a.empid=b.empid
inner join #emp_proof c on a.empid=c.empid