convert rows into column in sql server

8y737Learn iOS Programming

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

3 Replies

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

Sankara Krishnan Venugopal8y ago
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.
0
8y ago
ok. how to do it please help me.

i am new to temptable
0
Sankara Krishnan Venugopal8y ago
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.
0