Group Items Onto a Single Line

Hello Experts!

Thanks for reading this and for your help.

Without using a function or cursor how can you
get this:

Perid               itemid
---------------    -------
000000001       99394
000000001       86580
000000001       81002
000000002       Z9000
000000002       81002
000000002       85018
000000003       99394

into this with SQL:

Perid                  itemstr
---------------       -------------------
000000001          99394, 86580, 81002
000000002          Z9000, 81002, 85018
000000003          99394

TIA!
LVL 5
allanau20Asked:
Who is Participating?
 
rw3adminConnect With a Mentor Commented:
Based on your example...

--Drop Table #T
--Drop Table #PerID
--Drop Table #Final
create table #T(Perid Varchar(255), itemid Varchar(255))
create table #Final(PerID Varchar(255), itemid varchar(8000))
Create Table #PerID (Id int Identity(1,1), PerId Varchar(255))

Insert #T (Perid, itemid)
Select '000000001','99394' Union
Select '000000001','86580' Union
Select '000000001','81002' Union
Select '000000002','Z9000' Union
Select '000000002','81002' Union
Select '000000002','85018' Union
Select '000000003','99394'  


Insert       #PerID (PerID)
Select      Distinct
      PerID
From      #T



Declare
@ID     int,
@itemid Varchar(1000)

Select @ID=1, @itemid=''
While @ID<=(Select Max(ID) from #PerID)
Begin
Select     @itemid=@itemid+itemid+','
From       #T
Inner Join
       #PerID
ON     #T.PerID=#PerID.PerID
Where  #PerID.ID=@ID

Insert #Final
Select        PerID,
           Left(@itemid, Len(@itemid)-1)
From     #PerID
Where     #PerID.ID=@ID


Select @ID=@ID+1, @itemid=''
End

Select * from #Final
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
create function dbo.concate_itemid ( @perid )
returns varchar(8000)
as
begin
  declare @res varchar(8000)
  select @res = coalesce( @res + ',' , '') + cast( itemid as varchar(20))
  from yourtable where perid = @perid
  return (@res)
end


and use it like this

select perid, dbo.concate_itemid(perid)
from yourtable
group by perid
0
 
allanau20Author Commented:
Thanks angelIII, I know how to do it in a function.

It's just that the lead developer doesn't like us using functions b/c of maintenance issues. Sigh.

Maybe this is just a thought, can I create the function in the Sproc
and drop it after the Sproc is called like a temp table?

What will happen if more than one person runs it at the same time?

Thanks again for your help!
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>Maybe this is just a thought, can I create the function in the Sproc
>and drop it after the Sproc is called like a temp table?
you did not suggest that. drop it out of your mind! immediately  ... aaargh....


>It's just that the lead developer doesn't like us using functions b/c of maintenance issues. Sigh.
well, it does not matter if he "likes" it or not, it's a matter of functionality.

you can look at the code of r3wadmin, i doubt that your lead developer will like that more.
now, I can assure you that this kind of functionality should REALLY go into a stored function.
the function should not change alot, as it does not contain ANY complexity.

Well, good luck with your lead developer.
0
 
rw3adminCommented:
I was once a lead developer and can tell you from first hand experience... they are nincompoops, they will make other developer's life miserable no matter what the do, thats why they move people like me from Development to Systems so then they can make EVERYONE in EACH DPET miserable :)

angelIII is right, a function is very clean and re-usable very easily, whereas my sophistimicated code ( I like inventing words too... just like Bush)...works only for your existing issue.

rw3admin
0
 
YogeshupConnect With a Mentor Commented:
If it is SQL 2005, you can use something like this

http://blogs.x2line.com/al/archive/2005/11/13/1319.aspx
0
 
allanau20Author Commented:
>you did not suggest that. drop it out of your mind! immediately  ... aaargh....
 Hope I didn't upset you. :)

As it turns out I'll need to use rw3admin suggestion -- it works!

This is not the first time I suggested function, but idea was turned down. Sigh.

Yogeshup -- thanks for the tip.

Thank you all for your useful comments -- really appreciate it!

Hope you don't mind if I split points since you're all helpful. :)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
just to note that the rw3admin code is basically what happens behind the scenes when implementing (using) a function. only that without the function it will be less readable (at least it should be fully documented), while the function makes things nicer.
in terms of performance, there will be no great difference. only note that you need proper index: ie a (clustered) index on the field PerID
0
 
allanau20Author Commented:
Thanks angelIII!
0
 
rw3adminCommented:
>>only note that you need proper index: ie a (clustered) index on the field PerID<<
have to disagree, this will only matter if table is huge size, and since allanau20 mentioned use of a proc, he can basically add this script in that proc and now its same as script in function, if he is documenting his proc well then everything is kosher per any dev documentation protocols

rw3admin
0
 
allanau20Author Commented:
Thanks rw3admin!
0
 
rw3adminCommented:
Welcome
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>since allanau20 mentioned use of a proc, he can basically add this script in that proc and now its same as script in function, ...
I have to agree there!

>have to disagree, this will only matter if table is huge size,
well, note that even with few thousand records performance will suffer greatly without proper indexes. and 10K records is NOT what I call huge...
0
 
rw3adminCommented:
yep I agree you are right here, but the index should be  on both PerID and ItemID...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>yep I agree you are right here, but the index should be  on both PerID and ItemID...
I cannot help myself, but that again is only potentially correct.
of course, having a covering index will help, but:

IF the index is a normal index, AND if the second field itemid is numeric,  YES, you should add it.
  if the second field itemid is not numeric, additing it to the index will not make the index necessarily fully covering, because the values for varchars are not fully stored in indexes. hence, only looking at the index will not be enough, and table pages have to read whatsoever
IF the index is a clustered index, the data pages (remember, it's the index's leaf pages) will be read whatsoever, so the data is already there in any case. you don't need the second field in the index then, because here you want all the values of the second field.

CHeers
0
 
rw3adminCommented:
Ah you got me there  .... :) ...

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.