Solved

data in row needs to be in colums

Posted on 2012-03-16
9
444 Views
Last Modified: 2012-03-18
I have a data table that is set up like this

ID           DateTime                          Data  Inc1  Inc2  Inc3  Inc4  Inc5  Inc6  
354        3/15/2012 8:00 AM          150    25     10        5       15    10      20

all Data is setup like this in the table   there is a data value and then an incremental value for each hour.  So that  INC1  is the addition withing the hour.  So at inc1  you take 25 and add it to 150 to get a total of 175 and the time value would be 3/15/2012  9:00 AM.

So i am wanting to get an end result like this

ID               DateTime                    Data
354        3/15/2012 8:00 AM          150
354        3/15/2012 9:00 AM          175
354        3/15/2012 10:00 AM        185
354        3/15/2012 11:00 AM        190
354        3/15/2012 12:00 PM        205
354        3/15/2012 1:00 PM          215
354        3/15/2012 2:00 PM          235

I hope this makes sense.  I have no idea how to do this.  any insight would be  appreciated.
0
Comment
Question by:Brickwall
  • 5
  • 3
9 Comments
 
LVL 7

Expert Comment

by:micropc1
ID: 37731729
UNPIVOT should do what you need...

SELECT u.ID, u.[DateTime], u.Data
FROM 
   (SELECT ID
		, [DateTime]
		, Data AS Data1
		, Data + Inc1 AS Data2
		, Data + Inc1 + Inc2 AS Data3
		, Data + Inc1 + Inc2 + Inc3 AS Data4
		, Data + Inc1 + Inc2 + Inc3 + Inc4 AS Data5
		, Data + Inc1 + Inc2 + Inc3 + Inc4 + Inc5 AS Data6
		, Data + Inc1 + Inc2 + Inc3 + Inc4 + Inc5 + Inc6 AS Data7
	FROM pvt) p
UNPIVOT
   ([Data] FOR dIndex IN (Data1, Data2, Data3, Data4, Data5, Data6, Data7))AS u

Open in new window

0
 
LVL 7

Expert Comment

by:micropc1
ID: 37731756
I just realized you need the date to increment also. I'll have to get back to you on that one...
0
 

Author Comment

by:Brickwall
ID: 37731826
Yeah that was the issue i was having too.
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 7

Expert Comment

by:micropc1
ID: 37731827
This should do it...

SELECT u.ID, DATEADD(hh, ROW_NUMBER() OVER (ORDER BY u.ID, u.[Data]) - 1, u.[DateTime]) AS [DateTime], u.Data
FROM 
   (SELECT ID
		, [DateTime]
		, Data AS Data1
		, Data + Inc1 AS Data2
		, Data + Inc1 + Inc2 AS Data3
		, Data + Inc1 + Inc2 + Inc3 AS Data4
		, Data + Inc1 + Inc2 + Inc3 + Inc4 AS Data5
		, Data + Inc1 + Inc2 + Inc3 + Inc4 + Inc5 AS Data6
		, Data + Inc1 + Inc2 + Inc3 + Inc4 + Inc5 + Inc6 AS Data7
	FROM yourTableName) p
UNPIVOT
   ([Data] FOR dIndex IN (Data1, Data2, Data3, Data4, Data5, Data6, Data7))AS u
ORDER BY u.ID, u.[Data]

Open in new window

0
 

Author Comment

by:Brickwall
ID: 37731926
ok we are almost there.  The only issue i have now.  Is there is a new row for each 24 hour incremenet.  and your query when i run it for a period of time it adds an extra day to the date.  So basically
ID           DateTime                          Data  Inc1  Inc2  Inc3  Inc4  Inc5  Inc6  
354        3/15/2012 8:00 AM          150    25     10        5       15    10      20
354        3/16/2012 8:00AM           240  

Becomes
ID               DateTime                    Data
354        3/15/2012 8:00 AM          150
354        3/15/2012 9:00 AM          175
354        3/15/2012 10:00 AM        185
354        3/15/2012 11:00 AM        190
354        3/15/2012 12:00 PM        205
354        3/15/2012 1:00 PM          215
354        3/15/2012 2:00 PM          235
354        3/17/2012 8:00 AM


So close but just a little off
0
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 200 total points
ID: 37733158
No need for UNPIVOT, plus this has the benefit of being applicable to SQL Server 2000 or earlier:


SELECT [ID], [DateTime], [Data]
FROM [SomeTable]
WHERE [Data] IS NOT NULL
UNION ALL
SELECT [ID], DATEADD(hour, 1, [DateTime] AS [DateTime], [Inc1] AS [Data]
FROM [SomeTable]
WHERE [Inc1] IS NOT NULL
UNION ALL
SELECT [ID], DATEADD(hour, 2, [DateTime] AS [DateTime], [Inc2] AS [Data]
FROM [SomeTable]
WHERE [Inc2] IS NOT NULL
UNION ALL
SELECT [ID], DATEADD(hour, 3, [DateTime] AS [DateTime], [Inc3] AS [Data]
FROM [SomeTable]
WHERE [Inc3] IS NOT NULL
etc
0
 
LVL 7

Accepted Solution

by:
micropc1 earned 300 total points
ID: 37733253
Sure, a UNION will also work, but you'll need to do it like this to get your desired result...

SELECT [ID], [DateTime], [Data]
FROM (
      SELECT [ID]
            , [DateTime]
            , [Data]
      FROM [yourTableName]
      UNION ALL
      SELECT [ID]
            , DATEADD(hour, 1, [DateTime]) AS [DateTime]
            , [Data] + [Inc1] AS [Data]
      FROM [yourTableName]
      UNION ALL
      SELECT [ID]
            , DATEADD(hour, 2, [DateTime]) AS [DateTime]
            , [Data] + [Inc1] + [Inc2] AS [Data]
      FROM [yourTableName]
      UNION ALL
      SELECT [ID]
            , DATEADD(hour, 3, [DateTime]) AS [DateTime]
            , [Data] + [Inc1] + [Inc2] + [Inc3] AS [Data]
      FROM [yourTableName]
      UNION ALL
      SELECT [ID]
            , DATEADD(hour, 4, [DateTime]) AS [DateTime]
            , [Data] + [Inc1] + [Inc2] + [Inc3] + [Inc4] AS [Data]
      FROM [yourTableName]
      UNION ALL
      SELECT [ID]
            , DATEADD(hour, 5, [DateTime]) AS [DateTime]
            , [Data] + [Inc1] + [Inc2] + [Inc3] + [Inc4] + [Inc5] AS [Data]
      FROM [yourTableName]
      UNION ALL
      SELECT [ID]
            , DATEADD(hour, 6, [DateTime]) AS [DateTime]
            , [Data] + [Inc1] + [Inc2] + [Inc3] + [Inc4] + [Inc5] + [Inc6] AS [Data]
      FROM [yourTableName]) T
ORDER BY [ID] ASC, [Data] ASC
0
 
LVL 7

Expert Comment

by:micropc1
ID: 37733466
Here's a fixed version of the UNPIVOT statement...added a modulo operation to reset the time increment on every 7 rows...

SELECT u.ID, DATEADD(hh, (ROW_NUMBER() OVER (ORDER BY u.ID, u.[Data]) - 1) % 7, u.[DateTime]) AS [DateTime], u.Data
FROM 
   (SELECT ID
		, [DateTime]
		, Data AS Data1
		, Data + Inc1 AS Data2
		, Data + Inc1 + Inc2 AS Data3
		, Data + Inc1 + Inc2 + Inc3 AS Data4
		, Data + Inc1 + Inc2 + Inc3 + Inc4 AS Data5
		, Data + Inc1 + Inc2 + Inc3 + Inc4 + Inc5 AS Data6
		, Data + Inc1 + Inc2 + Inc3 + Inc4 + Inc5 + Inc6 AS Data7
	FROM yourTableName) p
UNPIVOT
   ([Data] FOR dIndex IN (Data1, Data2, Data3, Data4, Data5, Data6, Data7))AS u
ORDER BY u.ID, u.[Data]

Open in new window

0
 

Author Comment

by:Brickwall
ID: 37734137
Thanks Guys that Works.  I was able to get it working using the Union All. Instea of the unpivot.  it worked better for my application.  Especially since some of the fields were null if the system did not report..  I am going to split the points.  Thank you so Much
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

786 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