Remove NULL values from columns

Question

Using PIVOT

SELECT id, _TIMESTAMP, [Tag1], [Tag2], [Tag3], [Tag4], [Tag5], [Tag6]
FROM (SELECT id, _VALUE, _NAME, _TIMESTAMP
      FROM [dbo].[TableLogger]) AS SourceTable PIVOT (MAX(_VALUE) FOR _NAME IN ([Tag1], [Tag2], [Tag3], [Tag4], [Tag5], [Tag6])) AS PivotTable

my table looks like this:

id      _TIMESTAMP            Tag1  Tag2  Tag3  Tag4  Tag5  Tag6
1  2016-04-29 10:37:56.667    21    NULL  NULL  NULL  NULL  NULL
2  2016-04-29 10:37:56.667    NULL  0.2   NULL  NULL  NULL  NULL
3  2016-04-29 10:37:56.667    NULL  NULL  4     NULL  NULL  NULL  
4  2016-04-29 10:37:56.667    NULL  NULL  NULL  20    NULL  NULL  
5  2016-04-29 10:37:56.667    NULL  NULL  NULL  NULL  35    NULL  
6  2016-04-29 10:37:56.667    NULL  NULL  NULL  NULL  NULL  54
7  2016-04-29 10:37:58.667    32    NULL  NULL  NULL  NULL  NULL

The table has 30471 rows and can get much more. The execution of SELECT statement takes too much (like 10s). I tried to remove those NULL values with:

where  [Tag1] IS NOT NULL ;

If I wanna put simultaneously the same condition and for the [Tag2] column the table isn't anymore showed. I guess that those NULL values influence the time of SELECT execution. Is there a way to remove those NULL value so my SELECT statement executes faster? Many thanks in advance!

EDIT

My table I want to look like:

id      _TIMESTAMP            Tag1  Tag2  Tag3  Tag4  Tag5  Tag6
1  2016-04-29 10:37:56.667    21    0.2    4     20    35    54
2  2016-04-29 10:37:58.667    32    25     65    32    30    13

If youre trying to summarize by timestamp you need to remove `id` from your subquery that you are pivoting and the main Select statement — JamieD77, May 03 '16 at 12:24

JamieD77 · Answer 1 · 2016-05-04T19:50:05.487

Sometimes you get better performance using Case Expressions instead of Pivot. To do a true Pivot though you can't have unique fields (ID) that aren't part of the aggregate or you get those null values

SELECT _TIMESTAMP,
       MAX(CASE WHEN _NAME = 'Tag1' THEN _VALUE END) AS Tag1,
       MAX(CASE WHEN _NAME = 'Tag2' THEN _VALUE END) AS Tag2,
       MAX(CASE WHEN _NAME = 'Tag3' THEN _VALUE END) AS Tag3,
       etc..
FROM   TableLogger
GROUP BY _TIMESTAMP

Test Cases

DECLARE @TableLogger TABLE (id int, _TIMESTAMP datetime, _NAME varchar(MAX), _VALUE FLOAT)
INSERT INTO @TableLogger VALUES
(1, '2016-04-29 10:37:56.667', 'Tag1', 21),
(2, '2016-04-29 10:37:56.667', 'Tag2', 0.2),
(3, '2016-04-29 10:37:56.667', 'Tag3', 4),
(4, '2016-04-29 10:37:56.667', 'Tag4', 20),
(5, '2016-04-29 10:37:56.667', 'Tag5', 35),
(6, '2016-04-29 10:37:56.667', 'Tag6', 54),
(7, '2016-04-29 10:37:58.667', 'Tag1', 32),
(8, '2016-04-29 10:37:58.667', 'Tag2', 25),
(9, '2016-04-29 10:37:58.667', 'Tag3', 65),
(10, '2016-04-29 10:37:58.667', 'Tag4', 32),
(11, '2016-04-29 10:37:58.667', 'Tag5', 30),
(12, '2016-04-29 10:37:58.667', 'Tag6', 13)

-- CORRECT WAY TO PIVOT
SELECT  _TIMESTAMP,
        MAX(CASE WHEN _NAME = 'Tag1' THEN _VALUE END) AS Tag1,
        MAX(CASE WHEN _NAME = 'Tag2' THEN _VALUE END) AS Tag2,
        MAX(CASE WHEN _NAME = 'Tag3' THEN _VALUE END) AS Tag3,
        MAX(CASE WHEN _NAME = 'Tag4' THEN _VALUE END) AS Tag4,
        MAX(CASE WHEN _NAME = 'Tag5' THEN _VALUE END) AS Tag5,
        MAX(CASE WHEN _NAME = 'Tag6' THEN _VALUE END) AS Tag6
FROM    @TableLogger
GROUP BY _TIMESTAMP

-- INCORRECT WAY TO PIVOT
-- Including ID will cause each row to only have one value
SELECT  ID
        _TIMESTAMP,
        MAX(CASE WHEN _NAME = 'Tag1' THEN _VALUE END) AS Tag1,
        MAX(CASE WHEN _NAME = 'Tag2' THEN _VALUE END) AS Tag2,
        MAX(CASE WHEN _NAME = 'Tag3' THEN _VALUE END) AS Tag3,
        MAX(CASE WHEN _NAME = 'Tag4' THEN _VALUE END) AS Tag4,
        MAX(CASE WHEN _NAME = 'Tag5' THEN _VALUE END) AS Tag5,
        MAX(CASE WHEN _NAME = 'Tag6' THEN _VALUE END) AS Tag6
FROM    @TableLogger
GROUP BY ID, _TIMESTAMP

-- WAYS TO INCLUDE ROW NUMBER OR ID IN PIVOT
SELECT  ROW_NUMBER() OVER (ORDER BY _TIMESTAMP) AS [RowNumber],
        MIN(ID) [MinID],
        _TIMESTAMP,
        MAX(CASE WHEN _NAME = 'Tag1' THEN _VALUE END) AS Tag1,
        MAX(CASE WHEN _NAME = 'Tag2' THEN _VALUE END) AS Tag2,
        MAX(CASE WHEN _NAME = 'Tag3' THEN _VALUE END) AS Tag3,
        MAX(CASE WHEN _NAME = 'Tag4' THEN _VALUE END) AS Tag4,
        MAX(CASE WHEN _NAME = 'Tag5' THEN _VALUE END) AS Tag5,
        MAX(CASE WHEN _NAME = 'Tag6' THEN _VALUE END) AS Tag6
FROM    @TableLogger
GROUP BY _TIMESTAMP

-- A CORRECT PIVOT QUERY
SELECT * FROM
(
    SELECT  _TIMESTAMP, _NAME, _VALUE
    FROM    @TableLogger tl
) t
PIVOT
(
    MAX(_VALUE)
    FOR _NAME IN ([Tag1],[Tag2],[Tag3],[Tag4],[Tag5],[Tag6])
) pt

-- AN INCORRECT PIVOT QUERY
SELECT * FROM
(
    SELECT  ID, -- OOPS, included ID
            _TIMESTAMP, _NAME, _VALUE
    FROM    @TableLogger tl
) t
PIVOT
(
    MAX(_VALUE)
    FOR _NAME IN ([Tag1],[Tag2],[Tag3],[Tag4],[Tag5],[Tag6])
) pt

-- GIMME THOSE DARN ID's IN MA PIVOT
SELECT  ROW_NUMBER() OVER (ORDER BY _TIMESTAMP) AS [RowNumber], 
        *
FROM
(
    SELECT  MIN(ID) OVER (PARTITION BY _TIMESTAMP) [MinID], 
            _TIMESTAMP, _NAME, _VALUE
    FROM    @TableLogger tl
) t
PIVOT
(
    MAX(_VALUE)
    FOR _NAME IN ([Tag1],[Tag2],[Tag3],[Tag4],[Tag5],[Tag6])
) pt

It does what I want but the problem is that not all the values are taken. I mean instead of 30471 rows, I get only 5120 rows. Any idea why's that happening? :) — magn, May 03 '16 at 19:37
@magn have a better understanding of what you're trying to accomplish here. You have shown us what result you're getting from your query, but you have not shown us what result you're expecting. — JamieD77, May 03 '16 at 20:45
@magn how is your expected result different than what this answer produces? — JamieD77, May 04 '16 at 13:45
@magn I'm going to give up trying to help you now.. but i'll leave a full test case for you so maybe you can figure out what you're doing wrong. — JamieD77, May 04 '16 at 19:50

Remove NULL values from columns

1 Answers1