View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.charting
[email protected] godsgr8est@gmail.com is offline
external usenet poster
 
Posts: 1
Default Can SQL report multiple columns based on data in one DB column?




Greetings Everyone,
I am working with SQL in Excel to access a small database.
One of the columns (named "Tagindex") contains numbers 1-11.

I would like to create a report that contains separate columns for each Tagindex number.

Each record contains Timestamp, Tagindex, Max value, Min value and average value so what I want will look like this.

Tag1 Timestamp Tag1 max Tag1 Min Tag1 Avg Tag2 Timestamp Tag2 max ETC......

I can pull up a table that has all the data in 5 columns but what I would like would have about 44 columns.

Can SQL create this report?



The code I use to obtain the 5 columns looks like this.


SELECT convert(char(10),HistoricalData.DateAndTime,120) AS 'Groupby date', Max(HistoricalData.DateAndTime) AS 'Timestamp',
avg(HistoricalData.Val/10) AS 'Avg Value',
Max(HistoricalData.Val/10) AS 'Max Value', Min(HistoricalData.Val/10) AS 'Min Value',
(HistoricalData.TagIndex)

FROM rsview.dbo.HistoricalData HistoricalData

WHERE (HistoricalData.DateAndTime?) AND (HistoricalData.DateAndTime<?) AND (HistoricalData.Val <0)

GROUP BY convert(char(10), HistoricalData.DateAndTime,120),(HistoricalData.Ta gIndex)

ORDER BY convert(char(10), HistoricalData.DateAndTime,120)


The "?" parameters are date related values on the Excel worksheet.



Thanks Glen