SQL Query to combine data from Rows which has data

I'm sure there's a better way, but if you're sure that you only have 2 cols and there are only 4, you can try:
SELECT CASE WHEN Col1=1 THEN Col1 ELSE CASE WHEN Col2=1 THEN Col2 ELSE Col3 END END FirstCol,CASE WHEN Col2=1 AND Col1=1 THEN Col2 ELSE CASE WHEN Col3=1 AND (Col2=1 OR Col1=1) THEN Col3 ELSE Col4 END END SecondCol
FROM MyTable

This will not work because
a) I have 20 columns
b) Case else statement will only get One Column with data and then exit out.

This might do the trick ... uses an UNPIVOT to convert the the multiple columns to one then a PIVOT to turn them back into 2.

;with test_data as (
SELECT 0 col1, 2 col2, 3 col3, 0 col4 union all
SELECT 4 col1, 0 col2, 5 col3, 0 col4 union all
SELECT 2 col1, 5 col2, 5 col3, 0 col4 union all
SELECT 4 col1, 0 col2, 8 col3, 9 col4 union all
SELECT 0 col1, 6 col2, 0 col3, 7 col4
)
select [1], [2]
from (select id, col, row_number() over(partition by id order by colseq) as ky
      from (select id, col, colseq
            from (select row_number() over(order by col1) as id, col1 as [1], col2 as [2], col3 as [3], col4 as [4]
                  from test_data) v
            unpivot(col for colseq in ([1],[2],[3],[4]) ) as unpvt)x
      where col <> 0 )y
pivot(max(col) for ky in ([1],[2]))pvt

Select allOpen in new window

Inner most SELECT puts aliases on the columns as [1], [2] etc as these become row values that are used in an ORDER BY to ensure the first 2 values are selected.  The row_number() is used to get a grouping identifier for each row (pseudo PK).

Next SELECT out does the unpivot.

Penultimate SELECT add a new row_number() which becomes the pivot key.  Values of 0 are removed.

Outermost SELECT takes the unpivoted data and returns into into a pivot of only 2 values.

Topics: SQL Server 2008, Databases Miscellaneous, MySQL Server

uses an UNPIVOT to convert the the multiple columns to one then a PIVOT to turn them back into 2.
We can only hope they are not using MySQL ...

>>I need the data back as
Col2 , Col3
Col1 , Col3
Col2 , Col4<<
Is that one comma delimited field? So if the third row of your example included data for Col3, it would come back as Col2 , Col3, Col4?

Good pickup @acperkins ... I forgot to state that would only work in SQL Server (2005+).

For MySQL ... try

select max(case when seq = 1 then col end) as "1",
       max(case when seq = 2 then col end) as "2"
from (select id, col, if(@lastid=id,@i:=@i+1,@i:=1) as seq, @lastid:=id
      from (select id, 
                   colseq,
                   case colseq when 1 then col1 
                               when 2 then col2 
                               when 3 then col3 
                               when 4 then col4 
                   end as col
            from (select @id:=@id+1 as id, col1, col2, col3, col4
                  from test_data td, 
                       (select @id:=0)v1)x,
                 (select @s:=@s+1 as colseq
                  from (select 1 x union all select 1 union all select 1 union all select 1)a,
--                       (select 1 x union all select 1 union all select 1 union all select 1)b,
--                       (select 1 x union all select 1 union all select 1 union all select 1)c,
                       (select @s:=0)v2)y
            order by id, colseq)z,
           (select @i:=0,@lastid:=0)v3
       where col <> 0)f
group by id

Select allOpen in new window

Where the the inner in-line view 'x'
                  (select @id:=@id+1 as id, col1, col2, col3, col4
                  from test_data td,
                       (select @id:=0)v1)x
add a unique sequence to each row of the data table (pseudo PK).  If the table already has one - use it instead.

This is then cross joined to the in-line view 'y'
                 (select @s:=@s+1 as colseq
                  from (select 1 x union all select 1 union all select 1 union all select 1)a,
--                       (select 1 x union all select 1 union all select 1 union all select 1)b,
--                       (select 1 x union all select 1 union all select 1 union all select 1)c,
                       (select @s:=0)v2)y
which generates a list of sequential number ... with only in-line view 'a' included ... it is limited to 4 rows.  The number of rows must be >= the number of columns in the data table to be treated.  If more than 4, uncomment in-line 'b' ... this will give 16 (4x4), more than 16 ... uncomment 'c' to get 64 (4x4x4).

The next in-line view 'z' takes the columns and 'unpivots' into a single column ... add to the case for more columns.

Next the rows with 0 are excluded and another sequence within original row is added.

The outer select does an old-school pivot to bring it back to two columns.

You indicate that you have 20 columns. Can all twenty have data? If so, do you want 20 columns in your output or one column containing all 20 sets of values (comma delimited)?

They were not the exact answers but good enough to get me started in right direction.