Querying single table and merge result for same DateTime

Question

I have a table where I insert values, related to an id, that change over time.

Example:

| Id  |         Timestamp         |  Value  |
|  1  |  2018-09-12 02:29:31.154  |   7.139 |
|  1  |  2018-09-12 02:40:46.724  |   7.254 |
|  2  |  2018-09-06 02:40:46.724  | 132.451 |
|  1  |  2018-09-12 02:42:19.841  |   7.645 |
|  3  |  2018-09-12 03:01:45.811  |  45.276 |
|  1  |  2018-09-12 03:12:59.121  |   7.421 |
|  2  |  2018-09-12 03:12:59.121  | 130.789 |
|  1  |  2018-09-12 03:15:33.467  |   7.121 |
|  2  |  2018-09-12 03:15:33.467  | 136.198 |
|  3  |  2018-09-12 03:15:33.467  |  46.971 |
|  2  |  2018-09-12 03:27:13.642  | 131.879 |
|  3  |  2018-09-12 03:27:13.642  |  44.645 |
|  1  |  2018-09-12 03:30:27.564  |   7.691 |
| ... |            ...            |   ...   |

My goal is to make a single query to take values between a range of dates for Id 1, 2 and 3 and marge them together.

The result will be something like this:

{ Timestamp: "2018-09-12 02:29:31.154", id1: 7.123, id2: null, id3: null }
{ Timestamp: "2018-09-12 02:40:46.724", id1: 7.254, id2: 132.451, id3: null }
{ Timestamp: "2018-09-12 02:42:19.841", id1: 7.645, id2: null, id3: null }
{ Timestamp: "2018-09-12 03:01:45.811", id1: null, id2: null, id3: 45.276 }
{ Timestamp: "2018-09-12 03:12:59.121", id1: 7.421, id2: 130.789, id3: null }
{ Timestamp: "2018-09-12 03:15:33.467", id1: 7.121, id2: 136.198, id3: 46.971 }
{ Timestamp: "2018-09-12 03:27:13.642", id1: null, id2: 131.879, id3: 44.645 }
{ Timestamp: "2018-09-12 03:30:27.564", id1: 7.691, id2: null, id3: null }

Is it possible in a single query?

do you want the result in one long string as what you have shown or in 4 columns ? — Squirrel, Sep 13 '18 at 07:10

Slava Utesinov · Accepted Answer · 2018-09-13T07:36:45.597

1

var ids = new List<int> { 1, 2, 3};

var result = (from item in db.Table
              where ids.Contains(item.id) && item.Timestamp > fromDate && item.Timestamp < toDate
              group item by item.Timestamp into sub
              select new 
              {
                  Timestamp = sub.Key,
                  id1 = sub.Where(x => x.id == 1).Select(x => x.Value).FirstOrDefault(),
                  id2 = sub.Where(x => x.id == 2).Select(x => x.Value).FirstOrDefault(),
                  id3 = sub.Where(x => x.id == 3).Select(x => x.Value).FirstOrDefault()
              }).ToList();

edited Sep 13 '18 at 07:36

answered Sep 13 '18 at 07:17

Slava Utesinov

13,410
2
19
26

I never seen this type of query.. Let me know tag of this query. – Pugal Sep 13 '18 at 07:22
I need a query between a range of dates like: `2018-09-12 00:00:00.000` to `2018-09-12 23:59:59.999` – Broge Sep 13 '18 at 07:35
@Broge, date filter is added – Slava Utesinov Sep 13 '18 at 07:37
@IdontKnowEnglish - it seems this is "linq" – Oto Shavadze Sep 13 '18 at 07:51
Okay.. Thx @SlavaUtesinov.. I will follow this tag. – Pugal Sep 13 '18 at 07:52

score 0 · Answer 2 · answered Sep 13 '18 at 07:43

How about resorting to string aggregation, in SQL Server 2017 using STRING_AGG() function [documentation] it would work like this:

SELECT [Timestamp]
  -- building string very similar to what you've posted; you can certainly play with this column and format it the way you want
  , STRING_AGG(CONCAT('id', CAST(Id AS nvarchar(50)), ': ', ISNULL(CAST([Value] AS nvarchar(50)), N'NULL')), ', ')
FROM schema.Table
GROUP BY [Timestamp];

If you're using an older version of SQL Server, logic would be the same, you'd just resort to a custom way of string aggregation, for example one of these:

I myself usually use the XML solution when STRING_AGG() is not available.

Just replace `STRING_AGG` with how it's done in the first link and you'll be set. — MK_, Sep 13 '18 at 09:52

Querying single table and merge result for same DateTime

2 Answers2