Solved

Group Items Onto a Single Line

Posted on 2006-11-14
16
208 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
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 350 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 142

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 50 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 142

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 142

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 142

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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 …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

821 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