Normalizing a Crosstab

Giganews Newsgroups
Subject: Normalizing a Crosstab
Posted by:  imani_technology_spam@yahoo.com (imani_technology_spam@yahoo.com)
Date: 24 Aug 2006

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]

Replies