OOPS, a slight mistake (incomplete)
SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
Stim in (''V0'',''V5'') ORDER BY 1, 2) AS ct(SerialNumber, V0, V5)
On Tue, Apr 7, 2015 at 9:14 AM, John McKown <john.archie.mckown at gmail.com>
wrote:
> On Fri, Mar 27, 2015 at 10:48 AM, Drago, William @ CSG - NARDA-MITEQ <
> William.Drago at l-3com.com> wrote:
>
>> All,
>>
>> Say I have a table that looks like this:
>>
>>
>> SerialNumber | Stim | Resp
>> -------------|-------|---------
>> . | . | .
>> . | . | .
>> 123 | V0 | 0.136
>> 123 | V1 | 0.201
>> 123 | V2 | 0.297
>> 123 | V3 | 0.311
>> 123 | V4 | 0.408
>> 123 | V5 | 0.599
>> 124 | . | .
>> 124 | . | .
>>
>>
>> I want the rows containing V0 and V5 to become columns like this:
>>
>> SerialNumber | V0 | V5
>> -------------|-------|-------
>> 123 | 0.136 | 0.599
>> 124 | 0.126 | 0.587
>> 125 | 0.119 | 0.602
>>
>>
>> I am presently using two SELECT statements as follows:
>>
>>
>> SELECT SerialNumber, Resp AS V0 WHERE SerialNumber = '123' AND Stim = 'V0'
>>
>> SELECT SerialNumber, Resp AS V5 WHERE SerialNumber = '123' AND Stim = 'V5'
>>
>>
>> And then reformatting the results using C# code before outputting to .csv
>> file.
>>
>> Is there a way to do everything in SQL? I looked this up online and found
>> some pretty complicated examples. Is it really that hard?
>>
>> Thanks,
>> --
>> Bill Drago
>> Senior Engineer
>>
>
>
> ?I somehow missed this long ago. And so I may be too late. But have you
> looked at the crosstab function in the tablefunc package?
>
> http://www.postgresql.org/docs/9.4/interactive/tablefunc.html?
>
>
> ?SELECT * FROM CROSSTAB('SELECT SerialNumber, Stim, Resp FROM table WHERE
> Stim in (''V0'',''V5'')) AS ct(SerialNumber, V0, V5)
>
> Note that the above is just my guess. I don't have any data to test with. ?
>
>
>
> --
> If you sent twitter messages while exploring, are you on a textpedition?
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
--
If you sent twitter messages while exploring, are you on a textpedition?
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
