SQL Make every n row to a seperate column

Question

i have a little question. My problem is that i have a table with a single column with sensor data. Every third row represents a new sample. My target is right now to transform every third row to a seperate column like a 3 x N matrix. I dont know how big my N becomes. Is it possible to do this with SQL?

--VALUES--
sensor_sampleA1
sensor_sampleA2
sensor_sampleA3
sensor_sampleB1
sensor_sampleB2
sensor_sampleB3
...
...
...
sensor_sampleN1
sensor_sampleN2
sensor_sampleN3

I want this.

--TARGET_TABLE--
sampleA1 sampleB1 sampleC1 ... ... sampleN1
sampleA2 sampleB2 sampleC2 ... ... sampleN2
sampleA3 sampleB3 sampleC3 ... ... sampleN3

UPDATE: I used the number 3 above as example. I have 11 sensors which give me 11 values per second. My columns are: sensor_data_id, axis, source, type, value and sample_id.

picture of the database

Just to get that out of the way: are they always going to be 3 values per sample? Wouldn't it make more sense to have 3 columns with N rows? — Josh Part, Jan 08 '18 at 19:26
yes, it would be always 3 values per sample. In my context i need for my neural network this representation to fill the data... — bla93, Jan 08 '18 at 19:34
I'm not aware of ANSI SQL being able to do this transformation. If you had an ID column (row number) it would be easy. But relying on the order your DB stores the data and reads it when executing the query? That's DB specific. What tools do you have available? What format is the data stored in? — Petr, Jan 08 '18 at 20:05
My table is named sensor_data and has a primary key which can be used for accessing the row number. The order of the data is very important und is not allowed to change. Im using PostgresSQL with DBeaver. — bla93, Jan 08 '18 at 20:14
Is the data named in such a way that it can be logically ordered? Example: will the data always be named "`sensor_sampleA1`, `sensor_sampleA2', etc"? Can you provide an example of your data or the query you're using to pull and order that data? — Shawn, Jan 08 '18 at 20:23
Just to verify, there will always be an equal number of 3 sensor samples? ie the 3 sensors may return 20 samples, but you won't have 1 sensor return 12 samples and the others return 20? — Shawn, Jan 08 '18 at 20:29
two things: 1. don't post pictures; either use [Sql Fiddle](http://sqlfiddle.com/) or make a text representation of your table schema. 2. In your example picture I see 2 sensors with 3 values, 1 sensor with 1 value and 1 sensor with 4 values; how are these going to be represented in your result table? — Josh Part, Jan 08 '18 at 21:08

score 0 · Answer 1 · answered Jan 08 '18 at 19:40

0

Here's one idea - use the row number to group your data first, then you can select the results into a temp table or use as a subquery:

SELECT sensor_sample, ((ROW_NUMBER() OVER (ORDER BY sensor_sample) -1) / 3) AS sensor_group_id
FROM sensor_table

answered Jan 08 '18 at 19:40

Dave

3,273
1
17
15

Petr · Accepted Answer · 2018-01-08T22:02:24.590

This is one of possible solutions. I used the first 4 measure types - that should illustrate it.

select
     sample_id
    ,max(case when axis = 'X' and type = 'linear acceleration' then value else null end) as sensor1
    ,max(case when axis = 'Y' and type = 'linear acceleration' then value else null end) as sensor2
    ,max(case when axis = 'Z' and type = 'linear acceleration' then value else null end) as sensor3
    ,max(case when axis = 'X' and type = 'gyroscope          ' then value else null end) as sensor4
from have
group by sample_id

score 0 · Answer 3 · answered Jan 08 '18 at 22:43

If you were using SQL Server, I'd recommend using PIVOT. I think there's a way to replicate PIVOT in PostgreSql using CROSSTAB but I'm not familiar enough with it to try writing it.

Here's the MSSQL version in case you want to try converting it:

SELECT
    sample_id,
    source,
    [la_x],[la_y],[la_z],
    [gy_x],[gy_y],[gy_z],
    [st_a],[rv_r],[rv_p],
    [rv_y],[rv_m]
FROM
(
    SELECT  
        CONCAT(
            CASE type
                WHEN 'linear acceleration' THEN 'la'
                WHEN 'gyroscope' THEN 'gy'
                WHEN 'stepcounter' THEN 'st'
                ELSE 'rv'
            END, '_',
            ISNULL(LEFT(axis,1),'a')) 'dtype',
        value 'value',
        sample_id 'sample_id',
        source 'source'
    FROM
        sensortable
) sq
PIVOT
(
    SUM(value) FOR
    dtype IN
        ([la_x],[la_y],[la_z],
        [gy_x],[gy_y],[gy_z],
        [st_a],[rv_r],[rv_p],
        [rv_y],[rv_m])
) pv

SQL Make every n row to a seperate column

3 Answers3