Select Statement 1

[stroyer74]

Looking for help on a SQL select statement. I'm using MS SQL 2000.

Table1 includes
field 1 as int: ID
field 2 as int: DrawingID
field 3 as varchar: AffectedModel

The same DrawingID may be listed multiple times with a different AffectedModel listed each time. I would like to do a select statement that retrieves distinct DrawingIDs and combines the AffectedModels into one string.

For example, the table data may include:

ID DrawingID AffectedModel
---------------------------------
1 1234 Model1
2 1234 Model2


The select statement would return

DrawingID AffectedModel
----------------------------
1234 Model1, Model2



Part two of the question is that the end user of this would like it to determine how many AffectedModels are involved for each DrawingID. If the count is greater than 2, return "See Index" instead of the actual models.

For example, the table data may include:

ID DrawingID AffectedModel
---------------------------------
1 1234 Model1
2 1234 Model2
3 4321 Model9
4 4321 Model8
5 4321 Model7


The select statement would return

DrawingID AffectedModel
----------------------------
1234 Model1, Model2
4321 See Index


Any ideas? Thanks in advance,
Sheldon

 

[nigelrivett]

1. use a function to concatenate
see

http://www.nigelrivett.net/CSVStringFromTableEntries.html

2.
select id, case when count(*) > 2 then 'see index' else dbo.csvtbl(id) end
from tbl
group by id


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

 

[stroyer74]

Many thanks nigelrivett! That helped explain the concept I was trying to figure out. From your example, it was pretty easy to move it over to my database and make it work with my live data.

Thanks again,
Sheldon