Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

data in row needs to be in colums

Posted on 2012-03-16
9
Medium Priority
?
453 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 800 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 1200 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

577 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