Seprating Records With A Comma 3

[arpan]

Consider the following DDLS (which CREATEs 2 tables):

CREATE TABLE Contacts (ContactID int,ContactName varchar(50))
INSERT INTO Contacts VALUES(1,'Danny')
INSERT INTO Contacts VALUES(2,'John')
INSERT INTO Contacts VALUES(3,'Chris')
INSERT INTO Contacts VALUES(4,'Dolly')
INSERT INTO Contacts VALUES(5,'Mary')
------------------------------------------------------------
CREATE TABLE Client(ClientID int,ContactID int,ClientName varchar(50))
INSERT INTO Client VALUES(1,1,'Business Academy')
INSERT INTO Client VALUES(2,1,'Pri. School')
INSERT INTO Client VALUES(3,2,'Sec. School')
INSERT INTO Client VALUES(4,2,'Junior College')
INSERT INTO Client VALUES(5,3,'Senior College')
INSERT INTO Client VALUES(6,4,'Community Education')
INSERT INTO Client VALUES(7,5,'Youth Development')
------------------------------------------------------------

Next I have the following SQL query:

SELECT DISTINCT Contacts.ContactID,Contacts.ContactName,Client.ClientName FROM Contacts INNER JOIN Client ON
Client.ContactID=Contacts.ContactID

When the above query is executed in the Query Analyzer, 7 records are displayed. Please have a look at the recordset. You will find that under the column 'ContactName', the record 'Danny' appears twice (though I have used DISTINCT) since it has 2 ClientNames ('Business Academy' & 'Primary School') related to it. But I want that the record 'Danny' should be displayed only once in the recordset & under the column named 'ClientNames', the corresponding record should be a comma-delimited list i.e. I want that under the column 'ClientNames', the corresponding record should be displayed as Business Academy, Primary School. The records should look something like this:

-----------------------------------------------------
ContactID  ContactName   ClientName
-----------------------------------------------------
   1        Danny        Business Academy,Pri. School
   2        John         Junior College
   ..............................
   ..............................
-----------------------------------------------------

How do I display the records like I have shown above?

Thanks,

Arpan

 

[JamesLean]

No, you can't call a stored proc in the same way as I used the function. (Obviously you could simply encapsulate my script in a SP and just execute that).


--James

 

[arpan]

James,

Thanks once again for your advice. Unfortunately

SELECT * FROM #result

doesn't show any records. It only shows (1 row(s) affected) 5 times, that's all!!!

Also won't it be better if I include whatever you have done in a stored procedure?

Arpan

 

[JamesLean]

Hmm, this works perfectly for me on the sample data you originally posted.

In Query Analyzer, type:

CREATE PROC GetClients AS

Then copy/paste my entire script after that and execute it to create the SP. Then simply execute:

EXEC getclients

--James

 

[arpan]

THANKS JAMES.........YOUR SUGGESTION HAS INDEED WORKED OUT!!

Regards,

Arpan

 

[JamesLean]

Glad we got there in the end!

--James

 

[arpan]

James,

You will be happy to know that even the function which you have created works fine in SQL Server 2K. I tried it in my friend's PC & it worked. One last doubt......please. In the function you have the line:

SELECT @clients=CASE WHEN @clients IS NULL THEN ClientName ELSE @clients +
', ' + ClientName END
FROM Client
WHERE ContactID=@ContactID

I could very well understand the ELSE part but could you please explain me why you have used WHEN @clients IS NULL...........I mean to say how does that pick up the correct records? Why have you used IS NULL?

Thanks,

Arpan

 

[pascalsql]

>Why have you used IS NULL?
Because null acts as a black hole for concatenation
when you add something to null you get null.

Just as if uou want to multiply all values in an excell column. If only one is zero, the result is zero.

And at start the variable @clients is null (unless you initialize it with an empty string), so you must chek the first occurence

 

[arpan]

Pascalsql,

You say that at first @clients is null then how come the first record under the ClientName column gets delimited by a comma?

Arpan

 

[JamesLean]

Think of it as a "loop" through then table rows. On the first row, @clients is NULL so it just takes the ClientName from the row.

On second and subsequent rows, @clients is no longer NULL so it adds a comma and then the next ClientName to the variable.

--James

 

[arpan]

The same question again, James......then how come it delimits the very first record with a comma? Please explain me this. Also in the second & the subsequent rows, a comma gets added to the client names but then why there isn't a comma in the 2nd & subsequent records where the rows under the ClientName column have only one client?

Arpan

 

[JamesLean]

Right, let's try and make this as clear as possible!

Row 1
@clients is NULL so just take the ClientName:
-> @clients = "Name1"

Row 2
@clients is NOT NULL so add a comma and the ClientName:
-> @clients = "Name1, Name2"

Row 3
@clients is NOT NULL so add a comma and the ClientName:
-> @clients = "Name1, Name2, Name3"

...etc...

Do you see?

--James

 

[arpan]

But what you have explained is not what is exactly happening. What is happening is the following:

Row1
@clients is NULL
->@clients="Name1","Name2"
(in the first row, the record under the "ContactName" column is Danny & the corresponding records under the
"ClientName" column are Business Academy, Pri. School.....look at my very first post in this thread)

Row2
@clients is NOT NULL
->@clients="Name3"

Row3
@clients is NOT NULL
->@clients="Name4"

but what should actually happen, as per the query is

Row1
@clients is NULL
->@clients="Name1"

Row2
@clients is NOT NULL
->@clients="Name2","Name3" (if "Name3" exists)

OR

Row2
@clients is NOT NULL
->@clients="Name2", (if "Name3" does not exist)

Row3
@clients is NOT NULL
->@clients="Name4","Name5" (if "Name5" exists)

OR

Row3
@clients is NOT NULL
->@clients="Name4", (if "Name5" does not exist)

Arpan

 

[JamesLean]

OK, you've completely lost me now. Can I just confirm that the results ARE coming out correctly, ie:

1 | Danny | Business Academy, Pri. School

And you are just trying to confirm how it works? Or have I misunderstood and the results are NOT working as you require?

--James

 

[arpan]

The results are being retrieved exactly as how I need it i.e. they are being retrieved correctly exactly as per the example you have given. I am trying to confirm how it works.

Arpan

 

[JamesLean]

Well, at the risk of repeating myself, look at the actual statement:

SELECT @clients = CASE WHEN @clients IS NULL THEN ClientName ELSE @clients + ', ' + ClientName END
FROM Client
WHERE ContactID = @ContactID

This is only ever dealing with the clients of a specific contact, so for Danny the recordset it is working over looks like this:

ClientID | ContactID | ClientName
1 | 1 | Business Academy
2 | 1 | Pri. School

Now, the select iterates over the ClientName column, building a single string variable from the results.

At the start, @clients is NULL.

It goes to row 1, @clients is NULL so it sets @clients to the value in the ClientName column (as per the CASE expression).

@clients is now 'Business Academy'.

Now it goes to row 2. @clients is now NOT NULL so it sets @clients to @clients + ', ' + the value in the ClientName column.

@clients is now 'Business Academy, Pri. School'.

And that's it! I don't think I can explain it any more clearly than that.

--James

 

[arpan]

Yeah, James, that was the perfect explanation. Thanks for the same. Sorry to have bothered you so much.

Regards,

Arpan