Click here to Skip to main content
14,733,542 members
Please Sign up or sign in to vote.
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

It's pretty simple:
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]
   
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.
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
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
   
Comments
Upadhyay Praveen 18-Jan-15 3:22am
   
Thanks, But 3rd column i can not add. This table has huge record.
Wendelius 18-Jan-15 9:45am
   
In that case I guess the best option is to define what is the maximum amount of columns to be generated (in your example it was 4) and then select the items (Ball, Apple, Dog etc) based on their alphabetical order...

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