June 2, 2009 at 4:55 pm
Hi All
I have a problem thats driving me nuts and I need some fresh ideas.
I need an efficient way to update data in a table that looks like this:
SubjectDateHour0_A Hour1_A Hour2_A Hour3_A Hour4_A Hour5_A Hour6_A etc...
from a table that looks like this:
SubjectDate HourDataADataB
aaa1/1/2009 012121234
aaa1/1/2009 12343242
aaa1/1/2009 23445435
aaa1/1/2009 31227504
aaa1/1/2009 4-1009605
aaa1/1/2009 5-32211705
aaa1/1/2009 6-54413806
aaa1/1/20097-76615906
aaa1/1/20098-98818007
aaa1/1/20099-121020107
aaa1/1/200910-143222208
aaa1/1/200911-165424308
aaa1/1/200912-187626409
aaa1/1/200913-209828509
aaa1/1/200914-232030610
aaa1/1/200915-254232710
aaa1/1/200916-276434811
aaa1/1/200917-298636911
aaa1/1/200918-320839012
aaa1/1/200919-343041112
aaa1/1/200920-365243213
aaa1/1/200921-387445313
aaa1/1/200922-409647414
aaa1/1/200923-431849514
Sorry for the crude explanation of the problem. Basically the source has a row for every hour and the destination has a field for each hour in a single row. Efficiency is a concern so using a cursor is probably last resort.
Any help would be greatly appreciated as I'm fresh out of school and working as the sole programmer/DBA for a small company so ... nobody to bounce things off of.
Thanks in advance.
June 2, 2009 at 5:46 pm
you didn't explain what the issue is.
is it you need to populate a table with each hour? you said "update" the table, not populate, so i'm not sure where the issue lies.....
Lowell
June 3, 2009 at 9:47 am
Sorry,
A little background may help. The actual source data is stored in an encrypted file. The stored procedure I am writing calls an executable that pulls the data for a given date range out of the file into a .txt file then bulk loads it to a temporary table and compares the the aggregated data. (all working so far). If the aggregated data does not match I will have the SP load/update the destination table with the data from the temp table so that the encrypted file contents and the destination table match.
The problem I am having is how do I transform the data from multiple rows that have hourly data (1 row per hour) into a single row in the destination table with a field for each hour. The temp table will has multiple days worth of data for several "subjects". My first inclination is to set up a cursor for the hours but I'm hoping there is a more elegant way to do this.
Any help is greatly appreciated.
June 3, 2009 at 10:38 am
ok i got it, and a solution too!
what you want to do is use the PIVOT operator;
with that, you can PIVOT a single column based on the values of the HOUR column.
since you have TWO columns you need to pivot(DATAA and DATAB), as far as i know you will need to do do TWO updates fro the PIVOT results, one for dataA and and a second from DATAB.
try this code and confirm this is doing what you want.
I aliased the DataA values As [DA00],[DA01], etc.
CREATE TABLE #EXAMPLE(
Subject VARCHAR(30),
Date datetime,
Hour int,
DataA decimal(18,8),
DataB decimal(18,8) )
INSERT INTO #EXAMPLE
SELECT 'aaa','1/1/2009', 0 , 1212,1234 UNION ALL
SELECT 'aaa','1/1/2009', 1 , 234,3242 UNION ALL
SELECT 'aaa','1/1/2009', 2 , 344,5435 UNION ALL
SELECT 'aaa','1/1/2009', 3 , 122,7504 UNION ALL
SELECT 'aaa','1/1/2009', 4 , -100,9605 UNION ALL
SELECT 'aaa','1/1/2009', 5 , -322,11705 UNION ALL
SELECT 'aaa','1/1/2009', 6 , -544,13806 UNION ALL
SELECT 'aaa','1/1/2009', 7 , -766,15906 UNION ALL
SELECT 'aaa','1/1/2009', 8 , -988,18007 UNION ALL
SELECT 'aaa','1/1/2009', 9 ,-1210,20107 UNION ALL
SELECT 'aaa','1/1/2009', 10,-1432,22208 UNION ALL
SELECT 'aaa','1/1/2009', 11,-1654,24308 UNION ALL
SELECT 'aaa','1/1/2009', 12,-1876,26409 UNION ALL
SELECT 'aaa','1/1/2009', 13,-2098,28509 UNION ALL
SELECT 'aaa','1/1/2009', 14,-2320,30610 UNION ALL
SELECT 'aaa','1/1/2009', 15,-2542,32710 UNION ALL
SELECT 'aaa','1/1/2009', 16,-2764,34811 UNION ALL
SELECT 'aaa','1/1/2009', 17,-2986,36911 UNION ALL
SELECT 'aaa','1/1/2009', 18,-3208,39012 UNION ALL
SELECT 'aaa','1/1/2009', 19,-3430,41112 UNION ALL
SELECT 'aaa','1/1/2009', 20,-3652,43213 UNION ALL
SELECT 'aaa','1/1/2009', 21,-3874,45313 UNION ALL
SELECT 'aaa','1/1/2009', 22,-4096,47414 UNION ALL
SELECT 'aaa','1/1/2009', 23,-4318,49514 UNION ALL
SELECT 'aaa','1/2/2009', 0 , 1212,1234 UNION ALL
SELECT 'aaa','1/2/2009', 1 , 234,3242 UNION ALL
SELECT 'aaa','1/2/2009', 2 , 344,5435 UNION ALL
SELECT 'aaa','1/2/2009', 3 , 122,7504 UNION ALL
SELECT 'aaa','1/2/2009', 4 , -100,9605 UNION ALL
SELECT 'aaa','1/2/2009', 5 , -322,11705 UNION ALL
SELECT 'aaa','1/2/2009', 6 , -544,13806 UNION ALL
SELECT 'aaa','1/2/2009', 7 , -766,15906 UNION ALL
SELECT 'aaa','1/2/2009', 8 , -988,18007 UNION ALL
SELECT 'aaa','1/2/2009', 9 ,-1210,20107 UNION ALL
SELECT 'aaa','1/2/2009', 10,-1432,22208 UNION ALL
SELECT 'aaa','1/2/2009', 11,-1654,24308 UNION ALL
SELECT 'aaa','1/2/2009', 12,-1876,26409 UNION ALL
SELECT 'aaa','1/2/2009', 13,-2098,28509 UNION ALL
SELECT 'aaa','1/2/2009', 14,-2320,30610 UNION ALL
SELECT 'aaa','1/2/2009', 15,-2542,32710 UNION ALL
SELECT 'aaa','1/2/2009', 16,-2764,34811 UNION ALL
SELECT 'aaa','1/2/2009', 17,-2986,36911 UNION ALL
SELECT 'aaa','1/2/2009', 18,-3208,39012 UNION ALL
SELECT 'aaa','1/2/2009', 19,-3430,41112 UNION ALL
SELECT 'aaa','1/2/2009', 20,-3652,43213 UNION ALL
SELECT 'aaa','1/2/2009', 21,-3874,45313 UNION ALL
SELECT 'aaa','1/2/2009', 22,-4096,47414 UNION ALL
SELECT 'aaa','1/2/2009', 23,-4318,49514 UNION ALL
SELECT 'bbb','1/1/2009', 0 , 1212,1234 UNION ALL
SELECT 'bbb','1/1/2009', 1 , 234,3242 UNION ALL
SELECT 'bbb','1/1/2009', 2 , 344,5435 UNION ALL
SELECT 'bbb','1/1/2009', 3 , 122,7504 UNION ALL
SELECT 'bbb','1/1/2009', 4 , -100,9605 UNION ALL
SELECT 'bbb','1/1/2009', 5 , -322,11705 UNION ALL
SELECT 'bbb','1/1/2009', 6 , -544,13806 UNION ALL
SELECT 'bbb','1/1/2009', 7 , -766,15906 UNION ALL
SELECT 'bbb','1/1/2009', 8 , -988,18007 UNION ALL
SELECT 'bbb','1/1/2009', 9 ,-1210,20107 UNION ALL
SELECT 'bbb','1/1/2009', 10,-1432,22208 UNION ALL
SELECT 'bbb','1/1/2009', 11,-1654,24308 UNION ALL
SELECT 'bbb','1/1/2009', 12,-1876,26409 UNION ALL
SELECT 'bbb','1/1/2009', 13,-2098,28509 UNION ALL
SELECT 'bbb','1/1/2009', 14,-2320,30610 UNION ALL
SELECT 'bbb','1/1/2009', 15,-2542,32710 UNION ALL
SELECT 'bbb','1/1/2009', 16,-2764,34811 UNION ALL
SELECT 'bbb','1/1/2009', 17,-2986,36911 UNION ALL
SELECT 'bbb','1/1/2009', 18,-3208,39012 UNION ALL
SELECT 'bbb','1/1/2009', 19,-3430,41112 UNION ALL
SELECT 'bbb','1/1/2009', 20,-3652,43213 UNION ALL
SELECT 'bbb','1/1/2009', 21,-3874,45313 UNION ALL
SELECT 'bbb','1/1/2009', 22,-4096,47414 UNION ALL
SELECT 'bbb','1/1/2009', 23,-4318,49514 UNION ALL
SELECT 'bbb','1/2/2009', 0 , 1212,1234 UNION ALL
SELECT 'bbb','1/2/2009', 1 , 234,3242 UNION ALL
SELECT 'bbb','1/2/2009', 2 , 344,5435 UNION ALL
SELECT 'bbb','1/2/2009', 3 , 122,7504 UNION ALL
SELECT 'bbb','1/2/2009', 4 , -100,9605 UNION ALL
SELECT 'bbb','1/2/2009', 5 , -322,11705 UNION ALL
SELECT 'bbb','1/2/2009', 6 , -544,13806 UNION ALL
SELECT 'bbb','1/2/2009', 7 , -766,15906 UNION ALL
SELECT 'bbb','1/2/2009', 8 , -988,18007 UNION ALL
SELECT 'bbb','1/2/2009', 9 ,-1210,20107 UNION ALL
SELECT 'bbb','1/2/2009', 10,-1432,22208 UNION ALL
SELECT 'bbb','1/2/2009', 11,-1654,24308 UNION ALL
SELECT 'bbb','1/2/2009', 12,-1876,26409 UNION ALL
SELECT 'bbb','1/2/2009', 13,-2098,28509 UNION ALL
SELECT 'bbb','1/2/2009', 14,-2320,30610 UNION ALL
SELECT 'bbb','1/2/2009', 15,-2542,32710 UNION ALL
SELECT 'bbb','1/2/2009', 16,-2764,34811 UNION ALL
SELECT 'bbb','1/2/2009', 17,-2986,36911 UNION ALL
SELECT 'bbb','1/2/2009', 18,-3208,39012 UNION ALL
SELECT 'bbb','1/2/2009', 19,-3430,41112 UNION ALL
SELECT 'bbb','1/2/2009', 20,-3652,43213 UNION ALL
SELECT 'bbb','1/2/2009', 21,-3874,45313 UNION ALL
SELECT 'bbb','1/2/2009', 22,-4096,47414 UNION ALL
SELECT 'bbb','1/2/2009', 23,-4318,49514
SELECT [Subject],[Date],[Hour],DataA,DataB FROM #EXAMPLE
--------------
SELECT
[Subject],
[Date],
--[value] AS [ALIAS]?
[0] AS [DA00],[1] AS [DA01],[2] AS [DA02],[3] AS [DA03],[4] AS [DA04],[5] AS [DA05],
[6] AS [DA06],[7] AS [DA07],[8] AS [DA08],[9] AS [DA09],[10] AS [DA10],[11] AS [DA11],
[12] AS [DA12],[13] AS [DA13],[14] AS [DA14],[15] AS [DA15],[16] AS [DA16],[17] AS [DA17],
[18] AS [DA18],[19] AS [DA19],[20] AS [DA20],[21] AS [DA21],[22] AS [DA22],[23] AS [DA23]
FROM
(SELECT [Subject],[Date],[Hour],DataA FROM #EXAMPLE)
AS TheSource
PIVOT
(
MIN(DataA) -- the value to get that matches the hour
FOR
[HOUR] --the hour values are in brackets
IN ( [0], [1], [2], [3], [4], [5],
[6], [7], [8], [9],[10],[11],
[12],[13],[14],[15],[16],[17],
[18],[19],[20],[21],[22],[23])
) AS PIVOTALIAS
Lowell
June 3, 2009 at 3:57 pm
Excellent. Thanks very much.
I've never used a pivot before but after running your example it seems to be just what I need.
Thanks again!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply