Solved

data in row needs to be in colums

Posted on 2012-03-16
9
447 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

632 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