Loop Through Columns Sql

March 21, 2024 Post a Comment

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 OPTI

Solution 1:

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

SELECT  
  MyName + " ->"
  +case OPTION1 when1then' OPTION1'else''end+case OPTION2 when1then' OPTION2'else''end+ ...
FROMTable

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.

Solution 2:

You might want to have a look at PIVOT Tables.

Solution 3:

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.

Solution 4:

You could build a dynamic statement using the system catalog:

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

Solution 5:

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 NOTEXISTS( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME      
 ='TABLE1' ) 
createtable table1
(
   name nvarchar(50),       
   colvalue nvarchar(50)
)
elsetruncatetable table1

declare@table nvarchar(50)
set@table='yourtable'declare@columntable
(
   ID integeridentity,
   colname nvarchar(20)
)


insertinto@columnSELECT c.name FROM sys.tables t 
JOIN sys.columns c ON t.Object_ID = c.Object_ID 
WHERE t.Name =@tableand c.name in ('Option1','Option2','Option3','Option4','Option5')

declare@minIDinteger, @maxIDintegerdeclare@cmd nvarchar(max)  
declare@col nvarchar(20)
declare@SQLStr nvarchar(max)

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

while @minID<=@maxIDbeginselect@col= colname
    from@columnwhere ID =@minIDset@SQLStr='insert into table1 (name, colvalue)
    select name,'+@col+'
    from '+@table+' 
    where '+@col+' <> 0'exec(@SQLStr)

    set@minID=@minID+1endselectdistinct name, STUFF(
(SELECT','+ a.colvalue  AS [text()]
from Table1  a
where a.name = b.name
Orderby a.colvalue
for xml PATH('')),1,1,''    ) AS Comments_Concatenated
from Table1 b
groupby name, colvalue
ORDERBY 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.

Lab Sql