Alternative to having an aggregate concatenation function

Question

I have the following tables in SQL Server 2005

ReceiptPoint: ID (PK), Name  
GasIndexLocation: Location (PK)  
ReceiptPointIndexLocation: ReceiptPointId (FK), IndexLocation (FK), StartDate, EndDate

On any given day, a ReceiptPoint can be mapped to 1 or more IndexLocations. I need to be able to, for a given day, see what IndexLocations are being compared against. For a ReceiptPoint with multiple IndexLocations, it should show up as some kind of delimited list (for displaying to a user).

I have two problems with this.
1) ReceiptPointIndexLocation has no primary key, currently. Is the best way to handle this to just add a incrementing index field on to the table?

2) How can I get the list of IndexLocations used on a given day? From what I can tell, it would require using a non-existent string concatenation aggregate function.

Is there a totally different way I should go about organizing my data? This is still in the development stage, so I'm open to changing it.

Answer 1

I recommend adding an incrementing index to the table to use as a primary key. If you do not do so, I think SQL Server does so for you automatically behind the scenes, but I highly recommend explicitly defining the column. That way, you can refer to individual rows by the primary key instead of having to search for both cross-reference values.

Also, watch the clustering on the cross-reference table. Don't cluster on the index primary key if you add one. You probably want to cluster on both of the foreign keys, but you get to choose which foreign key one makes more sense. For example:

ALTER TABLE ReceiptPointIndexLocation 
    ADD CONSTRAINT UK_ReceiptPointIndexLocation_ReceiptPointId_IndexLocation
        UNIQUE CLUSTERED (ReceiptPointId, IndexLocation)

Alternatively, if the combination of (ReceiptPointId, IndexLocation) is unique enough, you can make that the primary key.

Answer 2

1) The primary key for ReceiptPointIndexLocation should be all four columns. However, this wouldn't stop entries with overlapping dates for the same location and receipt. There's no need for an identity column when you can identify rows with a composite key.

2) SQL:

 SELECT (SELECT t.indexlocation + ','
   FROM RECEIPTPOINTINDEXLOCATION t
  WHERE t.startdate >= @start
    AND t.enddate <= @end
FOR XML PATH(''))

Answer 3

There are several ways to aggegate string in SQL. You should read this article Concatenating Row Values in Transact-SQL. the most effective way is using the FOR XML PATH(', ') trick.