SQL select loop list?

5 pts.
Tags:
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

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Meandyou
    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.
    5,220 pointsBadges:
    report
  • carlosdl
    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)
    67,965 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following