Next to this question : MSSQL 2012 : PIVOT typologies and dates using PHP I have this result into Management Studio :
typo 01/04/2016 | 02/04/2016 | 04/04/2016 | 05/04/2016 | 06/04/2016 | 07/04/2016 | 08/04/2016 | 11/04/2016 |
TYPO1 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
TYPO2 13 | 0 | 29 | 30 | 24 | 18 | 17 | 22 |
TYPO3 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
TYPO4 6 | 1 | 12 | 16 | 17 | 6 | 18 | 11 |
TYPO5 44 | 1 | 80 | 62 | 84 | 116 | 103 | 100 |
How can I display this result in PHP ? I start with :
$SQL = "
DECLARE @cols NVARCHAR (MAX);
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 103) + ']',
'[' + CONVERT(NVARCHAR, [DATE], 103) + ']')
FROM (SELECT DISTINCT [DATE] FROM (SELECT DISTINCT CAST(dateadd(S, [date_ticket], '1970-01-01') as date) AS [DATE] FROM [MyBase].[dbo].[ticket] WHERE date_ticket BETWEEN 1459461600 AND 1462053600) PV) PT
ORDER BY [DATE];
DECLARE @query NVARCHAR(MAX);
SET @query = 'SELECT * FROM (
SELECT
typo_1,
CAST(dateadd(S, [date_ticket], ''1970-01-01'') as date) AS [DATE]
FROM
[MyBase].[dbo].[ticket]
WHERE
date_ticket BETWEEN 1459461600 AND 1462053600
) X
PIVOT (
COUNT([DATE]) FOR [DATE] IN ('+@cols+')
) p';
EXEC SP_EXECUTESQL @query;";
$result = sqlsrv_query($connexion, $SQL);
This result is based on April, and dates can change with choice of user (all the month or 3 days, etc.) ; so I would like to make an Excel export of this but how can I structure the output table ? Must I first do a loop on each column for each date ? Is there any way in PHP to copy this result table and paste it into Excel file ?
