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.

Sean Lange · Accepted Answer · 2017-10-30 16:06:51Z

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, 2017 at 16:06

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Antoine Stas Over a year ago

I personally prefer this method than the pivot method. It's more legible and takes few lines to write.

Sean Lange Over a year ago

It is also slightly faster than pivot in all the tests I have done and seen.

cmo Over a year ago

if there is an index on value, it can speed things up to add a WHERE value in (1, 2, 3, 4).

Sean Lange Over a year ago

@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.

gotqn · Accepted Answer · 2017-10-30 16:03:53Z

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;

Chris Mack · Accepted Answer · 2017-10-30 16:04:29Z

0

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

answered Oct 30, 2017 at 16:04

Chris Mack

5,2082 gold badges16 silver badges31 bronze badges

Collectives™ on Stack Overflow

convert rows to columns using date column

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related