473,322 Members | 1,781 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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]

Aug 24 '06 #1
5 1820
im*******************@yahoo.com wrote:
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/m...S,SQL.90).aspx
--

Aug 24 '06 #2
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,

Aug 24 '06 #3
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.

Aug 24 '06 #4
im*******************@yahoo.com wrote:
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/m...S,SQL.90).aspx
--

Aug 24 '06 #5
Wow, back to the drawing board.

The original table looks like this:

Lab ID | Organism Name | Source | BACITRACIN

Aug 24 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
4
by: Judy | last post by:
I'm using Access 2003 and was wondering if it is possible to have a paramater selection within a crosstab query so that I wouldn't need to build a new table. I have a select query that I'm using...
14
by: Tina | last post by:
My employer tracks productivity/performance of clinicians (how much they bill) each week, its averages for the month, and the 6 months. These averages are compared to their expected productivity....
8
by: Richard Hollenbeck | last post by:
I have a recipe database that I've been building but I haven't yet put any of the ingredients in because of a little problem of normalization. If I build a table of ingredients, all the recipes...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.