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.
| Author |
Topic |
|
andrewcw
Posting Yak Master
133 Posts |
 Posted - 2012-08-13 : 17:45:59
|
| Several weeks ago I was introduced to a way to move data into columns - Xvalues were alternating with Yvalues by rowThis is an example of that effort.SELECT C_FKEY,LN,attempt,MAX(CASE WHEN Seq=1 THEN Stream_FKey END) AS X_Fkey, 'INDEPX' as IndepTransducer, MAX(CASE WHEN Seq=1 THEN Value END) AS Xvalue,MAX(CASE WHEN Seq=2 THEN Stream_FKey END) AS Y_Fkey,'SENSOR_Y' as Transducer, MAX(CASE WHEN Seq=2 THEN Value END) AS Yvalue, recordNumFROM (SELECT ROW_NUMBER() OVER (PARTITION BY C_FKEY, attempt,recordNum ORDER BY stream_fkey) AS Seq,*FROM ( Select * from [SILK].[dbo].[TestFlightStreamHistory] where LN between 4130 and 4260 and ( Stream_FKey = 5259 or Stream_FKey = 5260) ) dtable)t GROUP BY LN,C_FKEY,Attempt, RecordNum I dont understand the SELECT ROW_NUMBER() OVER (PARTITION BY C_FKEY, attempt,recordNum ORDER BY stream_fkey) AS Seq,*very well. If I have a series of stream_fkeys that belong to the X-values & Y-values - then is there something else I can do, other than reduce my data to each X,Y pair and append the data as each set processes ?In the above example - 5259 was for the X-Values, 5260 for Y-values.Sometimes I will get more pairs, say 6443 for X-values, and 6448 for Y-values, and etc.Can select row_number() over partition by applied in this way or do I need to process each pair of stream_keys. Thanksandrewcw |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-08-13 : 18:00:46
|
| unless you provide us some data and explain we will not be able to understand your scenario wellFrom what I understood I think what you need is field which indicates mapping between corresponding X and Y values (5259->5260,6443->6448,...)do you've such a field?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
|
|
|
|
|
SELECT ROW_NUMBER() OVER (PARTITION