Pivot/cross tab question

Question

Pivot/cross tab question

wmedernach

SSC Rookie

Points: 41
More actions
March 19, 2011 at 9:38 pm

#236521

Hello all,
I'm looking for suggestions on how to create a query to structure some time series data.
The data looks something like this:
Is this possible with the Pivot command?
Thanks for any suggestions or advice
Warren M

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

MikeJohnsonZA SSC Enthusiast Points: 176 More actions · Answer 1

Hi Warren

I am not sure if you want two separate sets or not, if you want to return one set with the data pivoted then you can use this query

declare @MyTable table

(

[TimeStamp] datetime,

Name varchar(3),

RetVal real

)

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','M01',1.23);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','N02',2.21);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','M01',2.13);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','N02',1.45);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 03:00:00.000','M01',2.02);

select [TimeStamp],[m01],[n02] from

(

select [TimeStamp],Name,RetVal from @MyTable

) piv

pivot

(

sum(RetVal) for name in ([M01],[N02])

)chld

If you want two separate sets then it is probably simpler just to do this

declare @MyTable table

(

[TimeStamp] datetime,

Name varchar(3),

RetVal real

)

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','M01',1.23);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 01:00:00.000','N02',2.21);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','M01',2.13);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 02:00:00.000','N02',1.45);

insert into @MyTable([TimeStamp],Name,RetVal) values('2011-01-01 03:00:00.000','M01',2.02);

select [TimeStamp] [M01],RetVal from @myTable where [Name] = 'M01'

select [TimeStamp] [N02],RetVal from @myTable where [Name] = 'N02'

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 2

wmedernach (3/19/2011)
Hello all,
I'm looking for suggestions on how to create a query to structure some time series data.
The data looks something like this:
Is this possible with the Pivot command?
Thanks for any suggestions or advice
Warren M

How many names do you have?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wmedernach SSC Rookie Points: 41 More actions · Answer 3

Thanks michaelAdrianJohnson for the information.

Warren M

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

wmedernach (3/20/2011)
Thanks michaelAdrianJohnson for the information.
Warren M

Again I ask... how many names do you have in your real table and will the number of names ever increase? If the number of names does increase, Michael's code will need to be modified each time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

wmedernach SSC Rookie Points: 41 More actions · Answer 5

I apologize Jeff, I thought you were asking 'michaelAdrianJohnson' how many names he had... 😉

In the 'real data' I have _many_ names in this table, and the timestamps aren't formatted that nice either. I've come to the conclusion that I might not be able to pivot the data the way I was hoping.

Thanks

Warren

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 6

wmedernach (3/20/2011)
I apologize Jeff, I thought you were asking 'michaelAdrianJohnson' how many names he had... 😉
In the 'real data' I have _many_ names in this table, and the timestamps aren't formatted that nice either. I've come to the conclusion that I might not be able to pivot the data the way I was hoping.
Thanks
Warren

This can be done and in a fairly simple manner but I need to know of the table structure and have some readily consumable data to demonstrate with. Could you provide the data in a manner suggested by the article located at the first link in my signature line below?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)