How do I avoid getting duplicate information?

I have two tables...

CustomerId, Name   
1           pete
2           dave
3           jon


CustomerId, Role
1           1
1           2
2           1
3           2
3           3

And I want the select to return data in the following format

Name,  Role
pete   1
null   1
dave   2
jon    2
null   3

Basically when I look at a select with hundreds of records I want to be able to see just the customer then all the roles after them in a list开发者_开发问答 and return null where there arent duplicates in the table that the coumn references.

-- TEST DB PREPARATION!!!

DROP TABLE #Names
DROP TABLE #Roles

CREATE TABLE #Names
(
    CustomerId INT,
    Name VARCHAR(1000)
)

CREATE TABLE #Roles
(
    CustomerId INT,
    Role Int
)

INSERT INTO #Names VALUES (1, 'pete')
INSERT INTO #Names VALUES (2, 'dave')
INSERT INTO #Names VALUES (3, 'jon')

INSERT INTO #Roles VALUES (1, 1)
INSERT INTO #Roles VALUES (1, 2)
INSERT INTO #Roles VALUES (2, 1)
INSERT INTO #Roles VALUES (3, 2)
INSERT INTO #Roles VALUES (3, 3)

-- HERE BEGINS THE REAL CODE!!!

; WITH Base AS
(
    SELECT #Names.CustomerId, Name, Role, ROW_NUMBER() OVER (PARTITION BY #Names.CustomerId ORDER BY Role) RN FROM #Names INNER JOIN #Roles ON #Names.CustomerId = #Roles.CustomerId 
)

SELECT CustomerId, CASE WHEN RN = 1 THEN Name END Name, Role FROM Base

I'm using the ROW_NUMBER() to number the Names and only for the first (RN = 1) I'm "writing" the name. I'm using the CTE (the WITH... AS) because I love to use them instead of writing directly nested queries :-)

Here you go:

DECLARE @t TABLE
(
id INT,
Name VARCHAR(20),
Role INT
)

INSERT INTO @t
    SELECT ROW_NUMBER() OVER(ORDER BY Names.CustomerID, Roles.Role), Names.Name, Roles.Role 
    FROM @names AS Names
        INNER JOIN @roles AS Roles ON Roles.CustomerId = Names.CustomerId

UPDATE @t
SET Name = NULL
FROM @t Temp1
WHERE EXISTS(SELECT TOP 1 1 FROM @t Temp2 WHERE Temp2.Name = Temp1.Name AND Temp2.id < Temp1.id)

SELECT * FROM @t

Replace @Names and @Roles with your table definitions.

You can also do this with temp tables or recursive CTE or cursors but a simple table variable will do fine.

For reference, my test code:

DECLARE @names TABLE
(
CustomerId INT,
Name VARCHAR(20)
)

DECLARE @roles TABLE
(
CustomerId INT,
Role INT
)

INSERT INTO @names VALUES (1, 'pete'), (2, 'dave'), (3, 'jon')

INSERT INTO @roles VALUES (1, 1), (1, 2), (2, 1), (3, 2), (3,3)

-- rest of query

My results differ from yours as I suspect you had a typo swapping with the first null and dave being swapped around:

Name,  Role
pete   1
null   2
dave   1
jon    2
null   3

Edit: Having thought about it, you can actually do it without the temp tables at all:

SELECT CASE WHEN row_no = 1 THEN Name ELSE NULL END AS Name, Role
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY Names.CustomerID ORDER BY Names.CustomerID, Roles.Role) As row_no, Names.Name, Roles.Role FROM @names AS Names
INNER JOIN @roles AS Roles ON Roles.CustomerId = Names.CustomerId
) x