Click here to Skip to main content
14,691,709 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
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

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
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......
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, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900