Query needed

Database
Highlighted
Enthusiast

Re: Query needed

Apologies for so many quries in one. Being new user I come accross many problems. I hope you can help me again in below query.

data:

table 1

address   cust_id1   cust_id2     cust_age1        cust_age2

1 CP         1001         1002             60                       65

table 2

cust_id           Rating

1001                77

1002                 90

 how can I join these two table so that I have something like below:

address   cust_id1   cust_id2     cust_age1        cust_age2        Rating_1       rating_2

1 CP         1001         1002             60                       65                   77                90

Kind Regards

Ena

Enthusiast

Re: Query needed

SELECT

address,

Max(Postcode)

MAX(CASE WHEN rownum =1 THEN age END) AS cus_id1_age,

MAX(CASE WHEN rownum =2 THEN age END) AS cus_id2_age,

MAX(CASE WHEN rownum =1 THEN custid END) AS cus_id1,

MAX(CASE WHEN rownum =2 THEN custid END) AS cus_id2,

MAX(CASE WHEN rownum =1 THEN rating END) AS rating_1,

MAX(CASE WHEN rownum =2 THEN rating END) AS rating_2

FROM

(SEL a.address,a.postcode,a.age,a.cust_id,b.rating,ROW_NUMBER()OVER(PARTITION BY a.address ORDER BY 1) AS rownum FROM The_table a

inner join tbl_b b

on a.cust_id=b.cust_id) x

GROUP BY address

You can probably do like this unless u want the previous result in a separate table and this result in a previous table

Cheers,

Mani

Enthusiast

Re: Query needed

cool Mani thats so helpful.

Thanks you very much.