您可以通过在
ORDER BY设置
@cols字符串时添加来调整动态数据透视查询中字段的顺序:
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('Month'+cast(DATEPART(m, [Inv Date]) as varchar(2))) from #TempTable ORDER BY .... FOR XML PATH(''), TYPE).value('.', 'NVARCHAr(MAX)') ,1,1,'')
更新:
DISTINCT首先错过了,使用时
DISTINCT必须先使用子查询,然后再使用
ORDER BY:
SELECt @cols = STUFF((SELECT ',' + QUOTENAME(ColName) FROM (SELECt DISTINCT 'Month'+cast(DATEPART(m, [Inv Date]) as varchar(2)) ColName FROM #TempTable )sub ORDER BY ColName FOR XML PATH(''), TYPE).value('.', 'NVARCHAr(MAX)') ,1,1,'')
如果您不能简单地使用列名,则可能需要在子查询中添加“排序”字段,并且可以在子查询中添加任何字段,只要它们不会破坏
DISTINCT列表即可。例如:
SELECt @cols = STUFF((SELECT ',' + QUOTENAME(ColName) FROM (SELECt DISTINCT 'Month'+cast(DATEPART(m, [Inv Date]) as varchar(2)) ColName ,CASE WHEN field = 'something' THEN 1 WHEN field = 'something else' THEN 2 ELSE 3 END as Sort ,Cust_ID FROM #TempTable )sub ORDER BY Sort,Cust_ID FOR XML PATH(''), TYPE).value('.', 'NVARCHAr(MAX)') ,1,1,'')
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)