I have a table with one field that contains the barcode for a distinct item -- let's say they're pencils and another 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.
This was first published in February 2002