CODEWITHSUNDEEP
sqlsql-server-2008
Gurru
Gurru

Reputation: 2103

SQL Server 2008 r2 how to split column values to multiple rows

How to split column values to raw values e.g:

Col1            col2
1               james
1               gold
1               [email protected]
2               john
2               def
2               [email protected]

col1 values keep on changing to thousands.

results:

col1            col2      col3      col4
1               james     gold      [email protected] 
2               john      def       [email protected]

Upvotes: 1

Views: 1268

Answers (1)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this:

This will not guarantee the order of the values among the columns, as the table doesnt have a column to show the order of the columns..If you have a column, like that, you could change select 0 with that column

with cte as(
select *,ROW_NUMBER() 
         over(partition by Col1 order by (select 0))as rn from Table1)  
select * from cte
pivot(
   Max(COL2) FOR rn IN ([1],[2],[3] )  
   )P


SQL fiddle demo

Upvotes: 3

Related Questions