Re: SQL Query Help
From: Uri Dimant (urid_at_iscar.co.il)
Date: 06/10/04
- Next message: Jeff Cochran: "Re: How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server"
- Previous message: David Portas: "Re: insert trigger help"
- In reply to: CJM: "SQL Query Help"
- Next in thread: CJM: "Re: SQL Query Help"
- Reply: CJM: "Re: SQL Query Help"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 10 Jun 2004 14:50:52 +0200
CJM
This is an idea, modify it for your needs
create table w
(
id int,
t varchar(50)
)
insert into w values (1,'abc')
insert into w values (1,'def')
insert into w values (1,'ghi')
insert into w values (2,'ABC')
insert into w values (2,'DEF')
select * from w
create function dbo.fn_my ( @id int)
returns varchar(100)
as
begin
declare @w varchar(100)
set @w=''
select @w=@w+t+',' from w where id=@id
return @w
end
select id,
dbo.fn_my (dd.id)
from
(
select distinct id from w
)
as dd
drop function dbo.fn_my
"CJM" <cjmwork@yahoo.co.uk> wrote in message
news:%23$EaP4tTEHA.3476@tk2msftngp13.phx.gbl...
> I am try to build an SP that queries two table, Filters and Locations.
>
> Each filter is in one location only. I want to list all the locations, and
> for each location, list the PartNo and the number of filters in that
> location, eg:
>
> PartNo, Location1, Location2, Location3
> -----------------------------------------------------
> PartA, 10, 20, 30
> PartB, 5, 5, 5
> PartC, 0, 5, 10
> etc
>
> Filters:
> CREATE TABLE [dbo].[Filters] (
> [SerialNo] [int] NOT NULL ,
> [PartNo] [varchar] (10) COLLATE Latin1_General_CI_AS NOT NULL ,
> [LocationID] [int] NOT NULL ,
> [CustomerID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> Locations (snipped):
> CREATE TABLE [dbo].[Locations] (
> [LocationID] [int] IDENTITY (1, 1) NOT NULL ,
> [LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [CustomerID] [int] NOT NULL ,
> [Town] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
> [TelNo] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
> [FaxNo] [varchar] (15) COLLATE Latin1_General_CI_AS NULL ,
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>
> Can somebody give me a start on how to contruct this query?
>
> Thanks
>
> Chris
>
>
>
>
- Next message: Jeff Cochran: "Re: How to read an SQL Server into a ASP page and then change, add, delete and write it back to SQL Server"
- Previous message: David Portas: "Re: insert trigger help"
- In reply to: CJM: "SQL Query Help"
- Next in thread: CJM: "Re: SQL Query Help"
- Reply: CJM: "Re: SQL Query Help"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
