Click here to Skip to main content
14,733,037 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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 run the above code 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


i think below way to do it

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


how to do the above step to get my below output as follows

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo

What I have tried:

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 run the above code 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


i think below way to do it

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


how to do the above step to get my below output as follows

i want the output as follows

id Name Product1 Product2

1 Test1 Cilo Zilo
Posted
Updated 21-May-18 4:05am
Comments
Richard Deeming 21-May-18 7:44am
   
REPOST
Exactly the same question that you posted from your sock-puppet account:
https://www.codeproject.com/Questions/1244996/Rows-into-columns-in-SQL-server[^]

1 solution

select Firstname, Amount, PostalCode, LastName, AccountNumber
from
(
  select value, columnname
  from yourtable
) d
pivot
(
  max(value)
  for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber)
) piv;

If you have an unknown number of columnnames that you want to transpose, then you can use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(ColumnName) 
                    from yourtable
                    group by ColumnName, id
                    order by id
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT ' + @cols + N' from 
             (
                select value, ColumnName
                from yourtable
            ) x
            pivot 
            (
                max(value)
                for ColumnName in (' + @cols + N')
            ) p '

exec sp_executesql @query;
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900