Hi,

I'm hoping someone can help me with an SQL query (SQL Server 2000).

There is a manyne relationship between my Cases table and my Clients table
(a Client can have n Cases). I'd like to combine the Cases and Clients
table to give the following result set:

++++++++++++++++++++
TARGET RESULT SET 1
++++++++++++++++++++

COL1 COL2 COL3 COL4 COL5
ClientID ClientName CaseID#1 CaseID#2 CaseID#3
------------------------------------------------------------
1 Smith 2 4 16
2 Jones 3 7 <NULL>
3 Williams 14 18 24



I'd also be interested to know how I would produce this result set:

++++++++++++++++++++
TARGET RESULT SET 2
++++++++++++++++++++

COL1 COL2 COL3
ClientID ClientName CaseID's
--------------------------------------
1 Smith 2,4,16
2 Jones 3,7
3 Williams 14,18,24


The tables themselves are related via the Cases.ClientID field (as below).

Any help appreciated,

TIA,

JON

__________________________________________________ _____________


CREATE TABLE [Cases] (
[CaseID] [int] IDENTITY (1, 1) NOT NULL ,
[ClientID] [int] NOT NULL ,
CONSTRAINT [PK_Cases] PRIMARY KEY CLUSTERED
(
[CaseID]
) ,
CONSTRAINT [FK_Cases_Clients] FOREIGN KEY
(
[ClientID]
) REFERENCES [Clients] (
[ClientID]
)


CREATE TABLE [Clients] (
[ClientID] [int] IDENTITY (1, 1) NOT NULL ,
CONSTRAINT [PK_Clients] PRIMARY KEY CLUSTERED
(
[ClientID]
) ,
)

First case: Either you write dynamic SQL (because you don´t know how many
cases will be the maximum for one Client), or you use the script from this
article

http://groups.google.de/groups?q=%22...0&hl=de&lr =&
ie=UTF-8&oe=UTF-8&scoring=d&selm=e9tnQHVbDHA.2136%40TK2MSFTNGP10.p hx.gbl&rnu
m=14

it had the same problem described like you did.

HTH, Jens Süßmeyer.

With T-SQL you can have code something like this:
Ex:
drop table tab
create table tab(ID int,
cats varchar(50))
go
insert into tab values(1 ,'1')
insert into tab values(1 ,'2')
insert into tab values(1 ,'3')
insert into tab values(2 ,'1')
insert into tab values(3 ,'1')
insert into tab values(3 ,'2')
go
drop table #tmp
create table #tmp(id int, tmpval varchar(50))
go
declare @id int, @old_id int
declare @seq_num int
declare @cats varchar(50), @f_cats varchar(50)
select @id=0, @old_id=0,@seq_num=0, @cats='', @f_cats=''

declare c1 cursor for
select id, cats from tab order by id

open c1

fetch c1 into @id,@cats

while @@fetch_status = 0
begin

If @old_id <> @id and @old_id <> 0
begin
insert into #tmp values(@old_id, @f_cats)
select @seq_num=0, @f_cats=''
end
select @f_cats = @f_cats + case @f_cats when '' then '' else ',' end + @cats
select @old_id = @id
fetch c1 into @id,@cats
end
close c1
deallocate c1
insert into #tmp values(@old_id, @f_cats)
select * from #tmp


--
-Vishal

"Jon Maz" <jonmaz (AT) NOSPAM (DOT) surfeu.de> wrote

Try a GO between the INSERTS and the CREATE FUNCTION, that´ll work.

HTH, Jens Süßmeyer.

Hi All,

Thanks for the help so far! By plundering code from the thread Jens
referred me to, I'm managing to make some (slow) progress, and have produced
some SQL which works ("NEW QUERY ON CASES TABLE", below).

I now need to bolt this query on to a pre-existing query of the Clients
table (also below), and am struggling to do this. Can anyone help me join
these two queries?

Much appreciated,

JON


****************************************
PRE-EXISTING QUERY ON CLIENTS TABLE
****************************************

SELECT Clients.*, ClientTypes.ClientType
FROM tblClients Clients
LEFT OUTER JOIN tblClientTypes ClientTypes ON
Clients.ClientTypeID = ClientTypes.ClientTypeID


******************************
NEW QUERY ON CASES TABLE (needs to be joined somehow to the above query)
******************************

SELECT ClientID, CAST(MIN(CASE seq WHEN 1 THEN CaseID END) As VarChar) +
COALESCE(',' + CAST(MIN(CASE seq WHEN 2 THEN CaseID END) As VarChar), '') +
COALESCE(',' + CAST(MIN(CASE seq WHEN 3 THEN CaseID END) As VarChar), '') +
COALESCE(',' + CAST(MIN(CASE seq WHEN 4 THEN CaseID END) As VarChar), '') +
COALESCE(',' + CAST(MIN(CASE seq WHEN 5 THEN CaseID END) As VarChar), '')
AS CaseIDs
FROM
(
SELECT Cases1.ClientID, Cases1.CaseID, COUNT(*) AS seq
FROM tblCases AS Cases1
LEFT JOIN tblCases AS Cases2 ON
Cases1.ClientID = Cases2.ClientID AND Cases1.CaseID >= Cases2.CaseID
GROUP BY Cases1.ClientID, Cases1.CaseID
)
AS S
GROUP BY ClientID



*******************
TARGET RESULT SET
*******************


ClientID ClientType CaseIDs
=============================
1 P 1,3,5
2 P 2,4
3 C <NULL>

So post some DDL and Example Dat and i try to fix this query for you.

HTH, Jens Süßmeyer.

Hi Jens,

Here's some DDL / Data for you to play with!

Cheers,

JON

__________________________________________________ ________________

IF OBJECT_id ('tblCases') IS NOT NULL DROP TABLE tblCases
CREATE TABLE tblCases (CaseID INTEGER, ClientID INTEGER, PRIMARY KEY
(CaseID))

INSERT INTO tblCases VALUES (1, 1)
INSERT INTO tblCases VALUES (2, 1)
INSERT INTO tblCases VALUES (3, 1)
INSERT INTO tblCases VALUES (4, 2)
INSERT INTO tblCases VALUES (5, 2)
INSERT INTO tblCases VALUES (6, 3)
INSERT INTO tblCases VALUES (7, 4)
INSERT INTO tblCases VALUES (8, 4)
INSERT INTO tblCases VALUES (9, 4)
INSERT INTO tblCases VALUES (10,4)
INSERT INTO tblCases VALUES (11,4)
INSERT INTO tblCases VALUES (12,6)


IF OBJECT_id ('tblClients') IS NOT NULL DROP TABLE tblClients
CREATE TABLE tblClients (ClientID INTEGER, ClientTypeID INTEGER, PRIMARY KEY
(ClientID))

INSERT INTO tblClients VALUES (1, 1)
INSERT INTO tblClients VALUES (2, 1)
INSERT INTO tblClients VALUES (3, 2)
INSERT INTO tblClients VALUES (4, 2)
INSERT INTO tblClients VALUES (5, 2)
INSERT INTO tblClients VALUES (6, 1)


IF OBJECT_id ('tblClientTypes') IS NOT NULL DROP TABLE tblClientTypes
CREATE TABLE tblClientTypes (ClientTypeID INTEGER, ClientType VARCHAR(50),
PRIMARY KEY (ClientTypeID))

INSERT INTO tblClientTypes VALUES (1, 'Person')
INSERT INTO tblClientTypes VALUES (2, 'Company')


ALTER TABLE [dbo].[tblCases] ADD
CONSTRAINT [FK_tblCases_tblClients] FOREIGN KEY ([ClientID]) REFERENCES
[dbo].[tblClients] ([ClientID])
GO

ALTER TABLE [dbo].[tblClients] ADD
CONSTRAINT [FK_tblClients_tblClientTypes] FOREIGN KEY ([ClientTypeID])
REFERENCES [dbo].[tblClientTypes] ([ClientTypeID])
GO



++++++++++++++++++
TARGET RESULT SET
++++++++++++++++++

ClientID ClientType CaseID's
--------------------------------------------
1 Person 1,2,3
2 Person 4,5
3 Company 6
4 Company 7,8,9,10,11
5 Company <NULL>
6 Person 12

Hallo Jon !

Here is the answer to your question:

SELECT CL.ClientID,CT.ClientType,dbo.fn_Myfunc(ClientID) as Cases
FROM
(
SELECT DISTINCT ClientID,CLientTypeID FROM tblClients
)AS CL
INNER JOIN tblClientTypes CT
ON
CT.CLientTypeID=CL.CLientTypeID


ClientID ClientType Cases
----------- -------------------------------------------------- -------------
-----------------
1 Person 1,2,3
2 Person 4,5
3 Company 6
4 Company 7,8,9,10,11
5 Company NULL
6 Person 12

(6 row(s) affected)


HTH, Jens Süßmeyer.

Hi All,

Following a bit of off-NG-emailing, Jens has produced a solution (below) -
many thanks!

JON



**********
SOLUTION
**********

IF OBJECT_id ('fn_Myfunc') IS NOT NULL DROP FUNCTION fn_Myfunc
GO

CREATE FUNCTION fn_Myfunc(@COL int)
RETURNS VARCHAR(30)
AS
BEGIN
DECLARE @W VARCHAR(30)
DECLARE @CASEID INT
SET @W = ''
SELECT @W = @W + CAST(CaseID AS VARCHAR(10)) + ',' FROM tblCases WHERE
ClientID=@COL
SELECT @CASEID=CASEID FROM tblCases WHERE ClientID=@COL
If @CASEID IS NOT NULL
BEGIN
SET @W = LEFT(@W,LEN(@W)-1)
END
RETURN @W
END
GO

SELECT CL.ClientID, CT.ClientType, dbo.fn_Myfunc(ClientID) as Cases
FROM (SELECT DISTINCT ClientID,ClientTypeID FROM tblClients) AS CL
INNER JOIN tblClientTypes CT ON CT.CLientTypeID=CL.CLientTypeID