更新时间:2023-10-12 23:32:16
:
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.