Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Group Items Onto a Single Line

Posted on 2006-11-14
16
Medium Priority
?
223 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 400 total points
ID: 17940884
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
ID: 17941098
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 1400 total points
ID: 17941222
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17941364
>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
ID: 17941418
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 200 total points
ID: 17944704
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
ID: 17950757
>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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17950865
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
 
LVL 5

Author Comment

by:allanau20
ID: 17950875
Thanks angelIII!
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17950930
>>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
ID: 17950941
Thanks rw3admin!
0
 
LVL 11

Expert Comment

by:rw3admin
ID: 17950981
Welcome
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17950990
>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
ID: 17951073
yep I agree you are right here, but the index should be  on both PerID and ItemID...
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17953716
>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
ID: 17956643
Ah you got me there  .... :) ...

Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
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.

610 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