Check address voor 2 properties and display in same row

Question

From the Persons table I would like to get the following output:

Number  FirstName  AddressAvenue  AddressFloor
----------------------------------------------
1       David      Long Avenue    5th Floor
2       Bob        Short Avenue   NULL
3       Peter      Middle Avenue  1st Floor

(Apparently, Bob his address does not contain a Floor number).

I thought I would get this by running the following query:

select
    p.Number
    p.FirstName
  , case when (p.Street like '%Avenue%') then p.Street end as AddressAvenue
  , case when (p.Street like '%Floor%')  then p.Street end as AddressFloor
from
  #persons
;

However, the output is as follows:

Number  FirstName  AddressAvenue  AddressFloor
----------------------------------------------
1       David      Long Avenue    NULL
2       Bob        Short Avenue   NULL
3       Peter      Middle Avenue  NULL
1       David      NULL           5th Floor
3       Peter      NULL           1st Floor

Question How can I get both Address and Floor on the same line?

Any help is greatly appreciated!

Answer 1

Try this bad boy

CREATE TABLE #persons
(
    Number INT
    ,FirstName VARCHAR(10)
    ,Street VARCHAR(50)
)

INSERT #persons
VALUES
(1, 'David', 'Long Avenue'),
(2, 'Bob', 'Short Avenue'),
(3, 'Peter', 'Middle Avenue'),
(1, 'David', '5th Floor'),
(3, 'Peter', '1st Floor')

    --This is the code you really want, I just needed the rest to test it and make sure it worked
SELECT DISTINCT
z.Number
,z.FirstName
,(SELECT p.Street FROM #persons p where p.Street LIKE '%Avenue%' AND p.Number = z.Number)
,(SELECT p.Street FROM #persons p where p.Street LIKE '%Floor%' AND p.Number = z.Number)
FROM #persons z

DROP TABLE #persons

Results in the following: