In sql query how to get my excepted output

Query as follows

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(A.farm_detailsdata,'{',''),'}',''),'"',''),'',' '),',',' ') as farmdetailsdata,

A.farmer_season,A.farmvisitno,'-' as farmpicture

FROM dbo.tbl_farmer_farmdetails A

INNER JOIN dbo.tbl_farmerregistration B ON B.farmerctscode = A.farmer_id

WHERE B.farmerseason = '2018'

When i execute the above query shows output as follows

farmdetailsdata Season farmvisitno farmpicture (Column Name)

product1:A product2:B 2018 1 - (Values)

from the above i want prodcut1 in each column A in each column product 2 in each column B in each column

From the above i want excepted output as follows

product1 Product2 Season farmvisitno farmpicture (Column Name)

A B 2018 1 - (Values)

in the above sql query what changes i have to made to get excepted output.

please help me.

2 Replies

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

Nitin Sontakke8y ago
@Rajendra,

Just for your information, Narasiman is using Microsoft SQL Server 2017 and the value in farmdetailsdata is in JSON format.

From SQL Server 2016 JSON parsing is natively supported. I has suggested him to have a look and try. I never got the update, unfortunately.

Furthermore, the way the data is organized one has to deal with JSON data separately and then apply join with non-JSON data to get final output. Or use OUTER APPLY operator.
+1
rajendra singh8y ago
Please share script so i can execute on my local machine.
0