Normalizing a Crosstab

I re-designed a predecessor's database so that it is more properly
normalized. Now, I must migrate the data from the legacy system into
the new one. The problem is that one of the tables is a CROSSTAB
TABLE. Yes, the actual table is laid out in a cross-tabular fashion.
What is a good approach for moving that data into normalized tables?

This is the original table:

CREATE TABLE [dbo].[Sensitivities](
[Lab ID#] [int] NULL,
[Organism name] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[Source] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BACITRACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CEPHALOTHIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CHLORAMPHENICOL] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CLINDAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ERYTHROMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SULFISOXAZOLE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[NEOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OXACILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TETRACYCLINE] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[TOBRAMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VANCOMYCIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TRIMETHOPRIM] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CIPROFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[AMIKACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AMPICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CARBENICILLIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CEFTAZIDIME] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[GENTAMICIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[POLYMYXIN B] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MOXIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[GATIFLOXACIN] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[SENSI NOTE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

I re-designed a predecessor's database so that it is more properly
normalized. Now, I must migrate the data from the legacy system into
the new one. The problem is that one of the tables is a CROSSTAB
TABLE. Yes, the actual table is laid out in a cross-tabular fashion.
What is a good approach for moving that data into normalized tables?
>
This is the original table:
>

[snip]

What does the new structure look like? If these are drugs then I guess
you ought to use a formal coding scheme for medical terms (SNOMED for
example).

In general you can un-pivot as follows. SQL Server 2005 also has an
UNPIVOT keyword that makes things a bit easier. Look that up in Books
Online if you are using 2005.

SELECT s.LabIDNo, s.OrganismName, s.Source, c.Code, s.Sensitivity
FROM
(SELECT [Lab ID#], [Organism name], [Source],
BACITRACIN, 'BACITRACIN'
FROM dbo.Sensitivities
WHERE BACITRACIN ''
UNION ALL
SELECT [Lab ID#], [Organism name], [Source],
CEPHALOTHIN, 'CEPHALOTHIN'
FROM dbo.Sensitivities
WHERE CEPHALOTHIN ''
UNION ALL
SELECT [Lab ID#], [Organism name], [Source],
CHLORAMPHENICOL, 'CHLORAMPHENICOL'
FROM dbo.Sensitivities
WHERE CHLORAMPHENICOL ''
/* UNION ...etc */
) AS s (LabIDNo, OrganismName, Source, Sensitivity, Term)
LEFT JOIN MedicalCodes AS c
/* Note: I assume the target table won't allow nulls
LEFT JOIN guarantees you'll get an error if the code is missing
*/
ON s.Term = c.Term ;

(untested)

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Sorry, I guess the new table got cut off. Hopefully, this can help:

CREATE TABLE [dbo].[Sensitivities](
[SensiID] [smallint] IDENTITY(1,1) NOT NULL,
[AntibioticID] [smallint] NULL,
[SourceID] [smallint] NULL,
[SusceptID] [smallint] NULL,
[OrganismID] [smallint] NULL,
[SensiNote] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,|||Sorry, I guess the new table got cut off. Hopefully, this can help:

CREATE TABLE [dbo].[Sensitivities](
[SensiID] [smallint] IDENTITY(1,1) NOT NULL,
[AntibioticID] [smallint] NULL,
[SourceID] [smallint] NULL,
[SusceptID] [smallint] NULL,
[OrganismID] [smallint] NULL,
[SensiNote] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

Each ID field represents an FK except for the IDENTITY (of course). In
the old table (the crosstab), each Antibiotic is in a separate column.
In the new structure, those columns will become fields in the
Antibiotics table.|||imani_technology_spam@.yahoo.com wrote:

Quote:

Originally Posted by

Sorry, I guess the new table got cut off. Hopefully, this can help:
>
CREATE TABLE [dbo].[Sensitivities](
[SensiID] [smallint] IDENTITY(1,1) NOT NULL,
[AntibioticID] [smallint] NULL,
[SourceID] [smallint] NULL,
[SusceptID] [smallint] NULL,
[OrganismID] [smallint] NULL,
[SensiNote] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

You call that normalized!? Why make all those columns nullable? What is
the natural key supposed to be?

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||Wow, back to the drawing board.

The original table looks like this:

Lab ID | Organism Name | Source | BACITRACIN