Solved

Group Items Onto a Single Line

Posted on 2006-11-14
16
213 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 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
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query (lookup) 8 57
how to specify windows account to use for connection manager in SSIS package 25 65
Sorting a SQL script 5 34
T-SQL Query 9 30
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

738 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