循环通过列SQL [英] Loop through columns SQL

问题描述

我们正在寻找一种循环表格列来生成输出的方法，如下所示：

 
 ID名称OPTION1 OPTION2 OPTION3 OPTION4 OPTION5 
 1 MyName1 1 0 1 1 0 
 2 MyName2 0 0 1 0 0 
 

输出如下：

 
 MyName1 - > OPTION1，OPTION3，OPTION4 
 MyName2  - > OPTION3 
 

这样做的任何方向都将非常感谢。否则，我想我必须使用游标或临时表...数据库引擎是MSSQL。我在数据库级别进行格式化的原因是将其输出馈送到有限的可编程环境中。

更新：ouput可以

更新：这将是一个方法来完成这一点，使用@str构建一个字符串= @str + ...？

更新：我更改了输出...这应该更容易。

谢谢！

解决方案

：

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

如果在创建查询时未知列 - 我可能仍然这样一些动态创建的SQL。优点是代码可能做你想要的，很简单。

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!

解决方案

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.

这篇关于循环通过列SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！