Loop to gather sub records

[glgcag]

I am new to SQL Server 2005 so forgive me if my questions are basic, but I've moved past simple queries and I'm starting to find myself without the proper vocabulary to get some things done!

I have a query that joins three tables to extract contacts and their person types.

SELECT tMailList.MailListKey, tMailList.LastName, tMailList.FirstName, tPersonTypes.PersonTypeDesc
FROM tMailList JOIN tMailListPersonTypes ON tMailListPersonTypes.MailListKey = tMailList.MailListKey
JOIN tPersonTypes ON tMailListPersonTypes.PersonTypeKey = tPersonTypes.PersonTypeKey
ORDER BY tMailList.LastName, tMailList.FirstName;

If the user has multiple person types it looks like:

LastName FirstName PersonType
Smith John Manager
Smith John Programmer
Smith John Tech

What I need is to return one row per contact, with a field that has the person type listed in it, for example:

LastName FirstName PersonTypes
Smith John Manager, Programmer, Tech



How do I go about creating the field of concatenated PersonTypes?

I figured I should run the following query and then for each record run another query to grab all the types and concatenate them into a variable, then insert the values into a temp table or table variable. In any case, how do I do this?

SELECT DISTINCT tMailListPersonTypes.MailListKey, tMailList.LastName, tMailList.Firstname
FROM tMailListPersonTypes JOIN tMailList ON tMailListPersonTypes.MailListKey = tMailList.MailListKey
ORDER BY tMailList.LastName, tMailList.FirstName;

 

[gmmastros]

Try here: thread183-1159740

Of course, it's not exactly what you are looking for, but the method for solving your problem is in there.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[glgcag]

Thank you gmmastros for the reply.

I see in the thread how to concatenate, but for each contact I need to concatenate all of their person types. How would I loop through each contact and extract the person types?

In Access (which I'm graduating from . . .) I would open a recordset using the second query above, then use this code:

rst.movefirst
Do Until rst.eof
    lMLKey = rst.Fields("MailListKey")
    --I would then run a query that would open another recordset to extract all the Person Types for lMLKey, then I would loop through THAT recordset concatenating a variable with all the Person Types, then insert lMLKey, FirstName & LastName from rst, and PersonTypes from the variable into a temp table. Then I'd move to the next record in rst and do it again.
Loop

I assume there's a way to do this with SQL Server 2005 in a query/stored proc instead of having to do it in a client like Access, right?

 

[gmmastros]

Yes.

You'll want to create a user defined function that returns a comma delimited list of person types.

First, create this function (using the management studio)

[COLOR=blue]Create[/color] [COLOR=#FF00FF]Function[/color] dbo.GetPersonTypes
	(
	@MailListKey [COLOR=blue]Int[/color]
	)
Returns [COLOR=blue]VarChar[/color](8000)
[COLOR=blue]As[/color]
[COLOR=blue]Begin[/color]

	[COLOR=blue]Declare[/color] @Output [COLOR=blue]VarChar[/color](8000)
	[COLOR=blue]Set[/color] @Output = [COLOR=red]''[/color]

	[COLOR=blue]Select[/color] @Output = @Output + PersonType + [COLOR=red]','[/color]
    [COLOR=blue]From[/color]   TableName
    [COLOR=blue]Where[/color]  PersonType <> [COLOR=red]''[/color]

	[COLOR=blue]If[/color] [COLOR=#FF00FF]Right[/color](@Output, 1) = [COLOR=red]','[/color]
        [COLOR=blue]Set[/color] @Output = [COLOR=#FF00FF]Left[/color](@Output, Len(@Output)-1)

	[COLOR=blue]Return[/color] @Output
[COLOR=blue]End[/color]

Then, you can call this function like this...

[tt][blue]
Select Distinct
LastName,
FirstName,
dbo.GetPersonTypes(MailListKey)
From TableName
[/blue][/tt]

** All of this is untested, so you may need to do a little debugging to get this to work.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[glgcag]

gmmastros,

Which type of function would this be, table-valued, scalar-valued or aggregate? (Not sure which folder to create it in.)

Also, in the code you have "CREATE FUNCTION"- does that stay in there? I've seen that with "CREATE PROCEDURE" too, and I've wondered if that is only needed to create it initially, or if it's needed every time the function or proc runs?

Thanks for your help!

 

[gmmastros]

This is a scalar valued function. It's created once (and only once). you can re-use this function as often as you'd like.

In the management studio, all you really need to do is to open a new query window, copy/paste the code, and run it. Once it is run, it will be listed in the scalar valued functions (after you refresh the list).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[glgcag]

OK, so I adapted your code based on the following but I'm not doing it right because it keeps getting an error:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


ALTER Function [dbo].[GetPersonTypes]    
(
    @MailListKey Int
    )
Returns VarChar(8000)
As
Begin
	Declare @PTKey varchar (255)
    Declare @Output VarChar(8000)
    Set @Output = ''
	Set @PTKey = ''
	
	Select @PTKey = @PTKey + Cast(PersonTypeKey AS varchar (255)) + ','
	FROM tMailListPersonTypes
	WHERE tMailListPersonTypes.MailListKey = @MailListKey
	
	If Right(@PTKey, 1) = ','
        Set @PTKey = Left(@PTKey, Len(@PTKey)-1)

    Select @Output = @Output + PersonTypeDesc + ','
    From   tPersonTypes
    Where  PersonTypeKey IN(Cast(@PTKey AS varchar (255)))

    If Right(@Output, 1) = ','
        Set @Output = Left(@Output, Len(@Output)-1)

    Return @Output
End

Essentially, I'm first gathering into a string all the PersonTypeKeys that exist for the MailListKey in the table I'm querying, then I pass those into the "IN()" statement in the next part. Your code worked, but it gave me every PersonTypeDesc for each row, whereas, I only want the PersonTypeDesc's for a person if it exists in the tMailListPersonTypes table.

But it's giving me an error:
Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '4,6' to data type int.

What am I doing wrong?

 

[gmmastros]

Can you show some sample data for both tables and expected results? With this information, it will be easier for me to help you.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[glgcag]

The tMailList table is linked to tPersonTypes table by tMailListPersonTypes and provides the many to many link I need.

tMailList
Maillistkey (PK), LastName, FirstName

tPersonTypes
PersonTypeKey (PK), PersonTypeDesc (ie: Programmer, Manager, etc.)

tMailListPersonTypes
MailListPersonTypeKey (PK), MailListKey (FK), PersonTypeKey (FK)

So, I run a distinct select query on tMailListPersonTypes to retrieve all MailList members that have a person type. Then, I need to run a query that extracts all the person types for that MailList member.

 

[Lamprey13]

Here is another way to do it without using a UDF:

-- Setup
DECLARE @MailList TABLE(Maillistkey INT, LastName VARCHAR(50), FirstName VARCHAR(50))

DECLARE @PersonTypes TABLE(PersonTypeKey INT, PersonTypeDesc VARCHAR(50))

DECLARE @MailListPersonTypes TABLE(MailListPersonTypeKey INT, MailListKey INT, PersonTypeKey INT)

INSERT @MailList
SELECT 1, 'Smith', 'Frank'
UNION SELECT 2, 'Blow', 'Joe'

INSERT @PersonTypes
SELECT 1, 'Programmer'
UNION SELECT 2, 'Manager'
UNION SELECT 3, 'Tester'
UNION SELECT 4, 'VP'

INSERT @MailListPersonTypes
SELECT 1, 1, 1
UNION SELECT 2, 1, 3
UNION SELECT 3, 2, 2
UNION SELECT 4, 2, 4

-- Query
SELECT 
	ml.FirstName, 
	ml.LastName,
	STUFF((
		SELECT DISTINCT TOP 100 PERCENT ', ' + 
			pt.PersonTypeDesc 
		FROM 
			@PersonTypes AS pt 
		INNER JOIN
			@MailListPersonTypes AS mlpt
			ON pt.PersonTypeKey = mlpt.PersonTypeKey
		WHERE 
			mlpt.MailListKey = ml.Maillistkey 
		ORDER BY ', ' + pt.PersonTypeDesc 
		FOR XML PATH('')
		), 1, 1, '') AS PersonTypes
FROM
	@MailList AS ml

-Ryan