Re-Arranging Data - Potentially Using a View? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Re-Arranging Data - Potentially Using a View?

Author

Topic

maxzara
Starting Member

3 Posts

Posted - 2012-03-05 : 13:29:26

Hi All,

My first question on here, should be quite a simple one but I can't seem to be able to search through to find my answer. I have a data set, looks like this..

Date Hour Value
1/1/2012 1 50
1/1/2012 2 70
1/3/2012 1 60

And so on...

I would like for it to display the data as

Date Hour 1 2 3
1/1/2012 50 70 n/a
1/3/2012 60 n/a n/a

What is the easiest way of doing this?

Thank you!

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-05 : 13:31:20

How many columns can you have?

Look up CROSS APPLY

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

maxzara
Starting Member

3 Posts

Posted - 2012-03-05 : 13:35:05

I would like to have 24 columns (per 24 hours)

maxzara
Starting Member

3 Posts

Posted - 2012-03-05 : 13:58:07

I have attempted to do this and it doesn't work, as alternate suggestion.

SELECT Value, Hour, Date
from
(
select*, row_number over (partition by Hour order by Date) RowNumber from MyTable
) as p
pivot
(
min(Date) for Hour in (Value, Hour, Date)
)
as t
order by RowNumber

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-05 : 15:26:41

Maybe this will get you going:

DECLARE @Foo TABLE ([Date] DATE, [Hour] TINYINT, Value INT)

INSERT @Foo VALUES
('1/1/2012', 1, 50),
('1/1/2012', 2, 70),
('1/3/2012', 1, 60)

SELECT [Date], [1], [2], [3]-- ....
FROM 
(
    SELECT [Date], [Hour], Value
    FROM @Foo AS F
) AS SourceTable
PIVOT
(
    MAX(Value)
    FOR [Hour] IN ([1], [2], [3]) -- etc
) AS PivotTable

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources