How to split the column and row values

Question

Using sql Server 2005

id value1 value2

001 200 300
002 200x300 400x200
003 300x200 500x600
004 100 200
.....

i want to split the value column into 2 column

If value1 row 200x300 then it should display value1 - 200 and value2 - 300
if value2 row 400x200 then it should display value1 - 400 and value2 - 200

Expected output

id value1 value2

001 200 300
002 200 300
002 400 200
003 300 200
003 500 600
004 100 200
....

How to do this.

Need SQL Query Help

@AshReva, please try to understand the question before posting duplicate, that question is totally different from this question. — Gopal, Apr 25 '13 at 07:51

score 0 · Answer 1 · answered Apr 25 '13 at 07:49

Try This

select * from 
(
select * from Table2 where CHARINDEX('x',value1)=0 and CHARINDEX('x',value2)=0
union all
select ID,
CASE WHEN CHARINDEX('x',value1)>0 then LEFT(value1,CHARINDEX('x',value1)-1) END as value1,
CASE WHEN CHARINDEX('x',value1)>0 then RIGHT(value1,CHARINDEX('x',value1)-1) else value2 end as value2
from Table2
where CHARINDEX('x',value1) > 0 
union all
select ID,
CASE WHEN CHARINDEX('x',value2)>0 then LEFT(value2,CHARINDEX('x',value2)-1) END as value1,
CASE WHEN CHARINDEX('x',value2)>0 then RIGHT(value2,CHARINDEX('x',value2)-1) else value2 end as value2
from Table2
where CHARINDEX('x',value2) > 0 
) a
order by 1

score 0 · Answer 2 · answered Apr 25 '13 at 07:50

Please try with this..

    --Create Table :
    Create Table #Temp
    (
    Id  Varchar(10),
    Value1  Varchar(10),
    Value2  Varchar(10)
    )

    --Insert Values :
    Insert into #Temp Values ('001','200','300')
    Insert into #Temp Values ('002','200x300','400x200')
    Insert into #Temp Values ('003','300x200','500x600')
    Insert into #Temp Values ('004','100','200')

    --View Data :
    Select * from  #Temp

    --Query  for Exepected OutPut :
    Select Id,Value1,Value2 from #Temp Where (  Value1 not Like '%x%'  and  Value2 not Like '%x%' )
    Union all
    Select  Id,
    SUBSTRING(Value1,1,CharIndex('x',Value1)-1) as Value1,
    SUBSTRING(Value1,CharIndex('x',Value1)+1,Len(Value1)) as Value2
    from #Temp Where ( Value1  Like '%x%'  )
    Union all
    Select Id,
    SUBSTRING(Value2,1,CharIndex('x',Value2)-1) as Value1,
    SUBSTRING(Value2,CharIndex('x',Value2)+1,Len(Value2)) as Value2
    from #Temp Where ( Value2  Like '%x%'  )
    Order by ID

    --Clean Up:
    Drop table #Temp

score 0 · Answer 3 · answered Apr 25 '13 at 07:52

Please check:

DECLARE @table as TABLE(id nvarchar(10), Value1 Nvarchar(50), Value2 nvarchar(50))
insert into @table values
('001', '200', '300'),
('002', '200x300', '400x200'),
('003', '300x200', '500x600'),
('004', '100', '200')

select id, Value1, Value2 From @table
select id, Value1, Value2 From @table where CHARINDEX('x', Value2)=0
union all
select id, SUBSTRING(Value1, 1, CHARINDEX('x', Value1)-1) Value1, SUBSTRING(Value1, CHARINDEX('x', Value1)+1, LEN(Value1)) Value2  From @table where CHARINDEX('x', Value2)>0
union all
select id, SUBSTRING(Value2, 1, CHARINDEX('x', Value2)-1) Value1, SUBSTRING(Value2, CHARINDEX('x', Value2)+1, LEN(Value1)) Value2  From @table where CHARINDEX('x', Value2)>0
order by id

How to split the column and row values

3 Answers3