help with SQL

Database
Enthusiast

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.

6 REPLIES
Enthusiast

Re: help with SQL

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

Enthusiast

Re: help with SQL

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.

Enthusiast

Re: help with SQL

sel col1,ts,max(col2),max(col3),max(col4),sum(col5) group by 1,2;

note: Max includes only no-null values

Enthusiast

Re: help with SQL

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.

Enthusiast

Re: help with SQL

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

Re: help with SQL

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