同样的数据， 为什么PIVOT结果不相同

[顺势而为1]

如下， 请大家帮忙解惑，谢谢。

-- Test Data

	if object_id('tempdb..#Tmp_data') is not null
			drop table #Tmp_data

    Select *
	INTO #Tmp_data
	From (
				SELECT 7 as iRow,2018 as iYear,1 as iMonth,null as cBackColor,'NSW Thu 19th' as cEvent
				union
				Select 12,2018,1,null,'7;;;;'
				union 
				Select 10,2018,1,'#fff200','Gold Coast Marathon - Sun 1st'
				union 
				Select 14,2018,1,null,'Team Alignment and Core Values'
				union
				Select 13,2018,1,null,'7;;;;'
		  ) a


--TEST A
	if object_id('tempdb..#Tmp_A') is not null
			drop table #Tmp_A    

    Select * into #Tmp_A From #tmp_data

	SELECT	[1] AS cMonth1
	FROM	#Tmp_A
		PIVOT	(MAX(cEvent) FOR iMonth IN ([1])) AS pvt

--TEST B

	if object_id('tempdb..#Tmp_B') is not null
			drop table #Tmp_B    

    Select iMonth,cEvent into #Tmp_B From #tmp_data

	SELECT	[1] AS cMonth1
	FROM	#Tmp_B
		PIVOT	(MAX(cEvent)	FOR iMonth IN ([1])) AS pvt

[ダ雨夹雪リ]

你 #Tmp_A 还有3个非透视列 好吧,怎么说一样呢,你用*查询一下就知道了

--#Tmp_A
--非透视列 irow,iyear,cbackcolor
--聚合列 cEvent
--透视列 iMonth
SELECT    *
    FROM    #Tmp_A
        PIVOT    (MAX(cEvent) FOR iMonth IN ([1])) AS pvt
 
--#Tmp_B
--聚合列 cEvent
--透视列 iMonth
SELECT    *
    FROM    #Tmp_B
        PIVOT    (MAX(cEvent)    FOR iMonth IN ([1])) AS pvt
 

[二月十六]

好久不见啊……因为a表有非透视列

SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

    if object_id('tempdb..#Tmp_B') is not null
            drop table #Tmp_B    
 
    Select iMonth,cEvent,iRow into #Tmp_B From #tmp_data
 
    SELECT    *
    FROM    #Tmp_B
        PIVOT    (MAX(cEvent)    FOR iMonth IN ([1])) AS pvt

[xiaoxiangqing]

最好弄个测试数据出来，这样容易找原因

[顺势而为1]

好久不见， 回头看了下语法，明白了些

引用 2 楼 sinat_28984567 的回复:

好久不见啊……因为a表有非透视列

SELECT <非透视的列>,
    [第一个透视的列] AS <列名称>,
    [第二个透视的列] AS <列名称>,
    ...
    [最后一个透视的列] AS <列名称>,
FROM
    (<生成数据的 SELECT 查询>)
    AS <源查询的别名>
PIVOT
(
    <聚合函数>(<要聚合的列>)
FOR
[<包含要成为列标题的值的列>]
    IN ( [第一个透视的列], [第二个透视的列],
    ... [最后一个透视的列])
) AS <透视表的别名>
<可选的 ORDER BY 子句>;

    if object_id('tempdb..#Tmp_B') is not null
            drop table #Tmp_B    
 
    Select iMonth,cEvent,iRow into #Tmp_B From #tmp_data
 
    SELECT    *
    FROM    #Tmp_B
        PIVOT    (MAX(cEvent)    FOR iMonth IN ([1])) AS pvt