Home > IT Answers > SQL Server > SQL select loop list?
Vinical
5 pts.
 SQL select loop list?
SELECT, SQL 2008, SQL Select, T-SQL, T-SQL 2008
Say I want to derive the following: Select ID, F1 from table Union Select ID, F2 from table Union Select ID, F3 from table Union … etc until F10. Is there smart and easy way so that I can just provide a list: F1,F2,F3,…,F10 and somehow be able to have the select statement loop through it? Also, in addition to 2 columns, ID and Fn, I would also like a third column in the result which states the field name F1, F2,…, F3. Thank you so much.

Software/Hardware used:
SQL 08
ASKED: September 4, 2009  3:25 AM
UPDATED: September 9, 2009  7:30 PM
RELATED QUESTIONS
Answer Wiki:
Last Wiki Answer Submitted:  Be the first to answer this question.
All Answer Wiki Contributors:  Be the first to answer this question.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

let me see if I have this straight…

table with these columns :
ID
F1
F2
F3
etc

and you want a result like:
value_of_ID F1_value name_of_F1
value_of_ID F2_value name_of_F2
value_of_ID F3_value name_of_F3

answer:
SELECT ID, F1, “F1″ FROM table
UNION
SELECT ID, F2, “F2″ FROM table
UNION
etc

Part 2, can you “loop through it” by only supplying F1, F2, etc ?

Without some sort of code around your SQL, I cannot think of a way to “loop”. By some sort of code, I am referring to COBOL, REXX, or even some of the “scripting” languages.

Meandyou
 5,205 pts.
 

Dynamic SQL would be needed for that.

Something like this:

declare @counter int
declare @sql varchar(200)
	set @counter = 0
	set @sql = ''
	while @counter < 10
	begin
	  set @counter = @counter + 1
	  if @counter > 1
		set @sql = @sql + ' UNION '
	  set @sql = @sql + 'SELECT id,f'+cast(@counter as varchar(2))+',''f'+cast(@counter as varchar(2))+''' FROM table_x'
	end
	execute (@sql)
carlosdl
 63,535 pts.