Re: Query to combine two 'many:one' tables
From: Vishal Parkar <vgparkar_at_hotmail.com>
Date: Fri, 29 Aug 2003 08:14:16 -0600
Message-ID: <O21IVfjbDHA.2548_at_TK2MSFTNGP09.phx.gbl>
drop table #tmp
create table #tmp(id int, tmpval varchar(50)) go
Date: Fri, 29 Aug 2003 08:14:16 -0600
Message-ID: <O21IVfjbDHA.2548_at_TK2MSFTNGP09.phx.gbl>
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 _at_id int, @old_id int declare _at_seq_num int declare _at_cats varchar(50), @f_cats varchar(50)select _at_id=0, @old_id=0,_at_seq_num=0, @cats='', @f_cats=''
declare c1 cursor for
select id, cats from tab order by id
open c1
fetch c1 into _at_id,_at_cats
while _at__at_fetch_status = 0
begin
If _at_old_id <> @id and @old_id <> 0
begin
insert into #tmp values(_at_old_id, @f_cats)
select _at_seq_num=0, @f_cats=''
end
select _at_f_cats = @f_cats + case @f_cats when '' then '' else ',' end + @cats
select _at_old_id = @id
fetch c1 into _at_id,_at_cats
end
close c1
deallocate c1
insert into #tmp values(_at_old_id, @f_cats)
select * from #tmp
-- -Vishal "Jon Maz" <jonmaz_at_NOSPAM.surfeu.de> wrote in message news:binks7$mdj$1_at_online.de...Received on Fri Aug 29 2003 - 16:14:16 CEST
> Hi,
>
> I'm hoping someone can help me with an SQL query (SQL Server 2000).
>
> There is a many:one 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]
> ) ,
> )
>
>
>
>
>
>
>
>
>