Help with Transposing Values - SQL Server Forums

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

Help with Transposing Values

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-07 : 14:10:15

Hello All,

I have two tables like this:

Events

ID RID Event Location
615261 220 Vstart Flow Timings
615262 220 MAPSPY_INSP_NR Voltage
615263 220 Vimax1 Flow Timings
615264 220 V50 Flow Timings
615265 220 Vimax2 Flow Timings
615266 220 Vstop Flow Timings

and it repeats in this fashion.

TABLE2 - PACKETS

ID NAME DoubleValue EventID
60916 A7 (TcCO2) 24.20046982 615261
60917 Pes -0.066422894 615261
60918 SpO2 98.00103931 615261
60919 Thorax -2.02E-05 615261
60920 Vstart 2.551983833 615261
60921 Length 0 615262
60922 Parameter 40 615262
60923 Start sample 0 615262
60924 Stop sample 0 615262
60925 A7 (TcCO2) 24.30522461 615263
60926 Pes -0.066422894 615263
60927 SpO2 98.00103931 615263
60928 Thorax 2.63E-05 615263
60929 Vimax1 Index 0 615263
60930 Vimax1 Value 2.179376602 615263
60931 A7 (TcCO2) 24.22665852 615264
60932 Pes -0.066422894 615264
60933 SpO2 98.00103931 615264
60934 Thorax 3.84E-05 615264
60935 V50 2.246362209 615264
60936 A7 (TcCO2) 24.25284721 615265
60937 Pes -0.066422894 615265
60938 SpO2 98.00103931 615265
60939 Thorax 4.86E-05 615265
60940 Vimax2 Index 0 615265
60941 Vimax2 Value 2.238323927 615265
60942 A7 (TcCO2) 24.25284721 615266
60943 Pes -0.066422894 615266
60944 SpO2 98.00103931 615266
60945 Thorax 4.46E-05 615266
60946 Vstop 2.527868986 615266

I want to make this table:

Vimax1 Pes @ Vimax1 Vimax2
2.17937 -0.066 2.23832

and so forth for the relating Pes values for the Vimax1,2,V50,Vstart, and Vstop. It has to be transposed and the relating link is the pattern in the EVENTS table which makes a pair from Vstart to Vstop.

This should be a inner join. but then how do u transpose?
I would like some help with the code.

Thanks,
Dasman

==========================
Pain is Weakness Leaving the Body.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-07 : 15:07:45

wow - clear as mud.
I can't tell what you need in terms of this specific business requirement but in regards to transposing rows to columns there are a couple ways to go about it. You can use PIVOT if you have sql 2005 or later. or you can do it the old fashioned way with max(case...end) as [col] and group by.

Will there be a constant set of columns or does it depend on the values in your data?

If you need specific help could you post the actual DDL and DML. so we can run code against actual tables with sample data? And if you provide the exact desired output based on your sample data that always helps too.

EDIT:
check this out if you don't know what I mean by DDL/DML:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

Dasman
Yak Posting Veteran

79 Posts

Posted - 2011-07-08 : 13:56:57

Sry about that:

This is a simplified version of what I had:
V1 0
V1 -62.681072235107422
V2 0
V2 -68.894050598144531
V1 0
V1 -60.157051086425781
V2 0
V2 -64.816787719726562
V1 0
V1 -63.651851654052734

Now how can I turn this into:
V1 V2
0 0

etc.

==========================
Pain is Weakness Leaving the Body.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-11 : 14:03:02

Well you didn't answer any of my questions and you didn't post DDL/DML. But I'll give an example of PIVOT which may work for you assuming you have 2005 or later. Note that I added an ID column to associate V1 and V2 data into Rows. If you can't figure it out from this then go back to my previous post, answer the questions, and provide your full DDL, DML, and expected results.


;with yourTable (id, col1, col2) as
(
       select 1,'V1',  0 union all
       select 2,'V1', -62.681072235107422 union all
       select 1,'V2',  0 union all
       select 2,'V2',  -68.894050598144531 union all
       select 3,'V1',  0 union all
       select 4,'V1',  -60.157051086425781 union all
       select 3,'V2',  0 union all
       select 4,'V2',  -64.816787719726562 union all
       select 5,'V1',  0 union all
       select 6,'V1',  -63.651851654052734
)

select id
       ,p.v1
       ,p.v2
from
(
select id
       ,col1
       ,col2
from yourTable
) d
pivot ( max(col2) for col1 in ([v1],[v2])) p


OUTPUT:
id          v1                                      v2
----------- --------------------------------------- ---------------------------------------
1           0.000000000000000                       0.000000000000000
2           -62.681072235107422                     -68.894050598144531
3           0.000000000000000                       0.000000000000000
4           -60.157051086425781                     -64.816787719726562
5           0.000000000000000                       NULL
6           -63.651851654052734                     NULL

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources