CODEWITHSUNDEEP
sqlsql-serversortingjoin
shibby
shibby

Reputation: 1

Table join and sorting

I have an SQL table that stores process data logged from an automation process. The table stores a variable name, logged value, timestamp. The table looks like this:

VariableName | VariableValue | Timestamp
------------------------------------------
   Tag_1     |       1       |   Time_1  
   Tag_1     |       5       |   Time_2  
   Tag_1     |       4       |   Time_5  
   Tag_1     |       8       |   Time_7   
   Tag_2     |       3       |   Time_1  
   Tag_2     |       4       |   Time_3  
   Tag_2     |       5       |   Time_6  
   Tag_2     |       7       |   Time_7  
    ...

Values of the variables are logged "on change", therefore they appear in the table with different timestamps.

To be able to export the data and use them for further processing we need to sort the table and represent it in a different form, i.e.:

Timestamp | Tag_1 | Tag_2
--------------------------
  Time_1  |   1   |   3  
  Time_2  |   5   |   -  
  Time_3  |   -   |   4  
  Time_5  |   4   |   -  
  Time_6  |   -   |   5  
  Time_7  |   8   |   7

As you can see, there are some "blind spots" in the table representing the variable values, which corresponds to the fact that simply at this time, there was no value logged for the tag, namely no change in the tag value with respect to the previously logged value.

How do I achieve this with SQL? I most probably need a new table with the structure above. However, it needs to be created dynamically, since number of columns depends on the number of variables logged (Tag_1, Tag_2, etc.).

Creating the table and querying the database is not a problem since I use VB scripts for that purpose. I do not however know how to formulate the query (a join???) to achieve that structure...

Upvotes: 0

Views: 93

Answers (3)

LukStorms
LukStorms

Reputation: 29677

Using a dynamic sql with a pivot.

First the @Columns variable for the column names is calculated.

The LogTable is used to get the tag names. But if you have a reference table with the known tags then it might be better to base it on that reference table. Since log tables tend to get big. 

DECLARE @Columns VARCHAR(max);
DECLARE @SQL VARCHAR(max);

SELECT @Columns = concat(@Columns+', ',QUOTENAME(VariableName)) FROM LogTable GROUP BY VariableName;

SET @SQL = 'SELECT * 
FROM (SELECT VariableName, VariableValue, [Timestamp] FROM LogTable) q
PIVOT (MAX(VariableValue) FOR VariableName IN ('+ @Columns +')) p';

EXEC (@SQL);

Upvotes: 1

trincot
trincot

Reputation: 351328

Yo could use this pivot query:

SELECT TimeStamp, Tag_1, Tag_2
FROM   mytable
PIVOT  (
         MIN(VariableValue)
         FOR VariableName IN ([Tag_1], [Tag_2])
       ) AS PivotTable;

See it run on rextester.com. Output:

TimeStamp | Tag_1 | Tag_2
----------+-------+-------
  Time_1  |   1   |   3
  Time_2  |   5   | (null)
  Time_3  |(null) |   4
  Time_5  |   4   | (null)
  Time_6  |(null) |   5
  Time_7  |   8   |   7

You cannot do this with a dynamic list of columns directly, but in a programming environment (or in TSQL with execute) you could build this query dynamically.

Upvotes: 3

SQLAndOtherStuffGuy
SQLAndOtherStuffGuy

Reputation: 214

Incorrect Representation of results.

enter image description here

This might work. I note that Tags might not necessarily be just Tag_1, Tag_2, in that case a more dynamic approach would be required. 

  SELECT 
[TimeStamp], ISNULL(Max(Tag_1),'-') as Tag_1,  ISNULL(Max(Tag_2),'-') as Tag_2
FROM
(SELECT * FROM dbo.Table_3) as ST
PIVOT
(
MAX(VariableValue)
FOR VariableName in ([Tag_1], [Tag_2])
) AS PT
 Group By [TimeStamp]

Correct Representation of results enter image description here

Upvotes: 0

Related Questions