1

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.

woodyplz
  • 13
  • 1
  • 6
  • (1) Tag your question with the database you are using. (2) What do you mean "which allows me to provide the column I would like to have multiple of and the amount."? – Gordon Linoff Mar 06 '18 at 12:21
  • This is indeed odd, I assume you are displaying anything from 17:00 to 19:00 on one row, then skipping to 20:00 to 22:00, etc. I have no idea why you would want to do this, but it could all be achieved with a `GROUP BY` statement and some `CASE` statements as far as I can see. If you provide some test data then I'm sure someone could provide a solution? – Richard Hansell Mar 06 '18 at 12:25
  • Actually, re-reading your question you want to have a variable number of rows pivoted this way. This is almost certainly going to have to be dynamic, which means a stored procedure is probably the best solution. You could either look at dynamic `PIVOT` or do something clever with `LEAD` (also constructing the query dynamically). – Richard Hansell Mar 06 '18 at 12:28
  • @GordonLinoff (1) done (2) the grid actually takes any generic query, so i explained it with my knowledge that i explained poorly. For the example above i should have just asked for the 'count'. So in this case the column 'Value' 3 times. – woodyplz Mar 06 '18 at 12:36
  • @woodyplz, Try my answer. – DineshDB Mar 06 '18 at 12:59

2 Answers2

1

Try this:

DECLARE @Tab TABLE(DateTime DateTime, Value NUMERIC(8,5))

INSERT INTO @Tab VALUES('2010-07-27 17:00:00.00',1.337)
INSERT INTO @Tab VALUES('2010-07-27 18:00:00.00',2.453)
INSERT INTO @Tab VALUES('2010-07-27 19:00:00.00',3.2342)
INSERT INTO @Tab VALUES('2010-07-27 20:00:00.00',4.432)
INSERT INTO @Tab VALUES('2010-07-27 21:00:00.00',5.3422)
INSERT INTO @Tab VALUES('2010-07-27 22:00:00.00',6.9484)

SELECT MIN(D.DateTime)DateTime
    ,MIN(CASE WHEN D.RN=1 THEN D.Value END)Value1
    ,MIN(CASE WHEN D.RN=2 THEN D.Value END)Value2
    ,MIN(CASE WHEN D.RN=3 THEN D.Value END)Value3
FROM(
    SELECT *
        ,(CAST(Value AS INT)-1)/3 Val
        ,ROW_NUMBER() OVER(PARTITION BY (CAST(Value AS INT)-1)/3 ORDER BY DateTime)RN
    FROM @Tab
    )D
GROUP BY val

Result:

DateTime                Value1  Value2  Value3
2010-07-27 17:00:00.000 1.33700 2.45300 3.23420
2010-07-27 20:00:00.000 4.43200 5.34220 6.94840
DineshDB
  • 5,998
  • 7
  • 33
  • 49
  • hey thanks for the quick answer, i tried for my own for the last hour with LEAD which seemed promissing. I will try yours now, which looks really good! – woodyplz Mar 06 '18 at 13:30
  • @woodyplz, Yeah, happy to hear this. You are welcome...! – DineshDB Mar 06 '18 at 13:32
  • This is not dynamic and cant handle x rows. Use a dynamic pivot script. – SqlKindaGuy Mar 06 '18 at 13:47
  • I think I know understand what you were doing. With '(CAST(Value AS INT)-1)/3 ' you are dividing the value before the '.'(dot) and using it to identify the rownumber to group with. I just used successive first numbers to help people understand my example. Sadly it just caused confusion. :( – woodyplz Mar 06 '18 at 14:00
0

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.

woodyplz
  • 13
  • 1
  • 6