• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 672
  • Last Modified:

SQL Query to combine data from Rows which has data

I need a SQL Query to combine data from Rows which has data.

So my Table1 has

Col1 Col2 Col3 Col4
-----------------------------
 0       1       1      0
 1       0       1      0
 0       1       0      1

I need the data back as

Col2 , Col3
Col1 , Col3
Col2 , Col4


Please let me know how I can do with SQL.
0
nsdlsandy
Asked:
nsdlsandy
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
CluskittCommented:
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
0
 
nsdlsandyAuthor Commented:
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.
0
 
CluskittCommented:
The problem is mostly a. If you want two columns, you need one case for each, as I've placed above. If you want more, then you need more cases. It's not very practical though, as each subsequent column has to check for previous ones.

Maybe something like this would work:
SELECT COALESCE(Col1,Col2,Col3,Col4),COALESCE(Col4,Col3,Col2,Col1)
FROM (SELECT CASE WHEN Col1=0 THEN '' ELSE 'Col1' Col1,CASE WHEN Col2=0 THEN '' ELSE 'Col2' Col2,CASE WHEN Col3=0 THEN '' ELSE 'Col3' Col3,CASE WHEN Col4=0 THEN '' ELSE 'Col4' Col4
FROM MyTable) t

This, however, will only work if you only want 2 columns returned. More would be tricky. Probably some stored proc that fills a temp table.
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
DcpKingCommented:
So OutputCol1 has whatever non-zero data there is in InputCol1 and InputCol2, and OutputCol2 has whatever non-zero data there is in InputCol3 and InputCol4. What if InputCol1 and InputCol2 both have data ? Or neither ? If the answer to these two cases is "the sum" and "zero", then you're looking at something like this for each oCol.:

select (t1.iCol1 + t1.iCol2) as 'oCol1',
          (t1.iCol3 + t1.iCol4) as 'oCol2'
from Table1 t1

If you have a varying number of columns of input (not just 20 always) you can dynamically create your SQL on-the-fly, creating a select statement with just the right number of pairs of fields. I'd guess that your combination criteria are a little more complicated than I've used.

hth

Mike
0
 
lwadwellCommented:
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

Open 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.
0
 
Anthony PerkinsCommented:
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 ...
0
 
awking00Commented:
>>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?
0
 
lwadwellCommented:
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

Open 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.
0
 
awking00Commented:
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)?
0
 
nsdlsandyAuthor Commented:
They were not the exact answers but good enough to get me started in right direction.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now