1 Replies - 396 Views - Last Post: 01 August 2019 - 03:56 PM Rate Topic: -----

#1 adityaparakh   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 01-August 19

How to Transpose a SQL output , while also ensuring the count.

Posted 01 August 2019 - 07:34 AM

How to Transpose a SQL output , while also ensuring the count.

Present output of the below SQL query is :

Time | SumValue(CE+PE)

2 columns - ensuring the number of output is exactly ten.

How can I transpose it to :

Time | CE1Value | CE2Value |... |CE5Value | PE1Value | ... PE5Value

11 columns.

Ensuring that there are all the ten records for the same specific time , else skip to the next minute (next record). I am assuming that it is sorted by time,by default else can I use order by - is it optimum.

Using a standalone vbs file in windows against data in MS Access.

mySQL = "SELECT [Data.Time],"& Quantity &"*Sum([Data.Price]) AS SumOfPrice FROM Data WHERE (  ( [Data.Ticker] Like '"& CE1 &"'  Or [Data.Ticker] Like '"& CE2 &"' Or [Data.Ticker] Like '"& CE3 &"' Or [Data.Ticker] Like '"& CE4 &"' Or [Data.Ticker] Like '"& CE5 &"' Or [Data.Ticker] Like '"& PE1 &"'  Or [Data.Ticker] Like '"& PE2 &"'  Or [Data.Ticker] Like '"& PE3 &"'  Or [Data.Ticker] Like '"& PE4 &"'  Or [Data.Ticker] Like '"& PE5 &"'  ) AND ([Data.DateTr]=#"& DateIn &"#)) GROUP BY [Data.Time] HAVING [Data.Time] > #"& startTime(i) &"# and (((Count([Data.Ticker]))=10))"



Eg. For understanding : The data table is :

DateTr|Ticker|Time|Price
22July2016|Apple|3:20 AM|15
22July2016|Apple|3:21 AM|16
22July2016|Apple|3:23 AM|14
22July2016|Amazon|3:20 AM|16
22July2016|Amazon|3:21 AM|14
22July2016|Amazon|3:22 AM|14
22July2016|Amazon|3:23 AM|14

Eg. Apple data is missing for 3:22 , so skip and move onto next.
But ensure for the same time - the count is two
(in this case Apple+Amazon) ten in my case. CE1 = Apple , PE1 = Amazon Earlier Output :

3:20|31 3:21|30 3:23|28

New Output required :

3:20|15|16 3:21|16|14 so on.

Is This A Good Question/Topic? 0
  • +

Replies To: How to Transpose a SQL output , while also ensuring the count.

#2 Martyr2   User is online

  • Programming Theoretician
  • member icon

Reputation: 5457
  • View blog
  • Posts: 14,407
  • Joined: 18-April 07

Re: How to Transpose a SQL output , while also ensuring the count.

Posted 01 August 2019 - 03:56 PM

I am not sure if you are wanting to display the 3:22 minute or not in the output. Something like this?

3:20|15|16 3:21|16|14 3:22|NA|14

Obviously NA being "Not Available"
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1