0
Posted on
Thursday, August 14, 2014
by
醉·醉·鱼
and labeled under
sql
问题
有如下一张表，需要创建一个PIVOT表，用来显示每个registration买了多少个product。解决
- 当数据量比较小的时候，可以直接用下面的SQL
- 但是当数据量比较多时候，你需要创建动态query去实现。麻烦的地方就是动态生成那一串product_id。
- QUOTENAME(product_id) 将product_id用标识符包括起来，即[38252013].
- ', ' + QUOTENAME(product_id) 在每个product_id前面加一个逗点，即', [38252013]'。方便后面拼接用。
- FOR XML PATH将返回结果拼成一行，即<row>, [38252013]</row><row>, [38252113]</row>...
- FOR XML PATH将结果的节点名去掉，即, [38252013], [38252113]...
- STUFF((), 1, 2, '')将第一个逗点去掉。
- 注意: 参考http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits， 避免动态query被截断。
- 剩下就是拼接成为PIVOT TABLE的query了。用下面的query拼接起来，依旧要避免被截断。
注：
- 根据 http://support.microsoft.com/kb/310378，SELECT ID FROM A FOR XML PATH 返回的结果会被截断到2033字符。
- 在EXEC(@sql)之后，里面创建的临时表会自动被drop掉，这也就意味着你没法继续在后续query中引用这个临时表。一个解决方法是创建全局临时表，即##TABLE。
- 在用户这个问题里面，显示[38252013]是没有什么意义的。所以还需要把这些column给重命名。这里用了一个游标，在用tempdb..sp_rename去重命名。具体如下。
Post a Comment