MSSQL 2012 : PIVOT typologies and dates using PHP

Question

I don't understand correctly how pivot is working with MSSQL, and I'm not able to apply pivot with my needs.

I have a MSSQL 2012 Database with a "typology" content :

Date (Unix) | Typo    | Other data
1459461600  | Typo 1  | ---
1459461600  | Typo 4  | ---
1459548000  | Typo 2  | ---
1459548000  | Typo 2  | ---
1459634400  | Typo 1  | ---
...

For report reason, I need Output a table like :

Typo     | 2016-04-01 | 2016-04-02 | 2016-04-03 | ...
Typo 1   |      1     |      0     |      1     | ...
Typo 2   |      0     |      2     |      0     | ...
Typo 3   |      0     |      0     |      0     | ...
Typo 4   |      1     |      0     |      0     | ...

What I already made with help of this subject How to pivot dynamically with date as column

SELECT * INTO #Names
FROM
(
SELECT 1 ID,'Other' NAME 
UNION ALL
SELECT 2 ID,'Payment/Delivery' NAME 
UNION ALL
SELECT 3 ID,'Account cancellation ' NAME 
UNION ALL
...
) TAB

SELECT * INTO #Stockdates
FROM
(      
SELECT DISTINCT CAST(dateadd(S, [date_ticket], '1970-01-01') as date) AS [DATE] FROM [MyTable].[dbo].[ticket] WHERE date_ticket BETWEEN 1459461600 AND 1462053600
)TAB

Here #Names contains typologies and #Stockdates contains dates from 2016-04-01 to 2016-04-30

Then creating table and columns :

SELECT N.NAME,S.[DATE]
INTO #TABLE
FROM #NAMES N, #Stockdates S

DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 106) + ']', 
               '[' + CONVERT(NVARCHAR, [DATE], 106) + ']')
               FROM    (SELECT DISTINCT [DATE] FROM #TABLE) PV  
               ORDER BY [DATE]

And finally the query :

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM (
    SELECT * FROM #TABLE
) X 
PIVOT (
    COUNT(NAME) FOR [DATE] IN (' + @cols + ')
) p'
EXEC SP_EXECUTESQL @query

I know that my SQL is not correct, it's just to show you that I'm working on it for a few hours and I don't know what can I do to make it work.

I need to use these data with PHP script in order to display to user a table with typo as row and dates as columns, and if user select another month (like march) dates will be automatically updated.

Can anybody help me on this ?

EDIT:

With help of @JamieD77 and other tests, I simplified Transact 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]

PRINT @cols

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(typo_1) FOR [DATE] IN ('+@cols+')
) p'

PRINT @query

EXEC SP_EXECUTESQL @query

And it returns only one row :

01/04/2016 | 02/04/2016 | 04/04/2016 | ...
   346     |     5      |     480    | ...

I think there is something wrong with my PIVOT or previous SELECT. 346 is total amount of typo for 1st April, but I can't see details by typo. How can I add my Typo as row and number of each typo each day ? I think it's easy, but I'm working on it since this morning and I don't see obvious :)

JamieD77 · Accepted Answer · 2016-04-27T16:12:31.723

3

You're not setting your @cols value correctly for one..

should look something like this.

DECLARE @cols NVARCHAR (MAX)
SELECT  @cols = COALESCE (@cols + ',', '') + QUOTENAME(CONVERT(NVARCHAR, [DATE], 106))
FROM    (SELECT DISTINCT [DATE] FROM #TABLE) PV  
ORDER BY [DATE]

if you're still getting errors. use PRINT @query before EXEC SP_EXECUTESQL @query to see what your query looks like and try to debug it fully formed. Post your result back so we can see what the final query looks like.

Since you only have 2 fields in your pivoted query, use the aggregate on the date field if you want to see the type_1 field..

PIVOT (
    COUNT([DATE]) FOR [DATE] IN ('+@cols+')
) p

edited Apr 27 '16 at 16:12

answered Apr 27 '16 at 15:13

JamieD77

13,796
1
17
27

Thank you for your response. I corrected one error by replacing `CONVERT(NVARCHAR, [DATE], 106)` with `CONVERT(NVARCHAR, [DATE], 103)`. Now I have a result : 15 in each column but only one row. I think it's because of my PIVOT COUNT(NAME) FOR [DATE] IN (' + @cols + ') ; I need to have each typo as row – J. Grunder Apr 27 '16 at 15:23
you can add the field twice.. it thinks you're just trying to aggregate the Name column.. so do something like this in your subquery `SELECT [Name] as [Typo], * FROM #TABLE` – JamieD77 Apr 27 '16 at 15:36
See edited question, I added my progression on this topic. Thank you for your help ! – J. Grunder Apr 27 '16 at 16:00
@J.Grunder just try `PIVOT ( COUNT([DATE]) FOR [DATE] IN ('+@cols+') ) p` and see what you get – JamieD77 Apr 27 '16 at 16:03
This is incredible ! Yes, it's working ! I don't understand why COUNT DATE instead of COUNT NAME is doing a good table ! I just have now to find how can I use these SQL instructions into PHP :) Thank you again – J. Grunder Apr 28 '16 at 08:20

MSSQL 2012 : PIVOT typologies and dates using PHP

1 Answers1

Linked