SELECT
CASE WHEN ROW_NUMBER() OVER ( ORDER BY transaction_week ) = 1
THEN '' ELSE ',' END
|| '[''' || transaction_week || ''',''' || COUNT ( DISTINCT storename ) || ''']'
FROM pos.transactions_powerbi
WHERE transaction_week >= DATE '2017-01-01'
GROUP BY transaction_week
ORDER BY transaction_week;

---

SELECT
CASE WHEN ROW_NUMBER() OVER ( ORDER BY transaction_week ) = 1
THEN '' ELSE ',' END
|| '[''' || transaction_week || ''',''' || SUM( sale_amount )
|| ''',''' || TO_CHAR ( SUM ( sale_amount ), '9G999G999G990D00' )
|| ''']'
FROM pos.transactions_powerbi
WHERE transaction_week >= DATE '2017-01-01'
GROUP BY transaction_week
ORDER BY transaction_week;

SELECT DISTINCT transaction_week
INTO #weeks
FROM pos.transactions_powerbi
WHERE transaction_week >= DATE '2017-01-01';
 

SELECT DISTINCT storeid
INTO #stores
FROM pos.transactions_powerbi;
 

SELECT
  CASE WHEN ROW_NUMBER()
            OVER ( ORDER BY s.storeid, w.transaction_week ) = 1
       THEN '' ELSE ',' END
|| '[''' || w.transaction_week || ''',''' || s.storeid || ''','''
|| ( SELECT COUNT( DISTINCT storeid ) datacolumn
FROM pos.transactions_powerbi
WHERE transaction_week = w.transaction_week
AND storeid = s.storeid
LIMIT 1 )
|| ''']'
FROM #weeks w
CROSS JOIN #stores s
ORDER BY s.storeid, w.transaction_week;