Loop through columns SQL

Question

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!

Do you have a fixed number of columns? (also "Option4 -> MyName1" ?) — Dead account
– Dead account, Commented Mar 30, 2009 at 14:18
I think its unclear what the output should be. Do you want to take a table and generate text strings? A "pivot table" solution would result in a resultset or another table. — J. Polfer
– J. Polfer, Commented Mar 30, 2009 at 14:38
I've updated again. The output can be in any form, as a string or rows of strings. — Wadih M.
– Wadih M., Commented Mar 30, 2009 at 14:44

user76035 · Accepted Answer · 2009-03-30 15:43:40Z

4

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.

answered Mar 30, 2009 at 15:43

user76035

1,5461 gold badge10 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Boker · Accepted Answer · 2009-03-30 14:16:27Z

3

You might want to have a look at PIVOT Tables.

answered Mar 30, 2009 at 14:16

John Boker

83.8k17 gold badges101 silver badges130 bronze badges

1 Comment

Wadih M. Over a year ago

Right, pivot tables should be usable. Checking to see if there are other fresh ideas about a methodology I'm not aware of.

Tom H · Accepted Answer · 2009-03-30 15:54:08Z

2

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:

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.
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.

answered Mar 30, 2009 at 15:54

Tom H

47.5k15 gold badges90 silver badges133 bronze badges

2 Comments

Wadih M. Over a year ago

Right, Tom. The reason I'm trying to do formatting at the database level is to be able to feed the output directly in a report where I don't have much scripting flexibility.

Tom H Over a year ago

Yep, that would be one of those cases where this kind of thing becomes necessary. Carry on. ;)

nearly_lunchtime · Accepted Answer · 2009-03-30 15:45:41Z

1

You could build a dynamic statement using the system catalog:

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

answered Mar 30, 2009 at 15:45

nearly_lunchtime

13.1k15 gold badges41 silver badges43 bronze badges

Comments

Andrew Barber · Accepted Answer · 2012-09-28 06:21:19Z

If using pivot table, you must make sure all of your "Option" columns have the same data type and length.

I would suggest the following answer:

IF NOT EXISTS( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME      
 = 'TABLE1' ) 
create table table1
(
   name nvarchar(50),       
   colvalue nvarchar(50)
)
else
  truncate table table1

declare @table nvarchar(50)
set @table = 'yourtable'

declare @column table
(
   ID integer identity,
   colname nvarchar(20)
)


insert into @column
SELECT c.name FROM sys.tables t 
JOIN sys.columns c ON t.Object_ID = c.Object_ID 
WHERE t.Name = @table 
and c.name in ('Option1','Option2','Option3','Option4','Option5')

declare @minID integer, @maxID integer
declare @cmd nvarchar(max)  
declare @col nvarchar(20)
declare @SQLStr nvarchar(max)

select @minID = MIN(ID), @maxID= MAX(ID)
from @column

while @minID <= @maxID
begin
    select @col = colname
    from @column
    where ID = @minID

    set @SQLStr =    
    'insert into table1 (name, colvalue)
    select name,' + @col + '
    from ' + @table + ' 
    where ' + @col + ' <> 0'    

    exec(@SQLStr)

    set @minID = @minID + 1
end

select distinct name, STUFF(
(SELECT  ',' + a.colvalue  AS [text()]
from Table1  a
where a.name = b.name
Order by a.colvalue
for xml PATH('')),1,1,''    ) AS Comments_Concatenated
from Table1 b
group by name, colvalue
ORDER BY name

You just have to modify the @table by putting in your table name and the list of the column you need before insret into @column.

No matter what data type you are, it will working fine.

Community · Accepted Answer · 2017-05-23 11:55:43Z

-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

edited May 23, 2017 at 11:55

CommunityBot

11 silver badge

answered Mar 30, 2009 at 14:12

gbn

434k84 gold badges602 silver badges690 bronze badges

Collectives™ on Stack Overflow

Loop through columns SQL

6 Answers 6

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

1 Comment

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related