[SOLVED] Concat cells of multiple rows into one row

Concat cells of multiple rows into one row

I have a really weird requirement for my SQL Server table and I'm not sure if it is impossible to solve.

I have a client-side grid/table which displays data by a given T-SQL query/stored procedure. The table in the SQL Server database that I am looking at is numeric data with timestamps - something like this:

| DateTime(Key)          | Value  |
+------------------------+--------+
| 2010-07-27 17:00:00.00 | 1.337  |
| 2010-07-27 18:00:00.00 | 2.453  |
| 2010-07-27 19:00:00.00 | 3.2342 |

The requirement now is to display more 'value'-columns in one row like so:

| DateTime(Key)          | Value  | Value  | Value  |
+------------------------+--------+--------+--------+
| 2010-07-27 17:00:00.00 | 1.337  | 2.453  | 3.2342 |
| 2010-07-27 20:00:00.00 | 4.432  | 5.3422 | 6.9484 |

(Attention: The successive first numbers are just for readability)

Now I would like to create a view/stored procedure which allows me to provide the column I would like to have multiple of and the amount.

I have no idea how to approach this. Because you have to look at the next x rows and remove them and apply them to the first one.

Solution

I found an answer which is not fully dynamic yet, but it solves the basic requirement. I will post the fully dynamic stored procedure later.

DECLARE @Count int;
SET @Count = 3;

SELECT TimeUtc, V1, V2, V3 FROM (
SELECT 
    TimeUtc,
    Value as V1,
    LEAD(Value, 1, null) over (ORDER BY TimeUtc) AS V2,
    LEAD(Value, 2, null) over (ORDER BY TimeUtc) AS V3, 
    (ROW_NUMBER() OVER (ORDER BY TimeUtc) - 1) % @Count AS RN
FROM MeasurementData
WHERE 
) as tbl where RN = 0;

It would've been nice if I did not need the outer select, but i did not find a way around it.