|
|
Posted by David Portas on 10/02/25 11:56
imani_technology_spam@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/ms130214(en-US,SQL.90).aspx
--
Navigation:
[Reply to this message]
|