Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

data in row needs to be in colums

Posted on 2012-03-16
9
Medium Priority
?
451 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
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.
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.

885 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