How to get comma separated values and list down

[piupiu]

Hi All,

I have a database table like:

Supplier Product
Company A A,B,C,D
Company B B,D,G
Company C G,A
Company D A,F,B,C
...... .........(unlimited rows)
Now I want to list down all the values in Product separated by comma.

Expected result:
Product
A
B
C
D
G
F
......

Please help how to do it. I know there is a loop needed. But how to do? Very anxious about that, because it is my final year project..
Thank you very much...

 

[speakers_86]

So you may have a product listed more than once? If so, why not just use the group by function? Though, your design seems off. But maybe it's because you didn't give us much.

 

[piupiu]

Thanks for your reply.
No, I just want to list down all the products in the column separated by comma and the products may be repeated. I think there should be a loop to scan all the rows in the column and find commas.
The group by is used after all the products is collected, then I will do a filter to delete multiple values.
For example, the field name is Product, the rows in the field column is unlimited which means I can add anything to the datasheet at anytime. This is the datasheet:
Row Product
1 A,B,C,D
2 B,S,D
3 A,W
The result I want is to list all the products in the column even they are separated by commas.
Expected result:
Product
A
B
C
D
B
S
D
A
W
So all the values will be listed down...If the row is blank which means there is no product in the row, it will skip it.

Thanks.

 

[speakers_86]

Row Product
1 A,B,C,D
2 B,S,D
3 A,W

I don't think you are understanding how a relational db works. You need to look up normalization. A table in a relational db is different that a spreadsheet, although they may look similar. Your different products will not be in the same row like that separated with commas. The design might look more like this:

tblCompany
CompanyID
CompanyName

tblProducts
ProductID
CompanyID
ProductName


Notice how the company id is stored next to every product. This way, you can make a query that will bring both tables together without storing the information more than once. If the same product can be gotten from different companies, as you seem to be saying, then it would be more like this:

tblCompany
CompanyID
CompanyName

tblProducts
ProductID
ProductName

tblCompanyProduct
CompanyProductID
CompanyID
ProductID