query for grouping multiple same record into 1 line

Question

Have a problem find a query for my tables

I have 2 tables table A and table B as follow

table A 
---------------------
|   Name   | addrid |
---------------------
|   zlai   |   1    |
|   blai   |   2    |

table B 
---------------------
| addrid |   addr     |
---------------------
|   1    |   AMERICA  |
|   1    |   SPAIN    |
|   1    |   MEXICO   |
|   2    |   TURKEY   |

The result I need is

--------------------------
|   Num  | Name  | addr    |
--------------------------
|    1   | zlai  | AMERICA |               
|        |       | SPAIN   |
|        |       | MEXICO  |
|    2   | blai  | TURKEY  |

Query I have tried so far

http://sqlfiddle.com/#!12/3ac39/12

Answer 1

SELECT  CASE WHEN No != 1
             THEN ''
             ELSE CAST(a.addrid AS VARCHAR(20))
        END  AS Num,
        CASE WHEN No != 1
             THEN ''
             ELSE name
        END  AS name,
        addr
FROM
        (
          SELECT  ROW_NUMBER() OVER (PARTITION BY a.addrid 
                                     ORDER BY name DESC) AS No,
                  name, 
                  addr,
                  a.addrid
          FROM    a LEFT JOIN b ON a.addrid = b.addrid
        ) a
ORDER   BY a.addrid

• SQLFiddle Demo

Answer 2

I try to modified the addrid values by 101 and 325 (for example)

table A 
---------------------
|   Name   | addrid |
---------------------
|   zlai   |   101  |
|   blai   |   325  |

table B 
---------------------
| addrid |   addr     |
---------------------
|   101  |   AMERICA  |
|   101  |   SPAIN    |
|   101  |   MEXICO   |
|   325  |   TURKEY   |

the query is:

SELECT  CASE WHEN No != 1
             THEN ''
             ELSE CAST(row AS VARCHAR(20))
        END  AS Num,
        CASE WHEN No != 1
             THEN ''
             ELSE name
        END  AS name,
        addr
FROM
        (
          SELECT  ROW_NUMBER() OVER (PARTITION BY a.addrid 
                                     ORDER BY name DESC) AS No,
                  name, 
                  addr,
                  DENSE_RANK() OVER (ORDER BY a.addrid) "row"
          FROM    a LEFT JOIN b ON a.addrid = b.addrid
        ) a
ORDER   BY row

and the result is same as you want.

SQL Fiddle Demo