Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-06-19 : 16:19:19
|
lana writes "This is a problem that we have when we need to display records in a grid that uses a view based on a join on several tables.
For example, table1 with columns: f1,f2,f3; table2 with columns f1,f4,f5; and table3 with columns f1,f6.
The cardinality between t1 and t2 is 0 to 1 and between t1 and t3 is 0 to many
if i create a view to display everything from t1 and whatever exists in t2 or t3 the join will show f1 at least onece or as many times as there are records with f1 in table t3. I need to display only one row for each f1 value by concatenating all of f6 for the same f1 in one calculated column f7.
like so:
t1 t2 t3
f1 f2 f3 f1 f4 f5 f1 f6
1 2 3 1 6 15 1 7
2 3 4 2 3 4 1 5
3 1 1 1 3
view
f1 f2 f3 f4 f5 f6
1 2 3 6 15 7
1 2 3 6 15 5
1 2 3 6 15 3
2 3 4 3 4 NULL
3 1 1 NULL NULL NULL
This is used in searches that have to be pretty fast. And the real tables are huge.
If for the search criteria the user selects f6 ( i.e. where f6=7)
then the where clause filters out 5 and 3 but I still need to show them in the calculated column
grid
f1 f2 f3 f4 f5 f7
1 2 3 6 15 7 & 5 & 3
2 3 4 3 4 NULL
3 1 1 NULL NULL NULL
Is there a way to do it in a select statement or sp without using a cursor and looping through a recordset?
We are using SQL Server 2000
Thank you in advance.
Lana" |
|
|
|
|
|
Display search results