Sql server pivot table

Question

1.00/5 (1 vote)

See more:

I have a logger table with timestamp, tag name and value rows that logs data each 5 seconds automatically for several tags.
I want to run query that shows data collected in pivot display - timestamp, value for tag1, value for tag2,... value for tag n...
Can please someone recommend me a way to build this query for displaying data?

What I have tried:

I tried to look for help on google and only found way to run PIVOT query with SUM/AVG of values, but not presenting values by timestamp.

Posted 19-Dec-17 4:14am

Amit Golan

Updated 22-Dec-17 8:11am

Add a Solution

Comments

ZurdoDev 19-Dec-17 14:00pm

It's hard to know how to help without seeing how your data is stored and how you want it presented. Click Improve question and add that information please.

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RossMW · Answer 1 · 2017-12-19T09:54:00

Without knowing your data structure I am guessing here, however, I've created my own below

CREATE TABLE [dbo].[Test](
	[dtime] [datetime] NULL,
	[tag] [int] NULL,
	[value] [int] NULL
) ON [PRIMARY]

Your logger is storing data every 5 seconds so the records will be unique for a given datetime and hence you can use the avg or sum and it will still return the actual value so the pivot will look something like

select * from test pivot (sum(value) for tag in ([1],[2],[3])) as tagvalue where dtime > '2017-12-20'

My test data is something like

dtime	tag	value
2017-12-20 11:30:00.000	1	2
2017-12-20 11:30:00.000	2	5
2017-12-20 11:30:00.000	3	8

and this will give a result of

2017-12-20 11:30:00.000	2	5	8

You mention a timestamp, Im assuming it is a datetime field not a timestamp field as timestamp are unique for each record and you will not be able to pivot it.

Bryian Tan · Answer 2 · 2017-12-19T17:47:00

Here is an example on how to cast a column then pivot

SQL

DECLARE  @DATA_LOG TABLE(
	[timestamp] Datetime NULL,
	[point_id] VARCHAR(10) NULL,
	[_val] VARCHAR(10) NULL
)

INSERT INTO @DATA_LOG
	SELECT GETDATE()-1,	1, 2 UNION
	SELECT GETDATE()-1,	2, 5 UNION
	SELECT GETDATE()-1,	3, 8 

select * from (select [timestamp], [point_id],cast([_val] as int) as [_val] from @DATA_LOG) p 
 pivot(sum([_val]) for point_id in ([1],[2],[3])) as tagvalue where [timestamp] >= DATEADD(day,-4,GetDate())

Output:

timestamp	            1	2	3
2017-12-18 23:44:29.703	2	5	8

Reference: PIVOT Query Conversion For Varchar(40) to int or Decimal[^]

Amit Golan · Answer 3 · 2017-12-19T16:12:00

Thanks for the lead.

My existing table's name is DATA_LOG and it has timestamp field with datetime data type, point_id field with varchar data type and _val field with varchar data type.
I need to cast the _val field to float data type, so I tried the following query and it doesn't work:

select * FROM DATA_LOG 
pivot(sum(cast(_VAL as float)) for point_id in ([1],[2],[3])) as tagvalue where timestamp >= DATEADD(day,-4,GetDate())

I can't cast the data type inside the pivot. I also tried to cast it in the select part and it still doesn't work.
where should I place the cast of the data type?

Amit Kumar · Answer 4 · 2017-12-22T08:11:00

Solution 4

Hi,
Follow the below links.

</p?

Posted 22-Dec-17 8:11am

Amit Kumar

v2

Sql server pivot table

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0