How to transpose row to column

Question

0.00/5 (No votes)

See more:

SOHeaderId	SODetailId
11004	        100030
11004	        100031
11004	        100032
11004	        100033
11004	        100034
11004	        100035
11004	        100036
11005           100037
11005           100038
11005           100039
11005           100040
11006           100041
11006           100042
11006           100058
11006           100078
wanted it as

SOHeaderId     SODetailId
11004           100030,100031,100032,100033,100034,100035,100036
11005           100037,100038,100039,100040
11006           100041,100042,100058,100078

WITH THE help of SQL Query only

I have already tried on google, but not able to solve it.

Posted 24-Mar-14 0:50am

StackQ

Add a Solution

Comments

Andrius Leonavicius 24-Mar-14 7:06am

Hi,

What query have you tried so far?

StackQ 24-Mar-14 7:18am

http://stackoverflow.com/questions/20119162/transpose-rows-into-columns-in-sql-server-2008-r2
http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql
http://stackoverflow.com/questions/18618951/sql-transpose-rows-to-columns

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)

Andrius Leonavicius · Answer 1 · 2014-03-24T02:33:00

You don't need to transpose rows into columns. You need to concatenate [SODetailId] column values by distinct [SOHeaderId] into a string separated by commas. One of the simplest ways to do this is to use XML PATH('').

Your query should be something like this:

SQL

SELECT DISTINCT [SOHeaderId],
                STUFF((SELECT ',' + CAST([SODetailId] AS VARCHAR(10))
                       FROM   [TableName] t1
                       WHERE  t1.[SOHeaderId] = t2.[SOHeaderId]
                       FOR XML PATH('')), 1, 1, '')
FROM   [TableName] t2

I am assuming that the data type of [SODetailId] is INT, so I am casting it to VARCHAR(10).

References:
1. STUFF AND FOR XML PATH for String Concatenation[^]
2. Using FOR XML PATH and STUFF to CONCATENATE[^]