Solved

data in row needs to be in colums

Posted on 2012-03-16
9
438 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now