4 Replies - 3616 Views - Last Post: 13 June 2012 - 06:56 AM Rate Topic: -----

#1 pcaddict  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 45
  • Joined: 11-February 09

Query Result Formatting

Posted 08 June 2012 - 12:31 PM

I have inherited a custom piece of software that stores quite a bit of data in a way that I think could be greatly improved. Currently log data is stored in individual tables with their own formatting based on how the data needs to be exported. Generally however, the data we are storing is simply a float value and a date/time.

In order to improve data storage, simplicity for adding new reports (export format), and maintainability, I have started working on a new layout that is greatly simplified. The problem that I have however is getting data that is in rows to transform into columns. The attached images should help illustrate what I am aiming for.

The following image shows a first draft if you will of my envisioned table structure.
Attached Image

When queried, I want the point name, of which there are many, to become a column header and have the associated value become the row data. In the existing database's form, what I am asking above is achieved by creating a table that has each PointName as a column and the rows consist of CollectionValues.

This query produces the next image:
SELECT Points.PointName, Values.CollectionValue, Values.TimeStamp
FROM (Collection INNER JOIN Points ON Collection.CollectionID = Points.CollectionID) INNER JOIN [Values] ON Points.ID = Values.PID
WHERE Collection.CollectionName = 'Test Collection 2';


Attached Image

The end result I want would look like this:
testpoint4 | testpoint5 | testpoint6 | TimeStamp
-------------------------------------------------
16           45           25
35           35           46
0            23           34


While the TimeStamps do not align in the image above, they would in practice. Is what I am asking possible? A different layout is fine, I am just looking for a better way than individual tables for each layout when there are only two values I am storing and formatting is the only change. It seems that I should be able to use SQL to accomplish what these individual tables are.

I used MS Access above simply for convenience. The end result will use MySQL or MsSQL.

Is This A Good Question/Topic? 0
  • +

Replies To: Query Result Formatting

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: Query Result Formatting

Posted 08 June 2012 - 03:50 PM

View Postpcaddict, on 09 June 2012 - 06:31 AM, said:

Attachment query result.PNG

The end result I want would look like this:
testpoint4 | testpoint5 | testpoint6 | TimeStamp
-------------------------------------------------
16           45           25
35           35           46
0            23           34


I don't see how these two sets of data could possibly relate to one another.

You might want to post the data that is in the individual tables rather than data generated by a query, and then get the output format you want right. Stating "While the TimeStamps do not align in the image above, they would in practice" is no good - get the data where it should be, otherwise there's no way for someone to determine what the accurate solution is.
Was This Post Helpful? 0
  • +
  • -

#3 pcaddict  Icon User is offline

  • New D.I.C Head

Reputation: 6
  • View blog
  • Posts: 45
  • Joined: 11-February 09

Re: Query Result Formatting

Posted 11 June 2012 - 10:25 AM

Here are some examples of the current database schema and a description of their purpose. All of the data below can be copied and pasted into your spreadsheet editor or choice to view it in a readable format.

Table: wdcTables
Description: All of the records in this table represent collection jobs that need to be run. The collection service references the tableName and collects data for all of the points in wdcPoints that have a matching tableName.
serverName	databaseName	tableName	ddeCollection	ddeFrequency	startTime	nextRunTime	webctrlServer	TimeStampField	RealTimeStamp	CollectionType	maxTrendSamples
ALC1\DATACOLLECTOR                                	fbwf                                              	DCB7_Fuel_trends                                  	8	Hours     	2009-09-16 08:00:00.000	2012-06-11 16:08:14.000	webctrl1            	CollectionTimeStamp	null	TRENDS	8



Table: wdcPoints
Description: The records in this table are used by the collection service to gather the data. The value the program acquires comes from the bacnetPoint. The returned data is a float value and a date/time stamp. These values, and any other that have the same tableName, are stored in a table named the same as the value in the tableName field.
enabled	overwrite	tableName	fieldName	bacnetPoint	dataType	fieldId	keyField
1	0	DCB7_FUEL_TRENDS	boiler1_nat_gas_today	#boiler_1_fuel_monitor/n630	nvarchar	null	1414
1	0	DCB7_FUEL_TRENDS	boiler2_nat_gas_today	#boiler_2_fuel_monitor/n630	nvarchar	null	1416
1	0	DCB7_FUEL_TRENDS	boiler2_fuel_oil_today	#boiler_2_fuel_monitor/n570	nvarchar	null	1419
1	0	DCB7_FUEL_TRENDS	boiler4_nat_gas_today	#boiler_4_fuel_monitor/n630	nvarchar	null	1420
1	0	DCB7_FUEL_TRENDS	boiler4_fuel_oil_today	#boiler_4_fuel_monitor/n570	nvarchar	null	1421
1	0	DCB7_FUEL_TRENDS	boiler5_nat_gas_today	#boiler_5_fuel_monitor/n630	nvarchar	null	1422
1	0	DCB7_FUEL_TRENDS	boiler5_fuel_oil_today	#boiler_5_fuel_monitor/n570	nvarchar	null	1423
1	0	DCB7_FUEL_TRENDS	boiler6_nat_gas_today	#boiler_6_fuel_monitor/n630	nvarchar	null	1424
1	0	DCB7_FUEL_TRENDS	boiler6_fuel_oil_today	#boiler_6_fuel_monitor/n570	nvarchar	null	1425
1	0	DCB7_FUEL_TRENDS	boiler1_fuel_oil_today	#boiler_1_fuel_monitor/n570	nvarchar	null	1415



Table: DCB7_FUEL_TRENDS
Description: This table, and many very similar to it, stores the data for each point above.
CollectionTimeStamp	boiler1_nat_gas_today	boiler1_nat_gas_today_ts	boiler1_fuel_oil_today	boiler1_fuel_oil_today_ts	boiler2_nat_gas_today	boiler2_nat_gas_today_ts	boiler2_fuel_oil_today	boiler2_fuel_oil_today_ts	boiler4_nat_gas_today	boiler4_nat_gas_today_ts	boiler4_fuel_oil_today	boiler4_fuel_oil_today_ts	boiler5_nat_gas_today	boiler5_nat_gas_today_ts	boiler5_fuel_oil_today	boiler5_fuel_oil_today_ts	boiler6_nat_gas_today	boiler6_nat_gas_today_ts	boiler6_fuel_oil_today	boiler6_fuel_oil_today_ts	keyfield
2012-06-10 08:08:31.030	264.41928	2012-06-10 03:00:00.000	397.5907	2012-06-10 03:00:00.000	279.68225	2012-06-10 03:00:00.000	514.09955	2012-06-10 03:00:00.000	128.99031	2012-06-10 03:00:00.000	104.83101	2012-06-10 03:00:00.000	258.79706	2012-06-10 03:00:00.000	518.47003	2012-06-10 03:00:00.000	0.0	2012-06-10 03:00:00.000	0.0	2012-06-10 03:00:00.000	22477
2012-06-10 08:08:31.030	264.41928	2012-06-10 04:00:00.000	397.5907	2012-06-10 04:00:00.000	279.68225	2012-06-10 04:00:00.000	514.09955	2012-06-10 04:00:00.000	128.99031	2012-06-10 04:00:00.000	104.83101	2012-06-10 04:00:00.000	258.79706	2012-06-10 04:00:00.000	518.47003	2012-06-10 04:00:00.000	0.0	2012-06-10 04:00:00.000	0.0	2012-06-10 04:00:00.000	22478
2012-06-10 08:08:31.030	264.41928	2012-06-10 05:00:00.000	397.5907	2012-06-10 05:00:00.000	279.68225	2012-06-10 05:00:00.000	514.09955	2012-06-10 05:00:00.000	128.99031	2012-06-10 05:00:00.000	104.83101	2012-06-10 05:00:00.000	258.79706	2012-06-10 05:00:00.000	518.47003	2012-06-10 05:00:00.000	0.0	2012-06-10 05:00:00.000	0.0	2012-06-10 05:00:00.000	22479
2012-06-10 08:08:31.030	264.41928	2012-06-10 06:00:00.000	397.5907	2012-06-10 06:00:00.000	279.68225	2012-06-10 06:00:00.000	514.09955	2012-06-10 06:00:00.000	128.99031	2012-06-10 06:00:00.000	104.83101	2012-06-10 06:00:00.000	258.79706	2012-06-10 06:00:00.000	518.47003	2012-06-10 06:00:00.000	0.0	2012-06-10 06:00:00.000	0.0	2012-06-10 06:00:00.000	22480
2012-06-10 08:08:31.030	264.41928	2012-06-10 07:00:00.000	397.5907	2012-06-10 07:00:00.000	279.68225	2012-06-10 07:00:00.000	514.09955	2012-06-10 07:00:00.000	128.99031	2012-06-10 07:00:00.000	104.83101	2012-06-10 07:00:00.000	258.79706	2012-06-10 07:00:00.000	518.47003	2012-06-10 07:00:00.000	0.0	2012-06-10 07:00:00.000	0.0	2012-06-10 07:00:00.000	22481



As you can see, there is an incredible amount of redundant data and the possibility of any of these rows to become malformed due to a time stamp mismatch is very easy. What I was looking for in my original post is a way to simplify the table structure and rebuild the last table above and every one like it by using queries rather than individual tables for each report I need to generate.

This post has been edited by pcaddict: 11 June 2012 - 10:30 AM

Was This Post Helpful? 0
  • +
  • -

#4 rgfirefly24  Icon User is online

  • D.I.C Lover
  • member icon


Reputation: 262
  • View blog
  • Posts: 1,461
  • Joined: 07-April 08

Re: Query Result Formatting

Posted 13 June 2012 - 06:29 AM

if you know the names of each of the points I.E. point1, point2, ect. you could probably get away with using either a PIVOT command or using case statements in your select list.

if the list of points is ever changing then you'd have to look at using dynamic sql in order to do your pivot statement.
Was This Post Helpful? 0
  • +
  • -

#5 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5780
  • View blog
  • Posts: 12,596
  • Joined: 16-October 07

Re: Query Result Formatting

Posted 13 June 2012 - 06:56 AM

There's no standard way to do a "pivot" or "crosstab" in SQL. But each database usually offers a proprietary mechanism. This is often done in report engines or with scripts.

If you don't need to column names dynamic, then you can usually get away with a group by. e.g.
SELECT 
		sum(case when p.PointName='testpoint4' then v.CollectionValue else 0 end) as testpoint4,
		sum(case when p.PointName='testpoint5' then v.CollectionValue else 0 end) as testpoint5,
		sum(case when p.PointName='testpoint6' then v.CollectionValue else 0 end) as testpoint6,
		v.TimeStamp
	FROM Collection c
		INNER JOIN Points p
			ON c.CollectionID = p.CollectionID
				AND p.PointName in ('testpoint4', 'testpoint5', 'testpoint6')
		INNER JOIN [Values] v
			ON p.ID = v.PID
	WHERE c.CollectionName = 'Test Collection 2';
	GROUP BY v.TimeStamp	


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1