Return Select inside a Column

Question

Possible Duplicate:
Concatenate column values for rows with the same values (of different columns)

Not sure if i'm asking this properly, but please review below to get a jest of what i'm trying to do here:

SELECT a.ID, (SELECT n.NAME FROM NAMES n WHERE n.ID = a.ID) Names FROM Aliases a

Which should return something like this:

 ID          NAMES
 1           Bob, Sally, Jenny

These are not multiple rows returned, this is one single row returned with multiple values. The key here is to return them in one row, instead of multiple rows. Any suggestions?

Yes, do this in your presentation layer not at the database level. — JNK, Feb 06 '12 at 15:29
@ZeeTee - then return the data as rows and don't display the `ID` for each row — JNK, Feb 06 '12 at 15:32
Can't cross join or something be used on it instead of the SELECT? — Control Freak, Feb 06 '12 at 15:33
This has been asked many many times. Do a quick search with the tags sql-server and group-concat — Lamak, Feb 06 '12 at 15:45
This is a prime candidate for the For XML Path/STUFF technique. — Jon Egerton, Feb 06 '12 at 16:03

Arion · Answer 1 · 2012-02-06T16:01:04.173

This might help: First some test data:

DECLARE @tbl TABLE(ID INT, Name VARCHAR(100))
INSERT INTO @tbl
SELECT 1,'Bob' UNION ALL
SELECT 1,'Sally' UNION ALL
SELECT 1,'Jenny'

Then I would do something like this:

;WITH CTE AS
(
    SELECT
        tbl.ID
    FROM
        @tbl AS tbl
    GROUP BY
        tbl.ID
) 
SELECT
    CTE.ID,
    STUFF
            (
            (
                SELECT 
                    ',' + Name
                FROM 
                    @tbl AS tbl 
                WHERE 
                    ID = CTE.ID 
                FOR XML PATH('')
            ),1,1,'') AS Names
FROM
    CTE

score 0 · Answer 2 · edited Feb 06 '12 at 17:03

0

The best way (as mentioned by JNK) would be to use an inner join and group the output in your application layer.

Another way would be to pivot the data so you get something like:

ID           Name 1          Name 2            Name 3

1            Bob             Sally             Jenny

This can be complicated and require writing a dynamic pivot if you don't know how many aliases someone has.

edited Feb 06 '12 at 17:03

musefan

47,875
21
135
185

answered Feb 06 '12 at 15:35

lgr1ff1n

1

it's outputting to a datatable, so i can't get too crafty on the application, it has to all be done sql side.. if i were to inner join it, then group it, how do i combine all the values from the same column into one column in one row? – Control Freak Feb 06 '12 at 15:37
1

@ZeeTee - in your app, do something like `$var = ; foreach $id in $var{}` – JNK Feb 06 '12 at 15:50

Gaspa79 · Answer 3 · 2012-02-06T16:09:49.927

This may need some refinement but will certainly do the work. I'm also aware that there's a way to do it with a FOR XML PATH ('') but I dunno exactly how to.

  CREATE TABLE #MyNames 
  ( 
      ID INT, 
      name VARCHAR(MAX) 
  )

declare @Counter1 int
set @Counter1 = 0

declare @Counter2 int
declare @Names varchar(max)

while @Counter1 < select max(Row_Number() Over(Order BY ID)) from Aliases
begin
    set @Counter2 = 0
    set @Names = ''
    while @Counter2 < select max(ID) from Aliases
        begin
            select @Names = COALESCE(@Names + ' ', '') + COALESCE(SELECT ROW_NUMBER() OVER(ORDER BY n.ID) AS 'RowNumb', n.NAME FROM NAMES n WHERE n.ID = Counter1 AND RowNumb = Counter2,'')
            set @Counter2 = Counter2 + 1
        end
    Insert into ##t VALUES(@Counter1,@Names)
set @Counter1 = @Counter1 + 1
end
Select * from #MyNames

Although this is a non-optimal solution, it was unexpectedly fun to do.

The above solution is perfect. Disregard this =) – Gaspa79 Feb 06 '12 at 16:28 — Gaspa79, Feb 06 '12 at 16:28

Return Select inside a Column

3 Answers3