Solved

Group Items Onto a Single Line

Posted on 2006-11-14
16
200 Views
Last Modified: 2006-11-18
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!
0
Comment
Question by:allanau20
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 100 total points
Comment Utility
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
 
LVL 5

Author Comment

by:allanau20
Comment Utility
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
 
LVL 11

Accepted Solution

by:
rw3admin earned 350 total points
Comment Utility
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
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
 
LVL 1

Assisted Solution

by:Yogeshup
Yogeshup earned 50 total points
Comment Utility
If it is SQL 2005, you can use something like this

http://blogs.x2line.com/al/archive/2005/11/13/1319.aspx
0
 
LVL 5

Author Comment

by:allanau20
Comment Utility
>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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 5

Author Comment

by:allanau20
Comment Utility
Thanks angelIII!
0
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
>>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
 
LVL 5

Author Comment

by:allanau20
Comment Utility
Thanks rw3admin!
0
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
Welcome
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
yep I agree you are right here, but the index should be  on both PerID and ItemID...
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
>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
 
LVL 11

Expert Comment

by:rw3admin
Comment Utility
Ah you got me there  .... :) ...

Thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now