Post reply

Denormalising Data

  • September 8, 2014 at 6:20 am

    #290499

    Hi, I am having to move data from a table in one database to another. The data in the first table is normalised properly but in the target table it is not.

    Source table:

    Exam_id

    Od_Reading

    Os_reading

    date_taken

    time_taken

    Target Table:

    Exam_id

    od_reading1

    od_reading2

    od_reading3

    os_reading1

    os_reading2

    os_reading3

    So for each exam in the source table, I need to get at most the 3 first readings (for each of od_reading and os_reading) and stick them into the target fields of od_reading1, 2 & 3 and os_reading1, 2 & 3.

    Here's a data example to show you more clearly:

    SourceTable:

    Exam_id OD_Reading OS_Reading Date_Taken Time_Taken

    1 12 13 2014-04-25 2014-04-25 10:24:01

    1 11 14 2014-04-25 2014-04-25 10:24:05

    2 20 21 2014-04-25 2014-04-25 11:04:01

    2 21 22 2014-04-25 2014-04-25 11:05:01

    2 20 19 2014-04-25 2014-04-25 11:06:01

    2 22 20 2014-04-25 2014-04-25 11:07:01

    TargetTable:

    Exam_id OD_Reading1 OD_Reading2 OD_Reading3 OS_Reading1 OS_Reading2 OS_Reading3

    1 12 11 13 14

    2 20 21 20 21 22 19

    What's the best way to achieve this? Bear in mind there are a couple thousand records and this is a one-off import.

  • September 8, 2014 at 11:40 am

    #1743934

    My guess would be to create a PIVOT

    This article walks you through it:

    http://www.sqlservercentral.com/articles/pivot/62808/%5B/url%5D

    Then once you have that, you should be able to append it to your destination table.

  • September 8, 2014 at 2:30 pm

    #1743958

    Here's another article that you might want to check out. It explains PIVOTs and the wonders of an "ancient" tool known as a CROSSTAB.

    http://www.sqlservercentral.com/articles/T-SQL/63681/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 8, 2014 at 3:19 pm

    #1743969

    Here's a fairly primitive way to do it. Possibly not brilliant performance, but if you are only going to run it once....

    First, some code to create the source and target tables and populate the source with some test data

    --source table definition

    create table Source (Exam_id int not null

    , OD_reading int not null

    , OS_reading int not null

    , [date] date not null

    , time datetime2 not null

    , primary key (Exam_id,date,time));

    go

    -- insert test data

    insert Source values

    (1, 12, 13, '2014-04-25', '2014-04-25 10:24:01'),

    (1, 11, 14, '2014-04-25', '2014-04-25 10:24:05'),

    (2, 20, 21, '2014-04-25', '2014-04-25 11:04:01'),

    (2, 21, 22, '2014-04-25', '2014-04-25 11:05:01'),

    (2, 20, 19, '2014-04-25', '2014-04-25 11:06:01'),

    (2, 22, 20, '2014-04-25', '2014-04-25 11:07:01');

    go

    -- Target table definition

    create table Target (Exam_id int primary key

    , OD_Reading1 int not null

    , OD_Reading2 int null

    , OD_Reading3 int null

    , OS_Reading1 int not null

    , OS_Reading2 int null

    , OS_Reading3 int null);

    go

    Next the code to process the data and insert the result into the Target table: you will need to change the column names here to whatever the real ones are.

    with number as (select Exam_id

    , row_number() over (partition by exam_id order by ([time])) NUM

    , OD_reading

    , OS_Reading

    from Source )

    INSERT Target SELECT A.Exam_id

    , A.OD_reading

    , B.OD_reading -- or IsNULL(B.Od_reading, '') if you want zero length string instead of NULL

    , C.OD_reading -- or IsNULL(C.Od_reading, '') if you want zero length string instead of NULL

    , A.OS_reading

    , B.OS_Reading -- or IsNULL(B.Os_reading, '') if you want zero length string instead of NULL

    , C.OS_Reading -- or IsNULL(C.Os_reading, '') if you want zero length string instead of NULL

    FROM (select * from number where NUM = 1) A

    left join (select * from number where NUM = 2) B ON A.Exam_id = B.Exam_id

    left join (select * from number where NUM = 3) C ON A.Exam_id = C.Exam_id

    ;

    Tom

  • September 9, 2014 at 2:47 am

    #1744042

    Thanks PietLinden & Jeff, I had been looking at the PIVOT but most examples seemed to expect you to know the name of the columns you were going to get out (in the link you posted I need to know the names of the months), but in my case I don't know all the unique times.

  • September 9, 2014 at 3:19 am

    #1744050

    Thanks Tom, using the CTE worked for me

  • September 9, 2014 at 8:36 am

    #1744147

    Here's an option to read the table once based on Tom's post and cross tabs.

    with number as (select Exam_id

    , row_number() over (partition by exam_id order by ([time])) NUM

    , OD_reading

    , OS_Reading

    from Source )

    INSERT Target

    SELECT Exam_id

    , MAX( CASE WHEN NUM = 1 THEN OD_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 2 THEN OD_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 3 THEN OD_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 1 THEN OS_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 2 THEN OS_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 3 THEN OS_reading ELSE '' END)

    FROM number

    GROUP BY Exam_id

    ;

    If you want to make it dynamic, you could read the second article from Jeff on cross tabs.

    http://www.sqlservercentral.com/articles/cross+tab/65048/

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 9, 2014 at 5:45 pm

    #1744278

    Luis Cazares (9/9/2014)

    Here's an option to read the table once based on Tom's post and cross tabs.

    with number as (select Exam_id

    , row_number() over (partition by exam_id order by ([time])) NUM

    , OD_reading

    , OS_Reading

    from Source )

    INSERT Target

    SELECT Exam_id

    , MAX( CASE WHEN NUM = 1 THEN OD_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 2 THEN OD_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 3 THEN OD_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 1 THEN OS_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 2 THEN OS_reading ELSE '' END)

    , MAX( CASE WHEN NUM = 3 THEN OS_reading ELSE '' END)

    FROM number

    GROUP BY Exam_id

    ;

    If you want to make it dynamic, you could read the second article from Jeff on cross tabs.

    http://www.sqlservercentral.com/articles/cross+tab/65048/

    That's a nice simplification - I wish I had thought of doing it that way.

    Tom

  • September 9, 2014 at 7:31 pm

    #1744289

    Thanks Luis

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply