Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
richardwaugh
Starting Member
36 Posts |
 Posted - 2014-08-11 : 08:57:58
|
| Hello everyone,I've tried researching this topic and am unable to find a suitable situation to what I am facing at the moment. I am tasked with creating a query that obtains all of the data from a form in our database. Our database stores the information in the following columns: CaseFormID, FieldName, FieldValue and CaseFormDataID.The form in question has 2 add buttons which repeat the variables but with a specific set of text added at the end. For example one of the fields is called "ChildName". If the worker clicks the add button the database will have 2 entries for "ChildName". One FieldName will be "ChildName" and the other field name will be "ChildNameAdd_0". Clicking add again would then add a FieldName of "ChildNameAdd_1", and so on.What complicates things is that I need to return all of the values of the form in separate columns. So currently I have a very lengthy query that returns the result of each row as a column (using the pivot function hasn't worked out for me yet, although I am still trying).I know I can easily just use a UNION and get copy and paste my query, changing the variables from ChildName to ChildNameAdd_0, but doing this would create an extremely long query as I don't know how many times someone may click the add button. And I need all of the results returned in a way that if the add button is pressed the returned results are in a new a row.Any suggestions? |
|
|
Mar
Starting Member
47 Posts |
Posted - 2014-08-19 : 08:53:32
|
| Yes. Pick one thing and work on that. When done move to the next. You mention many things and are vague about them. Sounds like you need to spec out what you need to do. You say "creating a query that obtains all of the data from a form in our database" which is confusing. A query (usually) either gets data from a database or puts data in a database. A form gets information from a query or gives information to a query. I have never heard of putting a form in the database, thats not saying it cant be done, but I suspect you are trying to cram too much into one sentence to the point it becomes confusing to anyone but yourself. Then you say "The form in question has 2" but I have not seen question 2, nor question 1. The next puzzling thing is "repeat the variables" because variables no not repeat. If you are repeating fieldnames then that is a poor design, you should split the table at that point. Take your time, learn the terms you are using and start slow and do these things in this order:1) Explain what you are trying to do, the goal of your code.2) Explain how you are trying to do this, show code samples.3) State your problem or question. |
 |
|
|
richardwaugh
Starting Member
36 Posts |
Posted - 2014-08-19 : 09:11:43
|
| Some of your suggestions are valid...some are not. Regardless, they get me thinking a little bit more with regards to this question.With regards to explaining how I am doing it currently I do state that I am doing a union, which changes that length of my code from 1200 lines to well over 7000. Execution time suffers a little bit but I am running the reports from a replicated server so there is no impact on end users. You are correct in that there is a lot going on with this post and I am in the process of re-working everything so that it is more clear. Keeping in line with the typical on site requirements, the question was posted very quickly as it was a very urgent matter, which has fizzled to a trickle now and allows me to take my time and examine more closely.Thank you for replying to this post :) |
|
|
|
|
|
|
|
|
While Loop Inside a Select Statement