Nalza Nalza - 1 year ago 47
SQL Question

SQL Query/Linq : How to get data from mutiple column with specific criteria to feed to dropdownlist

|Distict | Gold | Silver | Bronze |
|--------|--------|--------|----------|
|A-------|Y-------|0-------|Y---------|
|B-------|0------ |Y-------|0---------|
|C-------|0------ |0-------|Y---------|
|D-------|Y-------|0-------|0---------|


I want to bind a dropdownlist to a specific row based on the column that got Y in the field

i.e: if district A got all medal (noted with Y), the dropdownlist will get Gold, Silver and Bronze in the list items, else as in the example the dropdown wil have only gold and bronze.

Answer Source

You can do this by unpivoting the data

SELECT distict, 
       NAME 
FROM   yourtable 
       CROSS apply (VALUES ('Gold',gold), 
                           ('Silver',silver), 
                           ('Bronze',bronze)) cs (NAME, value) 
WHERE  value = 'Y'