convert rows into column in sql server

Question

Query as follows

SELECT A._id,B.firstname + '' '' + B.lastname + '' '' + B.surname
as name,A.details
FROM dbo.tbl_farmer_farmdetails_' + @originname + ' A

INNER JOIN dbo.tbl_farmerregistration_' + @originname + ' B ON B.farmerctscode = A.farmer_id
WHERE B.farmerseason = '2018'

When i run the above code i get output as follows

id Name Details

1 Test1 "Product1":"Cilo","Product2":"Zilo"

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo

from my above sql query what changes i have to made to get the above output

Sankara Krishnan Venugopal · Answer

Hi Narasiman, Find the below details, 1. Create temp table CREATE TABLE #TempTable( ID int, Name varchar(100), value1 varchar(100), valu2 varchar(100)) 2. To insert records in "Temp table" IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL DROP Table #TempTable INSERT INTO #TempTable (ID, name, value1, value2) Spilit the records using this way, https://forums.asp.net/t/1965445.aspx?How+to+split+a+comma+separated+value+to+columns+in+sql+server Example: INSERT INTO #TempTable values(1, 'Test1', 'Cilo', 'Zilo') 3. Select temp table: SELECT id, name, value1, value2 FROM #TempTable I hope this helps.

Narasiman nar · Answer

ok. how to do it please help me. i am new to temptable

Sankara Krishnan Venugopal · Answer

Hi, You can use the following way to resolve your issue, 1.Create the temp table. 2. Use the loop to spilt the record based on : colon in "detail" column. 3.insert the each record into table 4. Use the select statement to fetch all records from temp table. I hope this helps.

convert rows into column in sql server

Answers (3)