SQL select loop list?
Home > IT Answers > SQL Server > SQL select loop list?
Vinical
5 pts.
0
Q:
SQL select loop list?
SELECT, SQL Select, T-SQL, T-SQL 2008, 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: Sep 4 2009  3:25 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Vinical
5 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
Last Answered: Sep 4 2009  3:25 AM GMT by Vinical   5 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Meandyou   1840 pts.  |   Sep 9 2009  3:53PM GMT

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.

 

Carlosdl   29820 pts.  |   Sep 9 2009  7:30PM GMT

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)

 
0