get comma seperated results from query

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi All,

I have a query to make which i m finding difficult to make.

I have a table with following structure:

Id Field
1 'aaaa'
1 'bbbb'
2 'cccc'
2 'dddd'

I want the output from the query as follows:

1 'aaaa','bbbb'
2 'cccc','dddd'

SQL

CREATE TABLE #a
(
id INT ,
field varchar(1000)
)

INSERT INTO #a
        ( id, field )
VALUES  ( 1, -- id - int
          'aaaaa'  -- field - varchar(1000)
          )
          
INSERT INTO #a
        ( id, field )
VALUES  ( 1, -- id - int
          'bbbb'  -- field - varchar(1000)
          )
          
INSERT INTO #a
        ( id, field )
VALUES  ( 2, -- id - int
          'cccc'  -- field - varchar(1000)
          )
          
INSERT INTO #a
        ( id, field )
VALUES  ( 2, -- id - int
          'ffff'  -- field - varchar(1000)
          )

Please help me .....

Regards,
Gopal

Posted 8-Sep-11 1:33am

gopalgupta

Updated 8-Sep-11 2:15am

v3

Add a Solution

4 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Om Prakash Pant · Answer 1 · 2011-09-08T02:23:00

Solution 2

Please check the following link for different options:

http://stackoverflow.com/questions/1817985/how-do-i-create-a-comma-separated-list-using-a-sql-query[^]

Posted 8-Sep-11 2:23am

Om Prakash Pant

#realJSOP · Answer 2 · 2011-09-08T02:18:00

Solution 1

I'm not a sql expert, but what you want to investigate the following t-sql commands:

COALESCE<br />
<br />
PIVOT

The PIVOT command will arrange the data the way you want it, and the COALESCE command will help you put the resulting dataset into a comma-delimited string.

Google is your friend.

Posted 8-Sep-11 2:18am

#realJSOP

Comments

[no name] 8-Sep-11 8:51am

very bad very bad

#realJSOP 8-Sep-11 9:00am

What does that mean? Why is it "very bad"? First, I preceded my statement with a disclaimer that I'm not a sql expert. Second, it may not have been the *best* answer, but certainly wasn't deserving of a 1-vote. I think you need to show a bit more respect.

MaulikDusara · Answer 3 · 2011-09-08T02:52:00

Solution 3

Hi, gopalgupta

try this one.

SQL

SELECT id, (
           SELECT field+', '
           FROM  tableName WITH (NOLOCK)
           WHERE  a.id = id
           ORDER BY
                  field
                  FOR XML PATH('')
       ) AS field
FROM   tableName a WITH (NOLOCK)
GROUP BY
       id

Posted 8-Sep-11 2:52am

MaulikDusara

v2

Comments

#realJSOP 8-Sep-11 9:14am

Our DBA said using NOLOCK (or any hint for that matter) was generally bad practice. We don't have any instances of that hint in our sql code. Notice that I didn't 1-vote your answer, I merely commented on it.

Wendelius · Answer 4 · 2011-09-08T08:50:00

Solution 4

Do you always have just two rows for each id? If that's true then you could try something like:

SQL

SELECT t1.Id,
       t1.Field,
       t2.Field
FROM   #a t1,
       #a t2
WHERE t1.Id = t2.Id
AND   t1.Field < t2.Field

Posted 8-Sep-11 8:50am

Wendelius

get comma seperated results from query

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

get comma seperated results from query

4 solutions

Solution 2

Solution 1

Solution 3

Solution 4

Add your solution here

Preview 0

Existing Members

...or Join us