Odd design makes for odd query? 2

[oppcos]

Hi,
We had someone that was big on "object oriented" table design where there is an "X" table that basically lets you dynamically add columns to a table. So you have a table like this:

FOREIGN_KEY
FIELD_NAME
FIELD_VALUE

You then do endless left joins against the same table to get all the FIELD_NAMEs to show up as columns in your query.

I'm facing a situation now where I don't necessarily know what all the valid FIELD_NAMEs are, however, so I'm trying to create a stored procedure that selects all the distinct FIELD_NAMEs then creates a dynamic SQL statement to join them all together for me so that the resulting query's table has one column for each valid FIELD_NAME.

Unfortunately, I'm a newbie at tsql and although I know how to get the distinct FIELD_NAMEs into a variable table and I know you can use EXEC to execute dynamic SQL within an SP, I don't know how to build the SQL statement in tsql. I think it will involve some sort of loop, but does anyone have any pointers on how I should go about this?

Thanks!

P.S. I'm using SQL 2005

 

[ptheriault]

Use a while loop while @@rowcount > 0. Declare a variable and keep appending your results to it.

DECLARE @row varchar(max)

SET @row = 'SELECT '

WHILE @@rowncount > 0
SELECT @row = FIELD_NAME+ ','+ @row
FROM table

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[oppcos]

Great, I have wide gaps in my knowledge and @@rowncount was one of them. Thanks!

 

[oppcos]

Well, upon having tried that in a number of variations I was unable to get it to work. I think I'm going to have to go figure out cursors even though I have tried to avoid them because I heard they are bad for performance. If you still think there's a way to do it with WHILE @@ROWCOUNT > 0 I'd love to hear it though. The code above just seems to be an infinite loop for some reason though.

 

[ESquared]

You only need to execute the select statement once. Why are you looping?

DECLARE @SQL varchar(max)

SELECT @SQL = IsNull(@SQL + ',', 'SELECT ') + FIELD_NAME 
FROM table 

PRINT @SQL
EXEC (@SQL)

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]

 

[oppcos]

Well.. because.. 'cause you're making it too easy without a loop!