convert rows to columns using date column

Question

I have a table containing four rows: id(primary key, auto increment), value, type and time.

id   value  type    time
1    1.2     1    2017-10-26 16:16:49.350 
2    12.4    2    2017-10-26 16:16:49.350 
3    0.6     3    2017-10-26 16:16:49.350 
4    1.1     4    2017-10-26 16:16:49.350 
5    1.8     1    2017-10-25 14:12:24.650 
6    3.2     2    2017-10-25 14:12:24.650 
7    0.2     3    2017-10-25 14:12:24.650 
8    1.2     4    2017-10-25 14:12:24.650

Is it possible to convert these rows to columns based on type and time(either by query or stored procedure)? something like this:

(type)1     2        3        4       time
1.2        12.4     0.6      1.1     2017-10-26 16:16:49.350
1.8        3.2      0.2      1.2     2017-10-25 14:12:24.650

PS: Each four types share the same time.

score 3 · Answer 1 · answered Oct 30 '17 at 16:06

3

Here is another option using conditional aggregation or cross tab.

select Type1 = max(case when type = 1 then value)
    Type2 = max(case when type = 2 then value)
    Type3 = max(case when type = 3 then value)
    Type4 = max(case when type = 4 then value)
    , time
from YourTable
group by time

answered Oct 30 '17 at 16:06

Sean Lange

33,028
3
25
40

I personally prefer this method than the pivot method. It's more legible and takes few lines to write. – Antoine Stas Oct 31 '17 at 10:18
1

It is also slightly faster than pivot in all the tests I have done and seen. – Sean Lange Oct 31 '17 at 13:09
if there is an index on `value`, it can speed things up to add a `WHERE value in (1, 2, 3, 4)`. – cmo Aug 30 '23 at 05:31
@cmo for that index it would need to be on type. But that also implies they only want rows where those values exist which is not clear from the question. – Sean Lange Aug 30 '23 at 15:27

score 2 · Accepted Answer · answered Oct 30 '17 at 16:03

Try this:

DECLARE @DataSource TABLE
(
    [id] SMALLINT
   ,[value] DECIMAL(9,1)
   ,[type] TINYINT
   ,[time] DATETIME2
);

INSERT INTO @DataSource ([id], [value], [type], [time])
VALUES (1, 1.2,  1, '2017-10-26 16:16:49.350')
      ,(2, 12.4, 2, '2017-10-26 16:16:49.350')
      ,(3, 0.6,  3, '2017-10-26 16:16:49.350')
      ,(4, 1.1,  4, '2017-10-26 16:16:49.350')
      ,(5, 1.8,  1, '2017-10-25 14:12:24.650')
      ,(6, 3.2,  2, '2017-10-25 14:12:24.650')
      ,(7, 0.2,  3, '2017-10-25 14:12:24.650')
      ,(8, 1.2,  4, '2017-10-25 14:12:24.650');

SELECT [1], [2], [3], [4], [time]
FROM
(
    SELECT [value], [type], [time]
    FROM @DataSource
) DS
PIVOT
(
    MAX([value]) FOR [type] IN ([1], [2], [3], [4])
) PVT
ORDER BY [time] DESC;

score 0 · Answer 3 · answered Oct 30 '17 at 16:04

You can use PIVOT:

SELECT
    [1] type1
    , [2] type2
    , [3] type3
    , [4] type4
    , time
FROM 
    (
        SELECT
            value
            , type
            , time
        FROM table
    ) T
    PIVOT
    (
        SUM (value)
        FOR type IN
            (
                [1], [2], [3], [4]
            )
    ) P

convert rows to columns using date column

3 Answers3