All Forums Database
td_admirer 36 posts Joined 07/12
28 Jul 2014
help with SQL

Hi Gurus,
Could you please help me with the below SQL? I need to merge the columns from a few rows on the same table based on a different grouping criteria.
 
This is how the input data looks like..
COL1                           TS                             COL2                    COL3              COL4              COUNT(*)
====================================================== =========
405273444339513690 2014-06-02 00:35:29 ?                          207813            ?                     1
405273444339513690 2014-06-02 00:35:29 ?                          ?                     725211            1
405273444339513690 2014-06-02 00:35:29 971062435-00001 ?                     ?                     1
 
I want the output to be something like this..
COL1                          TS                             COL2                         COL3               COL4     COL5
====================================================== ========
405273444339513690 2014-06-02 00:35:29 971062435-00001      207813            725211   3
 
Please help.
 
Thank you.

 

Glass 225 posts Joined 04/10
28 Jul 2014

What different grouping criteria? It looks like your grouping on the fier 2 columns. 

td_admirer 36 posts Joined 07/12
28 Jul 2014

Hi,
Yes, you are right. I need to group on the first 2 columns (COL1 and TS).
Also, another scenario to add
 
405273444339513690 2014-06-02 00:35:29    ?                          206-914-7813 725211           1
405273444339513690 2014-06-02 00:35:29    ?                          ?                    725211           1
405273444339513690 2014-06-02 00:35:29    971062435-00001 ?                    ?                    1
 
The output should be
 
COL1                            TS                              COL2                      COL3                 COL4     COL5
====================================================== ========
405273444339513690  2014-06-02 00:35:29   971062435-00001   206-914-7813    725211   3

Hope this helps.

Glass 225 posts Joined 04/10
28 Jul 2014

sel col1,ts,max(col2),max(col3),max(col4),sum(col5) group by 1,2;
note: Max includes only no-null values

krishaneesh 140 posts Joined 04/13
28 Jul 2014

Will the COL2, COL3 and COL4 be having multiple values or only a single repetitive value like 75211 mentioned in the example 2. if so the qury given be Glass will work.

Raja_KT 1246 posts Joined 07/09
28 Jul 2014

A quick look at the data it can be thus.
select distinct
max(case when id is null then 0 else id end) over (partition by id,dt order by id,dt),
max(case when dt is null then 0 else dt end) over (partition by id,dt order by id,dt),
max(case when usphn is null then 0 else usphn end) over (partition by id,dt order by id,dt),
max(case when vnet is null then 0 else vnet end) over (partition by id,dt order by id,dt),
count(cnt) over (partition by id,dt order by id,dt)
from test1
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

anoopbp 1 post Joined 08/13
07 Aug 2014

Hi Friend,
please check below query:
SELECT 

DISTINCT A.COL1,A.TS,B.COL2,C.COL3,D.COL4 

 

FROM  TABLENAME A

 

LEFT JOIN TABLENAME B ON   A.COL1=B.COL1 AND A.TS=B.TS AND B.COL2 IS NOT NULL

LEFT JOIN TABLENAME C ON   A.COL1=B.COL1 AND C.TS=B.TS AND C.COL3 IS NOT NULL

LEFT JOIN TABLENAME D ON   A.COL1=B.COL1 AND D.TS=B.TS AND D.COL4 IS NOT NULL

 

Result : 

COL1

TS

COL2

COL3

COL4

405273444339513690

2014-06-02 00:35:29

971062435-00001

207813           

725211 

 

You must sign in to leave a comment.