14,699,886 members
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

## 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
meranaamshahul 11-Jun-14 7:34am

did it support in Sql server 2005
Nirav Prabtani 11-Jun-14 7:41am

try updated solution 2

## 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

v2
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

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......

## 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',',')