MSSQL 2012 : PIVOT typologies and dates using PHP

问题:

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 :)

回答1:

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