How can I join these two tables into the same Sql result

Question

I'm trying to generate a SQL query that can join two tables together and return the result .. but the second table is 'flattened'. I'm not sure if that's the correct technical term. Is it denormalized?

Anyways, can someone suggest how I could do this?

Table: Users

UserId  Name
1       Pure.Krome
2       John
3       Jill
4       Jane

Table: UserAliases

UserAliasId  UserId  Alias
1            1       Idiot
2            1       PewPew
3            3       BlahBlahBlah

Desired results

UserId  Name        Aliases
1       Pure.Krome  Idiot PewPew
2       John
3       Jill        BlahBlahBlah
4       Jane

Please note:

A user does NOT need to have an alias. So that's a zero->many relationship (outer join)
The delimiter for the flattening of the 2nd table is a SPACE. If an alias has a space, bad luck for me. (Consider it, bad data).

Another example of my problem is to think of a StackOverflow question + tags.

(I hope everybody understood, that this was not to be taken serious) — Eugen Rieck, May 24 '12 at 05:15
exactly the same , have a look http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005 — sumit, May 24 '12 at 05:17
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/f09d4166-2030-41fe-b86e-392fbc94db53 — sumit, May 24 '12 at 05:22
And what is the technical term for what i'm trying to do? is this, denormalization? or just group concatting, like the mysql function? — Pure.Krome, May 24 '12 at 05:26
No, you still need to work around with either `STUFF()` (if you dont care about performance) or a CLR implementation. There is one on a MS blog using `StringBuilder` and there is the one in my answer. — Eugen Rieck, May 24 '12 at 05:33

score 4 · Answer 1 · answered May 24 '12 at 05:14

4

http://groupconcat.codeplex.com/ has a clone of MySQL's GROUP_CONCAT() implemented as a CLR aggregation function. I guess the SQL is not the problem, but I might as well:

SELECT 
  [Users].[UserId] AS UserId,
  [Users].[Name] AS Name,
  GROUP_CONCAT_D([UserAliases].[Alias]," ") AS Aliases
FROM [Users]
OUTER JOIN [UserAliases] ON [Users].[UserId]=[UserAliases].[UserId]

or similar.

answered May 24 '12 at 05:14

Eugen Rieck

64,175
10
70
92

@johntotetwoo Why wouldn't it? Please elaborate! – Eugen Rieck May 24 '12 at 05:22
1

@johntotetwoo The very first word of my answer tell, how to add `GROUP_CONCAT_D()` to MS SQL server! Ofcourse it is **after** this step, that the given SQL should work! – Eugen Rieck May 24 '12 at 05:31
yes it does not work and gives error on GROUP_CONCAT_D is not a recognized built-in function name. – Murtaza May 24 '12 at 05:33
1

@Murtaza Is this really so unclear? **First** do what I wrote **first**, **afterwards** do what I wrote **afterwards** – Eugen Rieck May 24 '12 at 05:34
@EugenRieck is that script only applicable in higher version of ms sql? I tried to run it on mssql 2005 express but it keeps displaying this error: `Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.` and when i Clicked the error, this line is focused. `CREATE AGGREGATE [dbo].[GROUP_CONCAT_D](@VALUE NVARCHAR (4000), @DELIMITER NVARCHAR (4))` – John Woo May 24 '12 at 07:42
@johntotetwoo Are you sure, that the Express version of MSSQL supports CLR user aggregates? I guess only the grown-up versions do. – Eugen Rieck May 24 '12 at 07:47
is that so? too bad can't install the aggregate function. anyway thanks for the idea. sorry for the comments above. – John Woo May 24 '12 at 07:51

John Woo · Answer 2 · 2012-05-24T05:46:21.183

2

This is not tested but give it a try. I have no server here.

SELECT  a.UserID, 
        a.[Name], 
        coalesce(NewTable.NameValues, '') Aliases
FROM    Users a LEFT JOIN
        (
          SELECT  UserID,
          STUFF((
            SELECT  '  ' + [Name] 
            FROM    UserAliases
            WHERE   ( UserID = Results.UserID )
            FOR XML PATH('')), 1, 2, '') AS NameValues
          FROM    UserAliases Results
          GROUP BY UserID
        ) NewTable
        on a.UserID = NewTable.UserID

Here's SQL Fiddle Output

edited May 24 '12 at 05:46

answered May 24 '12 at 05:25

John Woo

258,903
69
498
492

2

LOVE the Sql Fiddle link - didn't know that existed! – Pure.Krome May 24 '12 at 05:57

score 1 · Accepted Answer · answered May 24 '12 at 05:29

1

FOR XML PATH is handy in this situation:

SELECT UserID, Name 
    , LTRIM(RTRIM((SELECT ' ' + Alias 
        FROM UserAliases WHERE UserID = u.UserID 
        FOR XML PATH('')))) AS Aliases
FROM Users u

answered May 24 '12 at 05:29

dan radu

2,772
4
18
23

How can I join these two tables into the same Sql result

3 Answers3