Prev: OPENDATASOURCE reads file even if conditions not met?
Next: Will this newsgroup be cancelled ?
From: Drew on 21 Jul 2010 04:24
I have a database that keeps up with trip information and who attended
the trip, the structure is,

CREATE TABLE [dbo].[Trips] (
[TripID] [int] IDENTITY (1, 1) NOT NULL ,
[TripDate] [datetime] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[RLU] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TripPlace] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[TripPurpose] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TripAttendees] (
[UID] [int] IDENTITY (1, 1) NOT NULL ,
[TripID] [int] NULL ,
[RegNo] [int] NULL
) ON [PRIMARY]
GO

Running a query like this (selstar used for testing only),

SELECT *
FROM Trips T INNER JOIN TripAttendees TA ON T.TripID = TA.TripID
WHERE TripID = 22

returns a record for each attendee who attended the trip. For
example,

TripID - TripDate - StartTime - EndTime - RLU - TripPlace -
TripPurpose - RegNo
22 - 1/1/2010 - 1:00PM - 5:00PM - 6B - Mall - Trip to mall for
shopping - 100
22 - 1/1/2010 - 1:00PM - 5:00PM - 6B - Mall - Trip to mall for
shopping - 101
22 - 1/1/2010 - 1:00PM - 5:00PM - 6B - Mall - Trip to mall for
shopping - 102
22 - 1/1/2010 - 1:00PM - 5:00PM - 6B - Mall - Trip to mall for
shopping - 103

Instead, I would like to return one record, with a comma-delimited
list of the RegNo who attended, for example

TripID - TripDate - StartTime - EndTime - RLU - TripPlace -
TripPurpose - RegNo
22 - 1/1/2010 - 1:00PM - 5:00PM - 6B - Mall - Trip to mall for
shopping - 100, 101, 102, 103

If someone could explain this, it would be wonderful. I am a little
rusty on my SQL, as I have not done a lot (besides maintaining
existing dbs) for a while now. I am using SQL Server 2000.

Thanks,
Drew
From: Plamen Ratchev on 21 Jul 2010 11:19
This is easily done on SQL Server 2005/2008, and more difficult on SQL
Server 2000. See the following article for some methods that work on
SQL Server 2000:
http://www.projectdmx.com/tsql/rowconcatenate.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
From: Drew on 22 Jul 2010 02:49
On Jul 21, 11:19 am, Plamen Ratchev <Pla...(a)SQLStudio.com> wrote:
> This is easily done on SQL Server 2005/2008, and more difficult on SQL
> Server 2000. See the following article for some methods that work on
> SQL Server 2000:http://www.projectdmx.com/tsql/rowconcatenate.aspx
>
> --
> Plamen Ratchevhttp://www.SQLStudio.com

Plamen,

Thanks for your reply. I wish I did have SQL Server 2005/2008, but
alas I do not. The solutions you have on your site are great, but the
SQL Server 2000 solutions (UDF) just take too long to execute on my
large dataset, so I have rethought this and decided to do the
concatenation processing by the client instead of using T-SQL to
accomplish it.

Thanks!
Drew
From: Erland Sommarskog on 27 Jul 2010 04:12
Drew (dr00bert(a)gmail.com) writes:
> This solution requires SQL Server 2005... I appreciate the reply, but
> this just won't work for my configuration.

The options for SQL 2000 are unfortunately quite dire. Essentially, you will
have to run a cursor. Or produce the comma-separated lists client-side.
See also http://www.projectdmx.com/tsql/rowconcatenate.aspx.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: OPENDATASOURCE reads file even if conditions not met?
Next: Will this newsgroup be cancelled ?