Help needed with simple many-to-one result set query 1

[desperateUser]

I'm not able to adequately search for previous posts with this because I'm not sure how to wordsmith the question. This will totally seem like a n00b ? too. TIA...

I have a simple table with an index field, two fields that when combined make a reference number and a numeric field that represents a person. That's it.

INDEX prefix ref_num person
36087 A 123 36897
36088 B 456 12348
36089 A 897 55868
36090 A 897 36981
36091 A 897 48963

How do I build a query that will return this:

prefix ref_num person(s)
A 123 36897
B 456 12348
A 897 55868, 36981, 48963

The number of persons involved with each reference number can range from one to 15+

Like I said, thanks in advance for help with this. I can't seem to wrap my brain around this one.

Penelope in Portlnad

 

[gmmastros]

Take a look here and post back with any additional questions.

thread183-1159740



-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Unforturnaly it is not a real simple task in sql server and there are several methods of doing it.

I prefer "FOR XML" method which you can google for more examples.

Here is one I pulled off real quick.

SELECT DISTINCT s1.ID,
STUFF((SELECT ',' + s2.CODE
FROM @Sample AS s2
WHERE s2.ID = s1.ID FOR XML PATH(')), 1, 1, ') AS CODES
FROM @Sample AS s1
ORDER BY s1.ID

Simi

 

[desperateUser]

gmmastros

Thank you for the link to the thread.

I'm confused about the where condition to use.

So far I'm getting this:

A 123 ,36897
B 456 ,12348
A 897 ,55868

With no results beyond the first person number

I used this:

Declare @Result VarChar(8000)

Select prefix, ref_num, tbl@Result = IsNull(@Result + ',' + person, person)
From   tblofData  
Where ref_num = ref_num
Order By prefix, ref_num

Select @Result

I know I am missing something - hope it isn't too dumb Thank you for helping this newbie out.

 

[gmmastros]

You need to create a function that returns the data. This function only needs to be created once in your database. You can re-use multiple times.

Create Function dbo.GetPersonListForRefNumber(@ref_num Int)
Returns VarChar(8000)
AS
Begin
  Declare @Result VarChar(8000)

  Select @Result = @Result 
                   + IsNull(@Result + ',' + person, person)
  From   tblofData  
  Where  ref_num = @ref_num
         And Person Is Not NULL

  Return @Result
End

Then you can use it in a query like this.

Select Distinct
       Prefix,
       ref_num,
       dbo.GetPersonListForRefNumber(ref_num) As [Persons]
From   tblofData
Order By Prefix, ref_num

I haven't tested the code, but it should work.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[desperateUser]

Do you have to have permissions to create functions?

I think I don't have that. I'm getting:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Function'.
Server: Msg 178, Level 15, State 1, Line 17
A RETURN statement with a return value cannot be used in this context.

 

[gmmastros]

Yes. You usually need permissions to create a function. Based on the error message you are getting, I don't think you are having a permissions problem.

Can you please post the output you get from this:

[tt]Select @@Version[/tt]

Also try changing this line:

Return @Result

To

Return (@Result)

This may be the problem, but it's hard to tell without more information.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[desperateUser]

Microsoft SQL Server  2000 - 8.00.2039 (Intel X86)   May  3 2005 23:18:38   Copyright (c) 1988-2003 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Changing to Return(@Result) did not change the result of trying to run the CreateFunction.

I really appreciate this...I'm always in awe of you guys that help out on TekTips...

 

[markros]

Since you're using SQL 2000 take a look at the beginning of this article

http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

PluralSight Learning Library

 

[desperateUser]

Hmmmm, that article is for 2005+. Maybe that's why I'm having issues? I've never even worked with FOR XML.

 

[gmmastros]

Can you copy/paste the code you are trying to execute? I suspect there is a problem somewhere and I would need to see it in order to help you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[desperateUser]

Create Function dbo.GetPersonListForRefNumber(@ref_num Int)
Returns VarChar(8000)
AS
Begin  
	Declare @Result VarChar(8000) 
	Declare @ref_num VarChar(10)

	Set @ref_num = claim_number

	Select @Result = @Result
                    + IsNull(@Result + ',' + badge_number, badge_number)  

From   tblBadge_Number    
Where  claim_number = @ref_num         
	And badge_number Is Not NULL  

Return (@Result)
End

Unfortunately I did not save my work last night before I left and I had to recreate this today. The message I get is:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Function'.
Server: Msg 178, Level 15, State 1, Line 18
A RETURN statement with a return value cannot be used in this context.

 

[gmmastros]

I do see one problem. You are passing @ref_num in to the function and also declaring it within the function, which cannot be done.

Try this:

Drop Function dbo.GetPersonListForRefNumber
Create Function dbo.GetPersonListForRefNumber(@ref_num VarChar(10))
Returns VarChar(8000)
AS
Begin  
    Declare @Result VarChar(8000) 

    Select @Result = IsNull(@Result + ',' + badge_number, badge_number)  
    From   tblBadge_Number    
    Where  claim_number = @ref_num         
           And badge_number Is Not NULL  

    Return (@Result)
End

How are you running this code. Since you have SQL2000, should I assume you are using Query Analyzer? If not, what application are you using?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[desperateUser]

Yes, I am using Query Analyzer. I made the changes as suggested and get the same message with this thrown in:

Server: Msg 137, Level 15, State 1, Line 7
Must declare the variable '@ref_num'.

 

[gmmastros]

I don't see anything wrong with the code. This is frustrating. Cal you try a simple example?

open a new query window and run this:

Create Function dbo.Hello()
Returns VarChar(8000)
AS
Begin
	Return 'Hello World'
End

Then, open another query window and run this:

Select dbo.Hello()

Any errors with this?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I just had another thought!

Execute this and post the output:

sp_dbcmptlevel 'TheNameOfYourDatabaseHere'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[desperateUser]

The first "Hello World" function gives me the same error as above.

The result of your second request is:

"The current compatibility level is 65.

 

[gmmastros]

Now it makes sense.

Functions were introduced in to SQL Server with version 2000. You are using SQL2000, but your database compatibility level is set to SQL 7. I do not recommend that you change the compatibility level without doing some thorough testing of your application. If you keep your compatibility level set to SQL 7, you will not be able to use functions. If you change the compatibility level of your database, you run the risk of breaking your code.

In a situation like this, I would encourage you to make a copy of your database (backup and restore to a new database), and then change the compatibility level of the copy to SQL 2000, and then test the app thoroughly. If nothing breaks, then you should be able to change the compatibility level of your production database and start using functions.

If you cannot change the compatibility level at all, then I am afraid you are probably out of luck. I suppose you could try a cursor or while loop to build your output, but that won't be fun at all.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[desperateUser]

Oh George! Thank you so much for helping me get to here! There is, sadly, no way to change the compatibility. The app is scheduled for replacement within the next 6 - 12 months (which is why I'm *really* here at this job, I'm more project manager than programmer.)

Thank you, thank you, thank you for helping me realize what the issue is.

Penelope

 

[gmmastros]

Of course, you're welcome. Sorry I had to be the one to break the bad news.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom