How to convert row to column in sql server

Question

0.00/5 (No votes)

See more:

Hi,

I want to convert row to column in sql server, I tried pivot/unpivot concept, could not get the result.

I have a table with two columns and records as below.

35 Ball
35 Cat
35 Apple
35 Dog
36 Cricket
36 Football
36 Hockey

I want to convert it into two rows, one row for 35. and 2nd row for 36 a below

35 Ball Cat Apple Dog
36 Cricket FootBall Hockey null

Posted 17-Jan-15 4:17am

Upadhyay Praveen

Add a Solution

2 solutions

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)

OriginalGriff · Answer 1 · 2015-01-17T04:52:00

Solution 2

It's pretty simple:

SQL

SELECT
     YourNumberColumnName, 
     STUFF(
         (SELECT DISTINCT ' ' + YourItemsColumnName 
          FROM MyTable
          WHERE YourNumberColumnName = t.YourNumberColumnName
          FOR XML PATH (''))
          , 1, 1, '')  AS ItemsList
FROM MyTable AS t
GROUP BY YourNumberColumnName

[edit]Noticed you didn't want commas, replaced with spaces - OriginalGriff[/edit]

Posted 17-Jan-15 4:52am

OriginalGriff

Updated 17-Jan-15 4:55am

v2

Comments

Upadhyay Praveen 17-Jan-15 16:16pm

Its coming into two column. I am expecting 5 columns.

35 Apple Ball Cat Dog
36 Cricket Football Hockey null

**Each word i want in separate column. but, except 35,36 rest all are in one column.

Wendelius · Answer 2 · 2015-01-17T04:45:00

The data you have is insufficient for transforming the rows to columns. One big question is, why Ball is before Cat or Apple. If you would have some kind of ordering column then this could be done. For example a third column describing an ordinal within the value in first column (35 or 26).

For example consider following scenario

SQL

create table t2 (
   col1 int,
   col2 varchar(200),
   col3 int -- ordinal
);

insert into t2 values (35 ,'Ball'     ,2);
insert into t2 values (35 ,'Cat'      ,1);
insert into t2 values (35 ,'Apple'    ,4);
insert into t2 values (35 ,'Dog'      ,3);
insert into t2 values (36 ,'Cricket'  ,1);
insert into t2 values (36 ,'Football' ,4);
insert into t2 values (36 ,'Hockey'   ,2);

select a.col1,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 1) AS C1,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 2) AS C2,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 3) AS C3,
       (select col2 from t2 b where b.col1 = a.col1 and b.col3 = 4) AS C4
from (select distinct col1
      from t2) a

The result for the select would be:

col1  C1       C2      C3    C4
----  -------  ------  ----  -------
35    Cat      Ball    Dog   Apple
36    Cricket  Hockey  NULL  Football