Solved

Moving data from one table structure to different table structure

Posted on 2015-01-29
2
69 Views
Last Modified: 2015-02-02
Hi,

We currently have the following table with the following columns:

AnimalID OrderNo, TestA TestA_Result TestB TestB_Result TestC TestC_Result

I would like to be able to move the data to a table that has one row per test, as opposed to the current one where there is one row per animal
 Like so

AnimalID OrderNo   TestA        testAResult
12345        09890      TestA        Neg
12345        09890      TestC        Pos
12345        09890      TestC       IC

All fields are VARCHAR or CHAR

Any assistance would be appreciated

Thanks
0
Comment
Question by:Morpheus7
2 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40576981
Select AnimalID, OrderNo, TestA, TestA_Result
From myTable
UNION ALL
Select AnimalID, OrderNo, TestB, TestB_Result
From myTable
UNION ALL
Select AnimalID, OrderNo, TestC, TestC_Result
From myTable
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 500 total points
ID: 40577730
SELECT at.AnimalID, at.OrderNo,
    CASE whichTest
        WHEN 'A' THEN TestA
        WHEN 'B' THEN TestB
        WHEN 'C' THEN TestC
    END AS Test,
    CASE whichTest
        WHEN 'A' THEN TestA_Result
        WHEN 'B' THEN TestB_Result
        WHEN 'C' THEN TestC_Result
    END AS TestResult
FROM animal_tests at
CROSS JOIN (
    SELECT 'A' AS whichTest UNION ALL
    SELECT 'B' UNION ALL
    SELECT 'C'
) AS whichTests
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now