Sql – Is COALESCE the best way to achieve this

sqlsql-server-2005

Just want to see if anyone has a better way to accomplish what I need.

First a background. I have a table in my database that currently has about 20,000 rows. In the table we have a column for FirstName and LastName. There is also a column that is a ReferenceID. There can be multiple FirstName, LastName combinations for each ReferenceID.

What that means is when we do a select * from the table we get multiple rows back for each ReferenceID (since there is a row for each user).

I want to concatenate these two columns in a column called Name at the same time as taking each row for the ReferenceID and having it turned into one. (Basically flatten the data).

Let me try to draw an ascii diagram here.

TABLE
ID        ReferenceID        FirstName        LastName
________________________________________________
1         1                  Mike             Ekim
2         1                  Tom              Mot
3         1                  Pete             Etep
4         2                  Ryan             Nayr
5         3                  Jerry            Yrrej

So the end result of what I would like is a set such as

RESULT SET
ReferenceID   Name
__________________________________
1              Mike Ekim, Tom Mot, Pete Etep
2              Ryan Nayr
3              Jerry Yrrej

What I really need to know is before I start down a path with COALESCE and try pivoting my results is there a better way to accomplish this? Using SQL Server 2005.

Cheers,

Mike

Best Answer

Here is how you do it with the xml path technique (there is a coalesce in there too...)

SELECT DISTINCT n.ReferenceID,
STUFF((SELECT ', ' + COALESCE(n2.FirstName+' '+n2.LastName,n2.FirstName,n2.LastName,'NoName') 
    FROM namelist n2
    WHERE n.referenceid = n2.referenceid
    ORDER BY n2.lastname, n2.firstname
    FOR XML PATH('')
  ), 1, 2, '') AS [Name]
FROM namelist n

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

Assuming you're joining on columns with no duplicates, which is a very common case:

An inner join of A and B gives the result of A intersect B, i.e. the inner part of a Venn diagram intersection.
An outer join of A and B gives the results of A union B, i.e. the outer parts of a Venn diagram union.

Examples

Suppose you have two tables, with a single column each, and data as follows:

Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B.

Inner join

An inner join using either of the equivalent queries gives the intersection of the two tables, i.e. the two rows they have in common.

select * from a INNER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b;

a | b
--+--
3 | 3
4 | 4

Left outer join

A left outer join will give all rows in A, plus any common rows in B.

select * from a LEFT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a = b.b(+);

a |  b
--+-----
1 | null
2 | null
3 |    3
4 |    4

Right outer join

A right outer join will give all rows in B, plus any common rows in A.

select * from a RIGHT OUTER JOIN b on a.a = b.b;
select a.*, b.*  from a,b where a.a(+) = b.b;

a    |  b
-----+----
3    |  3
4    |  4
null |  5
null |  6

Full outer join

A full outer join will give you the union of A and B, i.e. all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in B, then the B portion is null, and vice versa.

select * from a FULL OUTER JOIN b on a.a = b.b;

 a   |  b
-----+-----
   1 | null
   2 | null
   3 |    3
   4 |    4
null |    6
null |    5

SQL Server – Best way to get identity of inserted row

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes. You need to be careful here, since it's across scopes. You could get a value from a trigger, instead of your current statement.
SCOPE_IDENTITY() returns the last identity value generated for any table in the current session and the current scope. Generally what you want to use.
IDENT_CURRENT('tableName') returns the last identity value generated for a specific table in any session and any scope. This lets you specify which table you want the value from, in case the two above aren't quite what you need (very rare). Also, as @Guy Starbuck mentioned, "You could use this if you want to get the current IDENTITY value for a table that you have not inserted a record into."
The OUTPUT clause of the INSERT statement will let you access every row that was inserted via that statement. Since it's scoped to the specific statement, it's more straightforward than the other functions above. However, it's a little more verbose (you'll need to insert into a table variable/temp table and then query that) and it gives results even in an error scenario where the statement is rolled back. That said, if your query uses a parallel execution plan, this is the only guaranteed method for getting the identity (short of turning off parallelism). However, it is executed before triggers and cannot be used to return trigger-generated values.

Best Answer

Related Solutions

Sql – the difference between “INNER JOIN” and “OUTER JOIN”

SQL Server – Best way to get identity of inserted row

Related Topic