Populate 1 row from multiple rows.

Database
Enthusiast

Populate 1 row from multiple rows.

Main Table

Col 1- PK
1
2

Lookup Table

Coll 1- FK     coll 2             coll 3   coll 4    coll 5
1 5/28/2015 23:44 A1 X1 123
2 5/28/2015 23:45 NULL S1 10
2 5/28/2015 23:43 B1 S2 11
2 5/28/2015 23:42 B2 S3 12
2 5/28/2015 23:41 B3 S4 13
3 5/28/2015 23:45 X1 Q1 NULL
3 5/28/2015 23:43 X2 Q2 500
3 5/28/2015 23:42 X3 Q3 501

Result, I want 

Col 1- PK     coll 2   coll 3             coll 4     coll 5
1 A1 5/28/2015 23:44 X1 123
2 B1 5/28/2015 23:43 S4 10
3 X1 5/28/2015 23:42 Q3 500

Hi Experts,

I have to achieve a above result from 2 tables mention above, when I am using Qualify to filter out one record, some values are missing that are coming from different row but same PK. I tried using RANK also. But it didn't work out. ( If i will be dividing lookup table into muliple tables using Qulaify then I need to so many left outer join for each and every column - It will work out but its not looking good approach)

Can someone suggest a good approach that I can implement?

Thanks in Advance!!

7 REPLIES
Enthusiast

Re: Populate 1 row from multiple rows.

Can you please explain the logic with which the Result shown above for Col 2? 

for Other columns logic looks like either its Min or Max ignoring the Nulls.

Can you please check if the below SQL suits your need for them.

SEL COL 1_PK, MIN ( CASE WHEN COL3 IS NOT NULL THEN COL3  END ) AS COL3

, MAX( CASE WHEN COL4 IS NOT NULL THEN COL4  END) AS COL4 

, MIN (CASE WHEN COL5 IS NOT NULL THEN COL5  END) AS COL5

FROM LOOKUP TABLE

INNER JOIN ON MAINTABLE

ON COL1_PK = COL1_FK

GROUP BY COL1_PK

Enthusiast

Re: Populate 1 row from multiple rows.

Hi Kirthi,

Result:

col 1: Unique

Colll2: Min date-time or first date-time when the record arrived.

Coll3: the latest record that isn’t null for the required value.

Coll4: value which is against the Min date-time or when record 1st arrived.

Coll5: the latest record that isn’t null for the required value.

Thanks!!

Enthusiast

Re: Populate 1 row from multiple rows.

So on your result the value for col2 for 2ndrow should have been 5/28/2015 23:41 instead of 5/28/2015 23:43.

Coming to the solution, The Approach you mentioned above (  join tables for every column)  is the viable solution in this case.

Had it been non-null columns you could have done it easily with 2 tables. one having all the fields based on Ascending time and one based on descending time qualifying the first row. and joining them based on ID.

Enthusiast

Re: Populate 1 row from multiple rows.

Like this in another table I am having 25 columns. So for fetching 25 columns I need to put 25 Qualify statement and 25 left join with same table. This is not looking good to me. I tried to implement this with RANK and Qualify but didn't worked.

Enthusiast

Re: Populate 1 row from multiple rows.

Use FIRST_VALUE,IGNORE NULLS....

FIRST_VALUE(A2.COL3 IGNORE NULLS) OVER (PARTITION BY A2.COL1 ORDER BY A2.COL2 DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING )
Enthusiast

Re: Populate 1 row from multiple rows.

will work in BTEQ scripts also?

Enthusiast

Re: Populate 1 row from multiple rows.

Yes. It works...