Click here to Skip to main content
14,270,386 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have a column value in a table like 1,2,3. i want to split it into three rows in sql server,
if anyone knows Let me know
Posted
Rate this:
Please Sign up or sign in to vote.

Solution 2

try this.. unpivot for that in sql :)

SELECT * FROM (
SELECT [column1] ,[column2] ,[column3] FROM [dbo].[table1]) T
UNPIVOT ( Value FOR DataValue IN ( [column1],[column2] ,[column3] ))P
   
v2
Comments
meranaamshahul 11-Jun-14 7:34am
   
did it support in Sql server 2005
Nirav Prabtani 11-Jun-14 7:41am
   
try updated solution 2
Rate this:
Please Sign up or sign in to vote.

Solution 3

Hi,

You can do it like this:
DECLARE @columnValue VARCHAR(MAX) = '1,2,3';
DECLARE @xmlList XML = CAST('<i>' + REPLACE(@columnValue, ',', '</i><i>') + '</i>' AS XML);

SELECT x.i.value('.', 'VARCHAR(MAX)') AS [Values]
FROM @xmlList.nodes('i') x(i);

Result:
Values
1
2
3

I would recommend you to read this article: Delimited String Tennis Anyone?[^]
   
v2
Comments
meranaamshahul 11-Jun-14 7:56am
   
how can i give a select Query(select * from table) in stead of '@columnValue '('1,2,3')
Andrius Leonavicius 11-Jun-14 8:43am
   
Splitting a column value (values stored in one column => rows) and transposing columns to rows (columns => rows) are two different things. You asked how to split a column value (like 1,2,3) and I gave a solution for that. Nirav Prabtani posted a solution for transposing columns to rows. So, what do you actually want to do?
meranaamshahul 11-Jun-14 9:02am
   
i have a column value like (1,2,3). i need to do split it 3 rows like 1 in first row ,2 in second row, 3 in third row,

select Notes from Notes id in ([fn_CSVToTable](select notes_id from Details where details_id=1))
Andrius Leonavicius 11-Jun-14 9:33am
   
I just noticed that my code was not HTML encoded after pasting here (some parts were missing). I'm sorry about that. It should work now.
Andrius Leonavicius 11-Jun-14 9:39am
   
Please copy updated code and replace '1,2,3' with your select.
kumari567 21-Jun-16 5:58am
   
how to split a column in many columns in sql
like 1:1:2:3:4 in col1->1 col2->1 col3->2......
Rate this:
Please Sign up or sign in to vote.

Solution 4

Try This,

CREATE FUNCTION [Split]
   (
       @String varchar(8000),
       @Delimiter char(1)
   )
   returns @temptable TABLE (items varchar(8000))
   as
   begin
       declare @idx int
       declare @slice varchar(8000)

       select @idx = 1
           if len(@String)<1 or @String is null  return

       while @idx!= 0
       begin
           set @idx = charindex(@Delimiter,@String)
           if @idx!=0
               set @slice = left(@String,@idx - 1)
           else
               set @slice = @String

           if(len(@slice)>0)
               insert into @temptable(Items) values(@slice)

           set @String = right(@String,len(@String) - @idx)
           if len(@String) = 0 break
       end
   return
   end


And

SELECT * from Split('1,2,3',',')



Hope this will help you.
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100