Sql

[zy_abc]

Table 1

File_ID                 File_Name            
F001                     File 1
F002                     File 2

Table 2

File_ID                   Version
F001                        1
F001                        2
F001                        3
F002                        1
F002                        2 
F002                        3

 

Combining Table 1 and Table 2 i would like to get the result as follows:

 

File_ID                    Version
F001                      1,2,3
F002                      1,2,3

 

Is it possible?

[Nerseus]

Yes, but not easily. To combine multiple rows into a single field separated by commas requires writing a cursor to get the data for each child row. You can loop through each row, add the commas, and add it as a string column to the parent resultset. You can simplify it by putting the cursor in a user defined function (in SQL Server), and using that function in your SELECT, passing in the File_ID.

 

Now, if you have control of the child table and know that there will always be 3 versions, you could re-create that table to have 3 columns (version1, version2, and version3). Or, if you need them as separate rows, add another column like Sequence:

Table 2

File_ID  Seq             Version
F001      1                 1
F001      2                 2
F001      3                 3
F002      1                 1
F002      2                 2 
F002      3                 3

 

Then you could join to the child table 3 times (using an alias for each join), and use the sequence to grab each of the three rows. That would allow you to have 0, 1, 2, or 3 rows and piece them together in one column. This would limit you to a max of 3 rows (or whatever you define the max to be), but can't be dynamic (you can't easily allow 4 rows later - you'd have to change your proc).

 

If you want dynamic, you'll have to use the cursor.

 

-Nerseus

[zy_abc]

Thanks Nersus for your reply. I am using MS-Access as the back end and ASP.NET as front-end. What i am trying to acheive is to get all the version numbers of the File ID's. Is it possible in MS-Access? Please help me out. I will be thankful if you can provide me a sample code,any links or any tips. Edited May 14, 2003 by zy_abc

[Nerseus]

Like I said, I think your best bet is to redesign how you're storing your information. If it really is file information, why not store the whole version number as one string or as 4 numbers. If you store things as multiple columns in one row it's MUCH easier to piece together than multiple rows into one column.

 

Who's building and filling your database? If it's you, I'd re-do the table structure. If it's someone else... well, have them do - it looks like a bad design from what I can see.

 

-Nerseus

[zy_abc]

I have done slight modifications in the designing part. It is working like a charm now. I thought the above problem is solvable. Thanks for ur patient reply.