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 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.