Web Development

Ask something
Discussion Locked

SQL grouping

0 helpful
13 replies

by john_wills 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I have a table with 3 columns; the 2nd column tells what the third one means. The 1st column identifies a person. It might look ike this:
identifier meaning value
Bloggs drunk yes
Bloggs age 39
Bloggs sex refused
Samuel sex male
Samuel IQ 98
Samuel DSM IV 202.7
I want to insert this information into a table with such columns as
identifier drunk-value age sex IQ DSM
Bloggs yes 39 refused null null
Samuel nullnull male 98 202.7
I am too thick to work this out myself, so someone please tell me the SQL to use.

13 replies

by nikki96

The syntax is dependent on the language you are using, if any. For example, if you are using PHP, ColdFusion, PLSQL, or any other application to insert the data, the syntax of variable use will be different.

Pure SQL, like at a sqlplus prompt forOracle, might look like this.
This is Oracle syntax. YMMV, depending on DBMS.

INSERT INTO myTbl (identifier, sex, IQ, DSM) VALUES ('Samuel', 'male', 98, 202.7);

Values not inserted are null by default.

However, this is very simplistic and not that common. More often than not, you'll be using some sort of program to enter these values. Which one are you using? ASP, PHP, Access, etc...? And which DBMS? mySQL, Oracle, etc...?

by john_wills

The problem is not to insert known values more or less by hand but to transform a table with several rows per client to one with one row per client. On possible way is:
Insert into NewTable(identifier)
Select Distinct identifier
From OldTable;
Update NewTable JOIN OldTable on identifier
Set NewTable.Age=OldTable.Value WHERE OldTable.Meaning="Age";
but I am disinclined to do this because it necessitates a separate Update for each kind of value, and I have 30-odd; I would really like a multiple Set with multiple Where clauses.
My database is in Access 2000.

by john_wills

TRANSFORM Last(OldTable.Value)
SELECT OldTable.Identifier, Last(OldTable.Value) AS FirstNewColumn
FROM OldTable
GROUP BY OldTable.Identifier
PIVOT OldTable.Meaning;
This is possible in the Microsoft JET extension of SQL, but it puts an extra column in, I am not sure how to send the output to the destination table, the order of the columns is not fixed(and I do not necessarily want all meanings for the application, the column names are not quite as I want, and I do not see how to do some transformations I want(e.g. text to yes/no) on some of the variables.

by bob sellman

Iyou attach to your database through ODBC and use an Access front end you might try building a cross tab query using the identifier as the Y axis and your second column as the second axis. (Use the Access cross tab query builder wizard.)

The result should be a table with data something like you listed. If the cross tab query works in Access, then use it as the source to make a new table. Then go into the new table's definition and change the column names to match those in your destinationtable. You can then use your ODBC connection to insert the values into your destination table or just import the table into your database.

If your first column is called Person, the second What, and the third Value in a table called StartTable, the resulting SQL for the query would be something like this:

TRANSFORM Count(StartTable.Value) AS [The Value]
SELECT StartTable.Person
FROM StartTable
GROUP BY StartTable.Person
PIVOT StartTable.What;

The basic idea is that the 2nd column will be the column name for all columns except the first column (the person's name) in the resulting crosstab query.

If you are using SQL Server, you may be able to do the same thing directly in SQL Server, probably using the Enterprise manager.

by bob sellman

As far as your questions in your comment, just do what I suggest above. After you get the data in the basic format you want, go in to the definition of the result (destination) table and change the column names to what you need. You can also then insert new columns with different types (ex: yes/no). Save that and then set up and run update queries that make the new column equal to the value in an existing column. The yes/no will probably work OK from the text yes/no. Numbers and dates willjust need conversion functions (look up functions in help).

Once this is successfull, go back into the table definition and delete the old columns.

But before you even run the cross tab query, if your data is not consistent (for example: somerows have "sex, male", while others say "sex, M", and others say "sexx, male"), run queries to update the values in a specific column to standard values. To find out what is not standard, set up a grouping query on your original table that groups by all three columns. So you will then see any values or 2nd column names that are non-standard.

by john_wills

The references to ODBC and SQL Server seem irrelevant; most of what you say is in my earlier comment. This is not a one-time operation but something to be embedded in a form to send the information for use elsewhere, with references to other tables, and issuing in a report. You do not explain how the output from the CrossTab is to get into a table for further manipulation, which should be programmed, not done by hand. What I am actually doing, partly because the destination columns have t obe of different types, is creating the array mostly empty and updating with JOIN for each column in turn, using a different SQL statement for each type of destination column.

by bob sellman

OK, if this is on ongoing operation, DUMP YOUR CURRENT TABLE! Create an input table that matches the layout of the table where the data will reside. You input form should display all of the fields with whatever are the appropriate options or answers for each field (when not just a fill in the blank text field). After the user enters the new data, just append your input table data to the permanent data table.

If you want to let users edit data but do it away from the permanent data table, just copy the appropriate record to your input table, let the user edit the data, then update the permanent table by replacing it's data (other than whatever is the primary key field) with the new/revised data.

The table you descirbe for input is absolutely useless for an effective system. Your question sounded like you were trying to convert from an older poorly designed table into one that made sense. Don't continue using such a piece of crap.

by john_wills

I do not choose the large-scale software for which I have to devise extensions. The "poorly designed table" is meant for interactive definiton of new events when the user(the whole health department of this City & County, in my case; also many other county health departments) wants something more than the vendor regularly suplies. That interactive definition is already done, and now I have to extract reports. I am not doing badly, actually, but I do not have the kind of single transformation I would like at SQL level.

by dsa

SELECT Sample.Identifier, Max(IIf([Name]="Drunk",[Value],Null)) AS Drunk, Max(IIf([Name]="Age",[Value],Null)) AS Age, Max(IIf([Name]="Sex",[Value],Null)) AS Sex, Max(IIf([Name]="IQ",[Value],Null)) AS IQ, Max(IIf([Name]="DSM",[Value],Null)) AS DSM
FROM Sample
GROUP BY Sample.Identifier;

This is the SQL statement using Access. This is practical only if you have a limited number of Name field. If you do not want the hard coding of the Name field, then you can create a Name table with just one field that will have all the possible 2nd column values.

by john_wills

I do have rather a lot of columns to fill, so this is not practical. What is more, I suspect it would give me a separate record for each column, though I will have to think that through. My technique of updating for each destination column is working, and quite well at that, because I have only a small (few hundred) number of destination rows; but it would be interesting to find a solution which would be practical for larger quantitites of data.

by d-ced

If I were doing this in Oracle (may apply to other DBs) I would do the following:
1) Extract the data to a tilde (~) delimited file
2) Load the data back into a table fitting the new format you require.

I will explain step 1 below:

SELECT name,
max(drunk_val),
max(age_val),
max(sex_val)
FROM ( SELECT name,
DECODE ( meaning, 'drunk', value, NULL ) drunk_val,
DECODE ( meaning, 'age', value, NULL ) age_val,
DECODE ( meaning, 'sex', value, NULL ) sex_val
FROM original_table ) working_table
GROUP BY name;

The subquery will create a table of rows where the value is placed in the appropriate column, then the main query will group those values together and bring them to a unique record per customer.

If you are not using oracle, then find the appropriate statement to replace DECODE.

Hope this works for you!

by john_wills

This last answer, if I understand it, is the best yet. I will investigate whether it works in JET, and if not what parallel might. In practice I am still using my multiple update in VBA.

This discussion is closed to new replies.