Get Result according to comma seperated sequence

[Rupesh Kumar Swami]

Hi All, I want to get the result according to comma seperated value in field. First let me explain the table and their data I have one table which contain following fields and data file sequence || Cat_Id || Sequence 4,1,5,8,9,10,20 || 41 || 2 18,17,19,24,25 || 48 || 9 19,21,22,23,24,25,26 || 49 || 10 above is the sample data structure which contains the data in above format. Now I want to create a query which rerurn the value of file_sequence field in following format 4,1,5,8,9,10,20,18,17,19,24,25,21,22,26 Here one thing Which I want to notify 1. above table is result of a query which is sorted on the base of Sequence 2. Now we pick the result of field_sequence from the start in the same order. 3. now we pick the 4,1,5,8,9,10,20 then 18,17,19,24,25 and then 21,22,26 4. duplicate value removed (in above example 19,24,25 are duplicate) so in this way I want to get the comma seperated value 4,1,5,8,9,10,20,18,17,19,24,25,21,22,26 Please suggest/guide how can i achieve this result

rup28aug@yahoo.co.in Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) Company - ISOL, India Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11

[Jorgen Andersson]

Maybe not the answer you're looking for, but your table doesn't even conform to the first normal form. Normalize it, for example like this:

CREATE TABLE MyTable
([fileid] int, [filesequence] int, [catid] int, [catsequence] int)
;

INSERT INTO MyTable
([fileid], [filesequence], [catid], [catsequence])
VALUES
(4, 1, 41, 2),
(1, 2, 41, 2),
(5, 3, 41, 2),
(8, 4, 41, 2),
(9, 5, 41, 2),
(10, 6, 41, 2),
(20, 7, 41, 2),
(18, 1, 48, 9),
(17, 2, 48, 9),
(19, 3, 48, 9),
(24, 4, 48, 9),
(25, 5, 48, 9),
(19, 1, 49, 10),
(21, 2, 49, 10),
(22, 3, 49, 10),
(23, 4, 49, 10),
(24, 5, 49, 10),
(25, 6, 49, 10),
(26, 7, 49, 10)
;

Now you can Query it like this:

select fileid
from mytable
group by fileid
order by min(catsequence),min(filesequence)

And the result will look like this:

FILEID
4
1
5
8
9
10
20
18
19
17
24
25
21
22
23
26

It's easy enough to make that into a comma separated file if you would really need to. But that task actually belongs to another layer than the database.

Wrong is evil and must be defeated. - Jeff Ello[^]