SQL Puzzle | The Address puzzle

In this puzzle you have to create a new columns using select, When the value of the AddressType column is cor then you have to take the minimum or the first value of the address line else you show the same value from the address line column.
Please check out the sample input values and sample expected output below.

Sample Input

1 cor london
1 cor london
1 res manchester
1 res luton
1 cor Athens

Expected Output

1 cor Athens Athens
1 cor london Athens
1 cor london Athens
1 res luton luton
1 res manchester manchester

Script

Use below script to create table and insert sample data into it.

 ```-- CREATE TABLE ADDR ( custid INT ,addresstype VARCHAR(10) ,adressline1 VARCHAR(20) ) GO INSERT INTO ADDR VALUES (1, 'cor' ,'london' ), (1, 'cor' ,'london' ), (1, 'res' ,'manchester' ), (1, 'res' ,'luton' ) GO INSERT INTO ADDR VALUES ( 1, 'cor' , 'Athens' ) GO -- ```

Rules/Restrictions

The solution should be should use “SELECT” statement or “CTE”.

Solution – 1

 ``` -- select * , CASE WHEN addresstype = 'COR' THEN FIRST_VALUE(adressline1) OVER (PARTITION BY custid,addresstype ORDER BY adressline1) ELSE adressline1 end as address1 from ADDR -- ```

Solution – 2

 ``` -- select * , CASE WHEN addresstype = 'COR' THEN MIN(adressline1) OVER (PARTITION BY custid,addresstype) else adressline1 end as address1 from ADDR -- ```

Output

```
/*------------------------

------------------------*/
----------- ----------- -------------------- --------------------
1           cor         Athens               Athens
1           cor         london               Athens
1           cor         london               Athens
1           res         luton                luton
1           res         manchester           manchester

(5 row(s) affected)

--
```

Add a comment if you have any other solution in mind. I would love to learn it. We all need to learn.

Enjoy !!! Keep Learning

Pawan Khowal

Http://MSBISkills.com