none
separate column values into multiple rows

    Question

  • field1       field2         field3        field4  

    abc xyz 100.00        3.00

    separate like this

    field1       field2         field3        field4  

    abc xyz 100.00       0.00

    abc xyz 0.00           3.00

    Wednesday, November 13, 2013 5:38 AM

Answers

  • Hi,

    try this :

    declare @t1 table(field1 varchar(10),field2 varchar(10),field3 numeric(10,2),field4 numeric(10,2) )
    
    insert @t1 values ('abc','xyz',100,3)
    select * from @t1
    
    select field1,field2,00.00,field4 from @t1 
    union 
    select field1,field2,field3,00.00 from @t1


    Best regards,
    Wednesday, November 13, 2013 5:50 AM
  • One method:

    create Table Test_Nov13_1 (field1 varchar(100),field2 int, field3 int,field4 int)
    Insert into Test_Nov13_1 Select 'abc', 100,30,NULL
    
    ;With cte
    As
    (
    	Select 
    		field1,
    		Case when B.Val =1 then field2 Else 0 End field2, 
    		Case when B.Val =2 then field3 Else 0 End field3,
    		Case when B.Val =3 then field4 Else 0 End field4
    	From Test_Nov13_1 
    	Cross apply( values (1),(2),(3) )B(Val) --The column number should be provided here.
    ) Select * From cte where (field2+field3+field4)<>0
    Drop table Test_Nov13_1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 13, 2013 5:57 AM
  • declare @t as table(f1 varchar(10),f2 varchar(10),f3 money,f4 money)
    insert into @t values ('abc','xyz',100.00,3.00),('def','ghi',200.00,4.00)
    select * from @t
    SELECT t.f1,
      t.f2,
      x.f3,
      x.f4
    FROM @t t
    CROSS APPLY 
    (
        VALUES
            (t.f3,0.00 ),
            (0.00, t.f4)
    ) x (f3, f4)


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    Wednesday, November 13, 2013 5:58 AM

All replies

  • Hi,

    try this :

    declare @t1 table(field1 varchar(10),field2 varchar(10),field3 numeric(10,2),field4 numeric(10,2) )
    
    insert @t1 values ('abc','xyz',100,3)
    select * from @t1
    
    select field1,field2,00.00,field4 from @t1 
    union 
    select field1,field2,field3,00.00 from @t1


    Best regards,
    Wednesday, November 13, 2013 5:50 AM
  • One method:

    create Table Test_Nov13_1 (field1 varchar(100),field2 int, field3 int,field4 int)
    Insert into Test_Nov13_1 Select 'abc', 100,30,NULL
    
    ;With cte
    As
    (
    	Select 
    		field1,
    		Case when B.Val =1 then field2 Else 0 End field2, 
    		Case when B.Val =2 then field3 Else 0 End field3,
    		Case when B.Val =3 then field4 Else 0 End field4
    	From Test_Nov13_1 
    	Cross apply( values (1),(2),(3) )B(Val) --The column number should be provided here.
    ) Select * From cte where (field2+field3+field4)<>0
    Drop table Test_Nov13_1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, November 13, 2013 5:57 AM
  • declare @t as table(f1 varchar(10),f2 varchar(10),f3 money,f4 money)
    insert into @t values ('abc','xyz',100.00,3.00),('def','ghi',200.00,4.00)
    select * from @t
    SELECT t.f1,
      t.f2,
      x.f3,
      x.f4
    FROM @t t
    CROSS APPLY 
    (
        VALUES
            (t.f3,0.00 ),
            (0.00, t.f4)
    ) x (f3, f4)


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you


    Wednesday, November 13, 2013 5:58 AM
  • thanks
    Wednesday, November 13, 2013 7:26 AM
  • thanks
    Wednesday, November 13, 2013 7:26 AM
  • thanks
    Wednesday, November 13, 2013 7:26 AM