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

 

[AtomicWedgie]

You could use something like this in your select statement
,Clientname = SUBSTRING(REPLACE(RTRIM(LTRIM(
CASE
WHEN (ClientID& 1)= 1
THEN 'XBusiness Academy'
ELSE ''
END
+LTRIM(
CASE
WHEN (ClientID& 2)= 2
THEN 'XPri. School'
ELSE ''
END
+LTRIM(........
)
) )),'X',', '),3,100)

Maybe this isn't the best way when you have a large table.

 

[arpan]

Can you please give me the entire SELECT statement as I can't follow what exactly you have done? What for you have used 'X' in your query? Sorry for the trouble.

Arpan

 

[AtomicWedgie]

You are asking a lot from me ;-)

SELECT DISTINCT Contacts.ContactID
,Contacts.ContactName
,Client.ClientName = SUBSTRING(REPLACE(RTRIM(LTRIM(
CASE
WHEN (ClientID& 1)= 1
THEN 'XBusiness Academy'
ELSE ''
END
+LTRIM(
CASE
WHEN (ClientID& 2)= 2
THEN 'XPri. School'
ELSE ''
END
+LTRIM(........
)
) )),'X',', '),3,100)
FROM Contacts INNER JOIN Client ON
Client.ContactID=Contacts.ContactID


I use the 'X' so that i don't have to type ', ' all the time. And when you want to change the ', ' in, for example '; ' you only have to change it in one place.

Regards,

Atomic Wedgie

 

[arpan]

Thanks for the query but it throws an error saying

Incorrect syntax near '='

pointing to the 3rd line in your query i.e. the line

,Client.ClientName = SUBSTRING(REPLACE(RTRIM(LTRIM(


Also what does the following line do?

+LTRIM(........

Regards,

Arpan

 

[AtomicWedgie]

The +LTRIM(....... means that you have to finish the query yourself by adding the other items from your table.

 

[arpan]

But what about the "Incorrect syntax near '='" error? How do I overcome it?

Regards,

Arpan

 

[AtomicWedgie]

have you completed the query yet? The error may be caused because the query wasn't complete.

 

[arpan]

The query you have provided is the complete one.....what else is needed to complete it? That is the complete query.

Arpan

 

[AtomicWedgie]

Tou still have to add the 'Sec. School' and 'Junior College' and all other values from your client table to the query

So for 'Sec. School' and 'Junior College' this would mean that you must continue where i stopped:

SELECT DISTINCT Contacts.ContactID
,Contacts.ContactName
,Client.ClientName = SUBSTRING(REPLACE(RTRIM(LTRIM(
CASE
WHEN (ClientID& 1)= 1
THEN 'XBusiness Academy'
ELSE ''
END
+LTRIM(
CASE
WHEN (ClientID& 2)= 2
THEN 'XPri. School'
ELSE ''
END
+LTRIM(
CASE
WHEN (ClientID& 3)= 3
THEN 'Sec. School'
ELSE ''
END
+LTRIM(
CASE
WHEN (ClientID& 4)= 4
THEN 'Junior College'
ELSE ''
END
------ Here you have to continue
)
)
)
)),'X',', '),3,100)
You have to add the other 3 Clients yourself.

Don't forget to add a close bracket for each opening bracket

 

[JamesLean]

The error is being thrown because you're still referencing the column name. Change to this:

SELECT DISTINCT Contacts.ContactID
,Contacts.ContactName
,ClientNames = SUBSTRING...

--James

 

[MaffewW]

hmmmm, I'm not certain this method is going to work, is it not going to just produce one row and clientname for each contact? If you get it working let us know, I'll have a try later bit busy at mo, I dont think its as easy as it sounds. BTW nicely prepared thread Arwan!

Matt

Brighton, UK

 

[AtomicWedgie]

U R correct James,

didn't see that one. Was to quick with the copy & pasting.

Regards,

Atomic Wedgie

 

[AtomicWedgie]

Forgot one thing in the example. You must add the 'X' in front of 'Sec. School' and 'Junior College'.

If you get it right it should work, no question about it.

Regards,

Atomic Wedgie

 

[arpan]

Sorry to say, Atomic, but it still produces exactly the same error pointing to the same line even after I changed the SELECT statement to what James suggested. Have you tried it out? Also the biggest hurdle is, as a programmer, I am not aware of what exactly are the records existing in the DB table. So I can't use anything like XPri. School or XBusiness Academy, XSec. School etc.....

Arpan

 

[AtomicWedgie]

Mabye you could use a sql-statement in the THEN clause

CASE
WHEN (ClientID& 1)= 1
THEN select clientname from client where clientid = 1
ELSE ''
END

and so on.

or something like that. I don't know if that would work?

Maybe you could solve the problem by using a "Bitwise comparison". Don't ask me how it works.

 

[JamesLean]

To be honest, this whole approach is not really a practical solution to the problem. Any modification to the client table requires a change to the query. Plus, as arpan says:

Also the biggest hurdle is, as a programmer, I am not aware of what exactly are the records existing in the DB table

arpan,
I would try creating a function which returns a comma-delimited list of clients given a certain contactid. Then just call that function in your select list. Unfortunately, I do not have SQL 2000 so have not tested this but try the following:

CREATE FUNCTION GetClients (@ContactID int)
RETURNS varchar(1000)
AS

DECLARE @clients varchar(1000)

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

RETURN @clients
GO

Then for your query:

SELECT contactid, contactname, GetClients(contactid) AS clients
FROM contacts

Obviously, if you also do not have SQL 2k then another approach is needed!

--James

 

[arpan]

James,

Thanks for your help. Actually I am working on SQL Server 7 & don't have SQL Server 2000 installed. So unfortunately I can't try it out. Can you suggest something else which can work in SQL Server 7.0.

Once again, thanks for your help,

Regards,

Arpan

 

[JamesLean]

OK, this script should work on SQL 7 (at least it did on my tests!). It's not great as it uses a cursor but I don't think you have much choice here.

--create temp table to hold results
CREATE TABLE #result
	(
	contactid int,
	contactname varchar(50),
	clients varchar(1000)
	)

--variables
DECLARE @cid int
DECLARE @cname varchar(50)
DECLARE @c varchar(1000)

--cursor for looping through the contacts table
DECLARE contacts CURSOR FOR
	SELECT contactid, contactname FROM contacts

OPEN contacts

FETCH NEXT FROM contacts INTO @cid, @cname

WHILE @@FETCH_STATUS = 0
BEGIN
	--get comma-delimited list of clients linked to the current contact
	SELECT @c = CASE WHEN @c IS NULL THEN clientname ELSE @c + ', ' + clientname END
	FROM client
	WHERE contactid = @cid

	--if the contact has any clients, insert the row
	--(you can comment out the IF line if you want to
	--show ALL contacts, whether they have clients or not)
	IF @c IS NOT NULL
		INSERT #result VALUES (@cid, @cname, @c)

	SET @c = NULL

	FETCH NEXT FROM contacts INTO @cid, @cname
END

--show results
SELECT * FROM #result

--tidy up
CLOSE contacts
DEALLOCATE contacts
DROP TABLE #result

--James

 

[arpan]

Hey James, can't that function along with the SQL statement be converted to a stored procedure?

Arpan