|
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor. |
Welcome to the p2p.wrox.com Forums.
You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
|
|
|
June 23rd, 2003, 09:06 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
|
|
Concatenate multiple records from table to single
I have multiple records in a table pertaining to a single user:
e.g. -
ID Value
01 ABC
01 DEF
01 GHI
02 Etc. <-Note new user ID
I'd like to produce a view (not a table) using strictly SQL code (no other programming languages involved - I can create a new table using VB if I must, but I prefer a view on the server) which will produce something that looks like this:
ID Value1 Value2 Value3
01 ABC DEF GHI
Can this be done strictly in SQL to produce a view or am I going to have to write code and create a table?
|
June 23rd, 2003, 10:50 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
|
|
This is really a presentation issue and not a data issue. One can argue that your attempt to repeat the Value rows as columns violates first normal form. Your table is correctly normalized; why would you want to produce a query which violates that?
What if a given ID had a zillion Value rows? Constructing a query as you request would eventually fail as you exceed the number of columns allowed in a SELECT statement.
You could try something like:
Code:
SELECT ID,
MAX(CASE WHEN Value='ABC' THEN Value ELSE NULL END) as Value1,
MAX(CASE WHEN Value='DEF' THEN Value ELSE NULL END) as Value2,
MAX(CASE WHEN Value='GHI' THEN Value ELSE NULL END) as Value3,
...
FROM YourTable
GROUP BY ID
ORDER BY ID;
but this probably isn't quite what you want, as the 'Value1' column will only have the value 'ABC' or null, so if a given ID doesn't have an 'ABC' row, then it's 'value1' column would be null. This way also requires that you know the possible values of the 'Value' column ahead of time and you've decided which column you want to report a given value in.
Do it in your client.
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com
|
June 23rd, 2003, 11:21 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
|
|
Jeff,
Thanks for your reply.
This is a presentation issue in a sense - I want to output this data in a variety of reports. Data this normalized doesn't seem to lend itself to easy reference by report generators like BRIO. The most important issue is that end users will want to generate ad-hoc reports using the Access report generator or BRIO. These reports need to compare multiple records for the same user across multiple dates (i.e. - tell me if the values entered on different dates for the name field show improvement, but print it on one line of the report). In some instances, I might want all fo the values for different dates on the same line, in other instances I might want all the answers for different questions for the same date on the same line. The users seem most likely to be able to use this data if it appears to them as though it's a single record per user AND datestamp (thus alleviating concerns about the possible number of columns/row).
And while the suggested code may work, the limitations mentioned render it unworkable. The solution I'm looking for shouldn't need to be changed every time the users dream up a new field or value. Otherwise I might as well write a VB6 program to create a new table from the existing one with the characteristics needed.
|
June 23rd, 2003, 12:15 PM
|
Authorized User
|
|
Join Date: Jun 2003
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I doubt this is exactly what your looking for, and it's ver inefficient, but it might help.
First of all you need some unique field in your table that enumerates every row. For this code that field is called PKey.
SELECT T1.ID, T1.value Value1, T2.value Value2, T3.value Value3
FROM testTable T1, testTable T2, testTable T3
WHERE T1.ID = T2.ID AND T1.ID = T3.ID AND
T1.PKey < T2.PKey AND T2.PKey < T3.PKey
This makes 2 major assumptions that are probably invalid.
1) All ID's have the same number of rows. You could add rows with null for the value in order to pad ID's that have fewer rows.
2) you know what that number is (in this case I wrote it for 3, you would have to keep joining the table to itself if there were more rows per ID.)
If you make too many joins the overhead will be prohibitive to running this query. I doubt this is the answer but maybe it'll give you an idea.
-Mike
|
June 23rd, 2003, 02:01 PM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
|
|
Mike,
Thanks for your response.
Unfortunately, this suggestion won't work, and for the reason you guessed: I don't know how many rows there might be. I can guarantee that for any given query, all the records returned would have the same number of fields, so there'd be no reason to create records with null values. However, this database will contain results from a variety of surveys, some containing a handful of questions and others containing possibly hundreds of questions. I'd be doing nothing but writing massive joins at the whim of our end users to combine just the right set of questions for each query they want. I can create a VB tool to do that for them and dump data to a local Access database - I was just hoping to be able to do it in SQL and avoid the VB code.
Thanks again for your response, but it's beginning to look like my only choice is to start coding.
|
August 20th, 2004, 03:36 PM
|
Friend of Wrox
|
|
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts
|
|
I don't think you have fully described or perhaps thought out what you plan.
You gave the following:
ID Value
01 ABC
01 DEF
01 GHI
02 Etc.
What if you also have 04 xyz, is that yet another column. This could stretch out to a very large number of columns. Assuming you want this as yet another column you can do it using temp tables but its hairy and cludgy. But here is what I would do. Select distinct on the value column into a temp table. Write a cursor to cursor through the result set adding a column for every entry in your temp table. then you have the structure to later select. Then right yet another cursor to populate the data. Then select * from your cursor created temp table with cursor populated data. This is messy but will work I can send you some sample code if you like for MS-SQL though. The code for this does NOT follow into ansi-92 standards in any way shape or form and this would have to be writen DBMS specific. If you want the code email me at s p a m r o b p r e l l at y a h o o . c _ _
Cheers,
Rob
|
August 23rd, 2004, 08:49 AM
|
Friend of Wrox
|
|
Join Date: Jun 2003
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
|
|
Rob,
At first glance my query may seem not to make sense. It may help to consider that my desire for this specific solution was fueled by a wish to avoid being tied to this project for the rest of my career creating new views, reports, etc., at the end users' whims. Dammit Jim, I'm a developer, not a maintainer! :D
Regarding your concerns, I can only assure you that a very large number of columns is not possible in this situation. Simply put, no end user has the time or desire to respond to a survey containing thousands of questions, nor for filling out the same survey thousands of times, so the results are necessarily brief. I can also refer you to actual data gathered over the last 6 months or so, where the largest logically combinable number of columns has not exceeded 100 (although there may be thousands of such rows produced, of course, based on how the data is selected for combination). There may yet be more complex surveys, but one with thousands of questions would never be completed by any user. Concerns about potential size are therefore minimized.
In any case, as this question came up several months ago, the issue is moot at this point. I was offered a solution that produced a view containing the information desired. It was, unfortunately inflexible, as it had to be coded to produce a specific subset of data, and a new view would have to be coded for each subset desired, while I wished for a generic solution that would encompass all subsets possible.
In the end, I turned to code, which as I said earlier, I wished to avoid but appeared inevitable. I've linked an Access database via ODBC to my SQL Server database. A couple of Access forms allow the users to determine the subset of information they wish to report (which survey and which questions from the survey) and using meta data also in the primary database, VBA code constructs the necessary Access table containing the denormalized data (and sometimes appeasing the gods of normalized database design is not the most important issue). This solution eliminates any possibility of returning an unlimited number of columns because the data can only be combined in approved logical ways, and also pretty much takes care of any efficiency issues as well by producing tables containing only the columns needed or a specific report. Running it to download the data may take a little while, but with the results stored locally, at least there's no further impact to the server during reporting. The end users can then create any desired reports using the Access report engine, export the data to Excel, or do whatever else it is that end users do without having to come to me every time they need to do it. That takes me out of the picture whenever a user gets a new idea for a report, which as I mentioned at the top, was my big concern. :)
|
|
|