Convert rows into column in SQL server

Question

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 20-May-18 15:21pm

sudhakarthikeyan

Updated 21-May-18 3:05am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Arunprasath Natarajan · Answer 1 · 2018-05-21T03:06:00

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;