Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 99
  • Last Modified:

Moving data from one table structure to different table structure

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
Morpheus7
Asked:
Morpheus7
1 Solution
 
Phillip BurtonCommented:
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
 
Scott PletcherSenior DBACommented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now