I need to write a SQL query that cant pull multiple records (from one table) into one record.

quest_capital
quest_capital used Ask the Experts™
on
I need to write a SQL query that cant pull multiple records (from one table) into one record.
Example:
From:
[Table1]
first_name | records
joe | r1
joe | r2
joe | r3
to
[Table1]
first_name | records
joe | r1, r2, r3

There may be more than 3 records there could be 1 to 20 records.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015

Commented:
There are ways to concatenate values into a single variable, but I don't think that's what you're looking for.  If you're trying to group the results for output/display in a front end application, then I recommend doing the grouping in the front end application - not in the database.  Trying to do it with sql is often more trouble and resource intensive than its worth.  
AneeshDatabase Consultant
Top Expert 2009
Commented:
create function dbo.ConcatList ( @id varchar(100))
returns varchar(8000)
as
begin
  declare @res varchar(8000)
  select @res = coalesce(@res + ',', '') + Records
   from Table1
   where first_Name = @id
  return (@res)
end


and use it like this:

insert into destinationTable(Name, records)
select Name, dbo.ConcatList(Name)
from yourtable
group by id

Commented:
Solution from aneesh is the one we used many times
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015

Commented:
@aneeshattingal,

should't you pass in the record @id instead of the name?  

@vnvk,

Yes, the coalesce trick is good. Thats what I was alluding to earlier. But I still maintain its a task for front-end applications   ;-)

I'm curious, how do you find performance? I've always found these types of functions to be a bit slow. So I tend not to use them.  What has been your experience?
AneeshDatabase Consultant
Top Expert 2009

Commented:
insert into destinationTable(Name, records)
select Name, dbo.ConcatList(Name)
from yourtable
group by name

Most Valuable Expert 2015

Commented:
Scratch what I said about the @id. That's obviously wrong :)

Commented:
When I used them, I found a large increase in performance compared to manipuations in the front end

SQL 2005 is more powerful than 2000 and we found increased performance in using the above technique. We tried using cursors as well as doing the changes from front, but this proved to be the best, at all times, irrespective of the size of the database or the amount of records retrieved.

Author

Commented:
aneeshattingal:
Can you do this with a select only (I can't Insert)
The code below gives me nothing. What am I doing wrong

declare @res varchar(8000)
  select @res = coalesce(@res + ',', '') + [description]
  from dbo.tmshtData
  where userid = 12345

Most Valuable Expert 2015

Commented:
@vnvk,

That may be the difference.  I spend most of my time on 2000 where they seem to be slower with larger sets.  (We tried cursors too)  I would be *very* pleased if 2005 handles it better than 2000.  I think I'll revisit the idea and will do some tests next time I'm on 2005.  Thanks!
Most Valuable Expert 2015

Commented:
@quest_capital,

You mean you cannot create functions?

Author

Commented:
_agx_:
no only selects this is on prodution
Most Valuable Expert 2015

Commented:
Oh.  I'm not sure I know of a way to it then (at least not on 2000).  The problem is you cannot assign a @variable and select columns in the same statement.  Thats why you need the UDF (function). I assume that same restriction applies to 2005.  
Most Valuable Expert 2015
Commented:
Clarification, you could use aneeshattingal's technique for a SINGLE name like this:

  --- build the list
  declare @res varchar(8000)
  select @res = coalesce(@res + ',', '') + [description]
  from dbo.tmshtData
  where userid = 12345

  --- return the user column and list
  select userid, @res as records
  from dbo.tmshtData
  where userid = 12345

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial