How do I avoid getting duplicate information?

Question

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.

I meant what logic are you using to define your desired results? Unclear to me why Role=1 has pete,null but Role=2 has dave,jon (and I see the answer you accepted returns something different from what you said you wanted anyway!) — Martin Smith
– Martin Smith, Commented Oct 17, 2011 at 15:25

xanatos · Accepted Answer · 2011-10-17 08:44:43Z

1

-- 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 :-)

edited Oct 17, 2011 at 8:44

answered Oct 17, 2011 at 8:32

xanatos

112k13 gold badges209 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Exitos Over a year ago

This is a great solution thanks alot Im going to figure out how these partitions work and use it in my queries...

Exitos Over a year ago

Just a quick question though why did you use #tablename and not just tablename?

xanatos Over a year ago

@Pete2k Because I was creating temporary tables on my SQL server to run the tests. So that I don't have to drop them later. Tables that start with # are local temporary table. Read here msdn.microsoft.com/en-us/library/ms174979.aspx

Seph · Accepted Answer · 2011-10-17 08:37:06Z

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

Collectives™ on Stack Overflow

How do I avoid getting duplicate information?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related