Sql

Z

zy_abc

Well-known member
Joined
May 2, 2003
Messages
67
Code: 
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:

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

Is it possible?
 
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:
Code: 
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 cant be dynamic (you cant easily allow 4 rows later - youd have to change your proc).

If you want dynamic, youll have to use the cursor.

-Nerseus
 
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 IDs. 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.
 
Last edited by a moderator:
Like I said, I think your best bet is to redesign how youre 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 its MUCH easier to piece together than multiple rows into one column.

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

-Nerseus
 
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.
 
Back
Top