June 22, 2012 at 7:34 pm
Sean Lange (6/22/2012)
SQL_Kills (6/22/2012)
Hi,Thanks for your advice so far and what you have shown me.
The table you created QuestionFieldsNormalized was what the table originally looked like that when pulling the data from the Source Table, the reason I have it like the table "QuestionFields" is because when joing to another table it would only show one line and not multiple lines if I was to join using the QuestionFieldsNormalized table.
So I take it a function cannot be createde with the way I have done my new table structure?
I would guess there was something else going on there preventing you from getting the data the way you wanted it. The real problem with the not normalized version is look closely at the query dwain wrote. If you need to add an 11th you have to modify the table and the function (and any other code you have pulling data from this). The way I put it together you can have any number of mapped fields and the code will work with no modification.
The code I wrote or the code Dwain wrote will both work in a function with a little modification. Why are you so determined to turn this into a scalar function? The queries that we both posted don't use a scalar function and both return the data you said you wanted in your first post.
I would HIGHLY recommend a more normalized route but you are the person who has to support this. In the end it is your system and your code you have to support, so the decision is yours.
I didn't realize the OP was wanting to do a scalar function! I was thinking a TVF. I agree the scalar function version would probably be a dog.
And to the OP (I'm sure Sean already knows this), all my query did was normalize the data as a first step in the fashion that Sean was suggesting you store it in to begin with (or something similar).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
June 25, 2012 at 7:01 am
dwain.c (6/22/2012)
Sean Lange (6/22/2012)
SQL_Kills (6/22/2012)
Hi,Thanks for your advice so far and what you have shown me.
The table you created QuestionFieldsNormalized was what the table originally looked like that when pulling the data from the Source Table, the reason I have it like the table "QuestionFields" is because when joing to another table it would only show one line and not multiple lines if I was to join using the QuestionFieldsNormalized table.
So I take it a function cannot be createde with the way I have done my new table structure?
I would guess there was something else going on there preventing you from getting the data the way you wanted it. The real problem with the not normalized version is look closely at the query dwain wrote. If you need to add an 11th you have to modify the table and the function (and any other code you have pulling data from this). The way I put it together you can have any number of mapped fields and the code will work with no modification.
The code I wrote or the code Dwain wrote will both work in a function with a little modification. Why are you so determined to turn this into a scalar function? The queries that we both posted don't use a scalar function and both return the data you said you wanted in your first post.
I would HIGHLY recommend a more normalized route but you are the person who has to support this. In the end it is your system and your code you have to support, so the decision is yours.
I didn't realize the OP was wanting to do a scalar function! I was thinking a TVF. I agree the scalar function version would probably be a dog.
And to the OP (I'm sure Sean already knows this), all my query did was normalize the data as a first step in the fashion that Sean was suggesting you store it in to begin with (or something similar).
Actually I think the OP wanted a scalar function because they didn't know this data could be pulled like we showed them. 😛
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2012 at 7:36 am
Hi, what's OP mean?
June 25, 2012 at 7:39 am
SQL_Kills (6/25/2012)
Hi, what's OP mean?
OP = Original Poster. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2012 at 7:40 am
The reason why it is not like the table how you did it, is because if you join to another table then the report would show many lines.
June 25, 2012 at 8:06 am
SQL_Kills (6/25/2012)
The reason why it is not like the table how you did it, is because if you join to another table then the report would show many lines.
And as I said before that is likely due to an issue with the query. You should never sacrifice good design to make it easier to retrieve your data. Poor design will cause you nothing but grief in the long run. My guess is that your query against against the normalized data could have been modified to return the results you were looking for.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 25, 2012 at 9:49 am
But when I report against these tables I am not using free-hand sql, I am using BI Products like Business Objects etc..which won't let me create Common table expressions when creating the universe.
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply