How to use PIVOT on SQL with additional criteria like colum count check and grouping?

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 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.

Ticker is matched with CE1 , CE2 , PE2 etc.

Thanks

Answer

If data resides in MS Access, consider the crosstab query which you can explicitly specify the 10 columns with PIVOT clause:

TRANSFORM SUM(t.Price) As SumPrice
SELECT t.[Time]
FROM mytable t
GROUP BY t.[Time]
PIVOT t.ticker IN ('Apple', 'Cisco', 'Oracle', 'Microsoft', 'IBM',
                   'Amazon', 'Alphabet', 'Intel', 'Adobe', 'SAP')

Alternatively, run the conditional aggregation, again explicitly specifying the 10 columns with 10 expressions. This approach allows the flexibility of renaming columns such as suffixing _Value.

SELECT t.Time,
       SUM(IIF(t.ticker = 'Apple', t.Price, NULL)) AS CE1_Value,
       SUM(IIF(t.ticker = 'Cisco', t.Price, NULL)) AS CE2_Value,
       SUM(IIF(t.ticker = 'Oracle', t.Price, NULL)) AS CE3_Value,
       ...
       SUM(IIF(t.ticker = 'Amazon', t.Price, NULL)) AS PE1_Value,
       SUM(IIF(t.ticker = 'Alphabet', t.Price, NULL)) AS PE2_Value,
       SUM(IIF(t.ticker = 'Intel', t.Price, NULL)) AS PE3_Value,
       ...
FROM mytable t
GROUP BY t.Time

---

Should values need to be passed into query, as I recommended in your last question consider ADO parameterization. Alternatively, with DAO (Access’ default API) you can save a prepared statement as an Access query object using PARAMETERS clause and bind values in code using QueryDefs at runtime. Both approaches avoid messy concatenation and punctuation as seen in your mysql variable.

DAO

SQL (save as an Access query; no quotes or concatenation)

PARAMETERS [DateIn] Date, [Qty_Param] Long,
           [CE1_Param] Text, [CE2_Param] Text, [CE3_Param] Text, [CE4_Param] Text, [CE5_Param] Text, 
           [PE1_Param] Text, [PE2_Param] Text, [PE3_Param] Text, [PE4_Param] Text, [PE5_Param] Text;
SELECT t.DateTr, t.[Time],
       [Qty_Param] * SUM(IIF(t.ticker = [CE1_Param], t.Price, NULL)) AS CE1_Value,
       [Qty_Param] * SUM(IIF(t.ticker = [CE2_Param], t.Price, NULL)) AS CE2_Value,
       [Qty_Param] * SUM(IIF(t.ticker = [CE3_Param], t.Price, NULL)) AS CE3_Value,
       ...
       [Qty_Param] * SUM(IIF(t.ticker = [PE1_Param], t.Price, NULL)) AS PE1_Value,
       [Qty_Param] * SUM(IIF(t.ticker = [PE2_Param], t.Price, NULL)) AS PE2_Value,
       [Qty_Param] * SUM(IIF(t.ticker = [PE3_Param], t.Price, NULL)) AS PE3_Value,
       ...
FROM mytable t
WHERE t.DateTr = [DateIn])
GROUP BY t.DateTr, t.[Time]

VBA

Dim db As DAO.Database, rst As DAO.Recordset, qdef As DAO.QueryDef

Set db = CurrentDb
Set qdef = db.QueryDefs("mySavedQuery")

With qdef
    .Parameters("DateIn") = CDate("2016-07-22")
    .Parameters("Qty_Param") = ###
    .Parameters("CE1_Param") = "Apple"
    .Parameters("CE2_Param") = "..."
    .Parameters("CE3_Param") = "..."
    ...
    .Parameters("PE1_Param") = "Amazon"
    .Parameters("PE2_Param") = "..."
    .Parameters("PE3_Param") = "..."
    ...
End With

Set rst = qdef.OpenRecordset()

...

rst.Close: qdef.Close
Set rst = Nothing: Set qdef = Nothing: Set db = Nothing