Loop through columns SQL

Question

Hello everyone,

I'm looking for a way to loop through the columns of a table to generate an output as described below.

The table looks like that:

ID  Name     OPTION1 OPTION2 OPTION3 OPTION4 OPTION5
1   MyName1  1       0       1       1       0
2   MyName2  0       0       1       0       0

And the output looks like that:

MyName1 -> OPTION1, OPTION3, OPTION4
MyName2 -> OPTION3

Any directions of doing this simply would be greatly appreciated. Otherwise, I suppose I'll have to use a cursor or a temporary table... The database engine is MSSQL. The reason I'm doing formatting at the database level is to feed its output into a limited programmable environment.

Update: the ouput can by in any form, a string or rows of strings.

Update: Would the be a way to accomplish that by building a string using @str = @str + ... ?

Update: I changed the output... this should be easier.

Thanks!

Answer 1

Edit:

Is there a way to create a MSSQL function to “join” multiple rows from a subquery into a single delimited field?

Search for various solutions to previous questions

Answer 2

You might want to have a look at PIVOT Tables.

Answer 3

Well, in case of a known number of columns, you can do:

SELECT  
  MyName + " ->"
  + case OPTION1 when 1 then ' OPTION1' else '' end
  + case OPTION2 when 1 then ' OPTION2' else '' end
  + ...
FROM
 Table

If columns are unknown when you create the query - I'd probably still go that way with some dynamically created SQL. The advantage is that the code probably does what you wants and is very simple.

Answer 4

You could build a dynamic statement using the system catalog:

http://msdn.microsoft.com/en-us/library/ms189082.aspx

Answer 5

Since you don't go into the specific needs of why you want to be able to do this I can't be certain, but usually when I see this kind of question there are two things that I think of:

1. You need to normalize your database. Maybe "Option1", "Option2" etc. have nothing in common, but there is also a good chance that they are a repeating group within your table.

2. Handle display issues in the display layer of your application - i.e. the front end, not the database.

As I said, maybe these don't apply in your case for some specific reason, but it seems like it from what I've read of your question.