MSSQL 2012：PIVOT类型和日期使用PHP [英] MSSQL 2012 : PIVOT typologies and dates using PHP

问题描述

我无法正确理解使用MSSQL的支持点，而且我无法根据需要应用数据。

我有一个MSSQL 2012数据库使用类型内容：

  Date（Unix）|打字|其他数据
 1459461600 |错字1 | --- 
 1459461600 |错字4 | --- 
 1459548000 | Typo 2 | --- 
 1459548000 | Typo 2 | --- 
 1459634400 |错字1 | --- 
 ... 
  

对于报告原因，我需要输出一个表，如：

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

我已经通过此主题的帮助制作了如何以日期为动态点列

  SELECT * INTO #Names 
 FROM 
（
 SELECT 1 ID，'other'NAME 
 UNION ALL 
 SELECT 2 ID，'付款/交货'NAME 
 UNION ALL 
选择3 ID，取消帐户NAME 
 UNION全部
 ... 
）TAB 
 
 SELECT * INTO #Stockdates 
 FROM 
（
 SELECT DISTINCT CAST（date，（date，date），'1970-01-01'）as as）AS [DATE] FROM [MyTable]。[dbo]。[ticket] WHERE date_ticket BETWEEN 1459461600 AND 1462053600 
）TAB 
  

这里#Names包含类型，#Stockdates包含从2016-04-01到20164-30的日期

然后创建表和列：

  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] 
  

最后查询：

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

我知道我的SQL不正确，只是告诉你我正在处理好几个小时，我不知道该怎么做它的工作。

我需要使用这些数据与PHP脚本，以向用户显示一个打印为行和日期为列的表，如果用户选择另一个月（像三月）日期我会自动更新。

有人可以帮我吗？

编辑：

在@ JamieD77和其他测试的帮助下，我简化了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]，' -01'）作为日期）AS [DATE] FROM [MyBase]。[dbo]。[ticket] WHERE date_ticket BETWEEN 1459461600 AND 1462053600）PV $ p $ b 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 as）AS [DATE] 
 FROM 
 [MyBase]。[dbo]。[ticket] 
 WHERE 
 date_ticket BETWEEN 145946 1600和1462053600 
）X 
 PIVOT（
 COUNT（typo_1）FOR [DATE] IN（'+ @ cols +'）
）p'
 
 PRINT @query 
 
 EXEC SP_EXECUTESQL @query 
  

它只返回一行：

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

我认为我的PIVOT或以前的SELECT有问题。 346是4月1日的错字总数，但是我看不到打字错误的细节。我如何每天输入错字的行号和号码呢？我觉得很简单，但是我从今天早上开始工作，我看不清楚：）

解决方案

你'不要正确设置你的 @cols 的值。

应该是这样的。 >

  DECLARE @cols NVARCHAR（MAX）
 SELECT @cols = COALESCE（@cols +'，'，''）+ QUOTENAME （CONVERT（NVARCHAR，[DATE]，106））
 FROM（SELECT DISTINCT [DATE] FROM #TABLE）PV 
 ORDER BY [DATE] 
  pre> 
 
 
如果您仍然收到错误。在 EXEC SP_EXECUTESQL @query 之前，使用 PRINT @query 来查看您的查询是什么样子，并尝试完全调试。发布你的结果，所以我们可以看到最终的查询是什么样的。
 
 
 
由于您的枢轴查询中只有2个字段，所以在日期字段中使用聚合想要看到type_1字段.. 
  PIVOT（
 COUNT（[DATE]）FOR [DATE] IN '+ @ cols +'）
）p 
  
 
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 :)
解决方案
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 


                        
这篇关于MSSQL 2012：PIVOT类型和日期使用PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！