我不能正确理解透视如何与MSSQL一起工作,并且我无法根据自己的需要应用透视。
我有一个MSSQL 2012数据库,其中包含"类型学"内容:
Date (Unix) | Typo | Other data
1459461600 | Typo 1 | ---
1459461600 | Typo 4 | ---
1459548000 | Typo 2 | ---
1459548000 | Typo 2 | ---
1459634400 | Typo 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,'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
此处 #Names 包含类型,#Stockdates 包含从 2016-04-01 到 2016-04-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和其他测试的帮助下,我简化了事务处理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
它只返回一行:
01/04/2016 | 02/04/2016 | 04/04/2016 | ...
346 | 5 | 480 | ...
我认为我的 PIVOT 或以前的选择有问题。 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]
如果您仍然收到错误。 在EXEC SP_EXECUTESQL @query
之前使用PRINT @query
查看查询的外观,并尝试调试其完全成型。 将结果发回,以便我们查看最终查询的外观。
由于透视查询中只有 2 个字段,因此如果要查看type_1字段,请使用日期字段上的聚合。
PIVOT (
COUNT([DATE]) FOR [DATE] IN ('+@cols+')
) p