design a new table from existing table

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

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

design a new table from existing table

Author

Topic

rasher83
Starting Member

24 Posts

Posted - 2008-06-03 : 02:08:19

hi,
i have a table with data in the following format (dont ask why it was done this way, its not something i can change)

so my table looks like this:

point :timestamp :value
_________________________
var1 11:00 5
var2 11:00 2.3
var3 11:00 21
var1 12:00 5.2
var2 12:00 2.4
var3 12:00 18

and so on.

i would like to have my data rearranged to look like the following:

timestamp :var1 value :var2 value :var3 value
________________________________________________
11:00 x y z
12:00 a b c
....

i am working with my database in a c sharp application. so if anyone does come up with a solution for this maybe keep that in mind so that i can discuss how to impliment it in the code.

thanks guys.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-03 : 05:40:16

One question. Will there always be only a set number of vars?

If so, do this in a Stored Proc using PIVOT and then call that from the c# app.

rasher83
Starting Member

24 Posts

Posted - 2008-06-03 : 11:24:50

Yes, there will always be a set number of vars.
Could you give an example of the pivot proceedure for my given example.
Also, I would need to be able to create this procedure from my c# application as I cannot go to every one of my customers databases and add a proceedure.

If this would then restrick us forom using this method is there another way to do this from code?

Thanks.

rasher83
Starting Member

24 Posts

Posted - 2008-06-04 : 11:56:04

I can pull out the data by doing a select where point = var1, and another for var2, and another for var3.
but these result in 3 different result sets.
Is there a way I can make my application display all of these 3 result sets in one table or gridview with the timestamp as the common column?

Thanks,

rasher83
Starting Member

24 Posts

Posted - 2008-06-04 : 13:03:54

I believe i need an inner join of sorts from what i've read but do not know how to impliment in this situation.

select Timestamp, value as value1 from table where point = var1

join

select Timestamp, value as value2 from table where point = var2

on Timestamp = Timestamp

this is what is in my head as the approach but as i said i have no idea how to impliment correctly, i've read up on jopins and im as confused as ever. could someone provide me with some sample code to try out?
thanks,

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-05 : 02:07:42

From your sample data.


declare @test table (point varchar(4), [timestamp] varchar(5), [value] decimal(15,2))

insert into @test
select 'var1','11:00',5
union select 'var2','11:00',2.3
union select 'var3','11:00',21
union select 'var1','12:00',5.2
union select 'var2','12:00',2.4
union select 'var3','12:00',18

select
	[timestamp],
	var1,
	var2,
	var3
from @test
PIVOT (
	sum([value])
for point in (var1, var2,var3))
as pvt

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources