Denormalising Data

Question

Post reply

Denormalising Data

Frank Cazabon

Hall of Fame

Points: 3431
More actions
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.

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

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

pietlinden SSC Guru Points: 63428 More actions · Answer 1

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.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 2

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)

TomThomson SSC Guru Points: 104773 More actions · Answer 3

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

Frank Cazabon Hall of Fame Points: 3431 More actions · Answer 4

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.

Frank Cazabon Hall of Fame Points: 3431 More actions · Answer 5

Frank Cazabon

Hall of Fame

Points: 3431

September 9, 2014 at 3:19 am

#1744050

Thanks Tom, using the CTE worked for me

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

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

TomThomson SSC Guru Points: 104773 More actions · Answer 7

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

Frank Cazabon Hall of Fame Points: 3431 More actions · Answer 8

Frank Cazabon

Hall of Fame

Points: 3431

September 9, 2014 at 7:31 pm

#1744289

Thanks Luis