Query needed

Database

Query needed

Hi There,

I am stuck doing a analysis. I have created a table something like below:








cust_id address postcode AGE Name
1002 1 cp Ig3  35 MR. X
2003 1 cp Ig3  32 Mrs X
1003 2 cp HI2 65 MR. y
1006 2 cp HI2 63 MR. y
3003 2 cp HI2 25 Miss y

So now I want to see one row per address. Like we can see on address 1CP there are two people. So I want something like below.









address postcode cust_id_1 age_1 cust_id_2 age_2 Name_1 Name_2
1 cp Ig3  1002 35 2003 32 MR. X Mrs X

It is bit urgent so will be very gratefull if somebody can help me here.

Ena

12 REPLIES

Re: Query needed

Looks like it doesnt like copy pasting from excel. I will try to explain again.

table:

Cust_id   address   age    Name

1001         1 CP       60      Mr. X

2001          1 CP      55      Mrs Y

Output:

address    cust_id_1   age_1   Name_1   cust_id_2  age_2   Name_2

Re: Query needed

Hi there,

I anyhow managed to colaborate data. Now the problem is that I have two rows- something like below;

address    cust_id1_age   cust_id2_age   Cust_id1    cust_id2

1cp             60                     55                   1001             ?

1cp              60                     55                   2001             ?

Now I want them in single row - something like below

address    cust_id1_age   cust_id2_age   Cust_id1    cust_id2

1cp             60                     55                   1001             2001

Could someone help me on this. Its bit urgent.

Regards

Ena

Re: Query needed

select

address,

max(case when rownum =1 then age) as cus_id1_age

max(case when rownum =2 then age) as cus_id2_age

max(case when rownum =1 then custid) as cus_id1

max(case when rownum =2 then custid) as cus_id1

from

(sel address,age,cust_id,row_number()over(partition by address) as rownum from table) a

group by address

cheers

Mani

Re: Query needed

Thank you so much Mani. Really appreciated!

Its throwing error by saying something expected between address and ) that means in qualifying statement.

row_number()over(partition by address) as rownum from table)

Thanks - Ena

Re: Query needed

Row_number wont run without order by clause.

SELECT
address,
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
FROM
(SEL address,age,cust_id,ROW_NUMBER()OVER(PARTITION BY address ORDER BY 1) AS rownum FROM The_table ) a
GROUP BY address

Re: Query needed

oops !! sorry missed the order by

Re: Query needed

Thank you so much Ashvini.

Guys I am in big confusion at the moment. A little help will be great help.

ok So let me explain. I have table something like below.

Cust_id    address     Postcode          age        

1001             60 CP     IG4                55          

1002            60 CP      IG4                65           

Now I want them in single row - something like below

address    Postcode         cust_id1     Cust_id2    cust_id1_age        cust_id2_age

60cp             IG4                1001         1002             55                          65


Please if someone can help me on this urgently

Regards

Ena

Re: Query needed

SELECT

address,

Max(Postcode) -- If the postcode is not same use max else simply use Postocode and add it in group by

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

FROM

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

GROUP BY address

Cheers

Mani

Re: Query needed

Cheers Mani. Thank you very much indeed.