Splitting one column into several
I have a table with one field that contains the barcode for a distinct item -- let's say they're pencils and another...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
field with defects. Each pencil can have no defects or up to 10 defects, such that the table could contain up to 10 entries per pencil. I would like to pull the data out such that I get one row per pencil along with the defect(s) for that pencil (joining table to itself?).
Table: Pencil Defect 023b5607 dented 023b5607 no graphite 023b5607 no eraser 023c5608 dented 023d5609 dented 023d5609 no eraser Results: Pencil dented no eraser no graphite 023b5607 dented no eraser no graphite 023c5608 dented 023d5609 dented no eraser
I don't want a concatenated list because I need to analyze the data using a statistical program. Each of the possible values in column 2 of the example table must have its own column in the output table.
If you're using Microsoft Access, it has a built in crosstab capability which gives you exactly what you want. If you can't use a crosstab function, here's a brute force method you can try.
Begin by declaring the final result table you want with the columns that you need. Then declare a temporary table with the same columns, to contain partial results, obtained from the original table.
create table finaltable (pencil char(8) , dented char(6), nographite char(11), noeraser char(9)) create table temptable (pencil char(8) , dented char(6), nographite char(11), noeraser char(9))
Note that the columns are designed to hold the actual defect character strings; this could be made a bit more efficient by using yes/no or 1/0 datatypes (assuming your statistical software could work with that as well).
Now populate the temporary table by selecting for each of its columns.
insert into temptable select pencil, 'dented', ' ', ' ' from origtable where defect = 'dented' insert into temptable select pencil, ' ', 'no graphite', ' ' from origtable where defect = 'no graphite' insert into temptable select pencil, ' ', ' ', 'no eraser' from origtable where defect = 'no eraser'
You can do the above in a single query if your database allows UNIONS in the INSERT SELECT syntax.
If the final result table must also include defect-free pencils, then just include another insert for the pencils alone --
insert into temptable select pencil, ' ', ' ', ' ' from origtable
Now collapse the temptable rows into the final result table using GROUP BY on the pencil, with MAX() to capture each defect -- pencils with no defect in a particular column will have a blank in that column.
insert into finaltable select pencil, max(dented), max(nographite), max(noeraser) from temptable group by pencil
Variations: With SUM() in the collapsing query, you can enhance this approach to do things like counting (using 0/1 as the datatype) and totals (using numeric values as the datatype).
For More Information
- What do you think about this answer? E-mail the edtiors at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.
Meet all of our Oracle Database / Applications experts
View all Oracle Database / Applications questions and answers
Start the conversation
0 comments