0
Posted on Thursday, August 14, 2014 by 醉·醉·鱼 and labeled under

问题

有如下一张表,需要创建一个PIVOT表,用来显示每个registration买了多少个product。

解决

  1. 当数据量比较小的时候,可以直接用下面的SQL
  2. 但是当数据量比较多时候,你需要创建动态query去实现。麻烦的地方就是动态生成那一串product_id。
    1. QUOTENAME(product_id) 将product_id用标识符包括起来,即[38252013].
    2. ', ' + QUOTENAME(product_id) 在每个product_id前面加一个逗点,即', [38252013]'。方便后面拼接用。
    3. FOR XML PATH将返回结果拼成一行,即<row>, [38252013]</row><row>, [38252113]</row>...
    4. FOR XML PATH将结果的节点名去掉,即, [38252013], [38252113]...
    5. STUFF((), 1, 2, '')将第一个逗点去掉。
    6. 注意: 参考http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits, 避免动态query被截断。
  3. 剩下就是拼接成为PIVOT TABLE的query了。用下面的query拼接起来,依旧要避免被截断。
注:

  1. 根据 http://support.microsoft.com/kb/310378,SELECT ID FROM A FOR XML PATH 返回的结果会被截断到2033字符。
  2. 在EXEC(@sql)之后,里面创建的临时表会自动被drop掉,这也就意味着你没法继续在后续query中引用这个临时表。一个解决方法是创建全局临时表,即##TABLE。
  3. 在用户这个问题里面,显示[38252013]是没有什么意义的。所以还需要把这些column给重命名。这里用了一个游标,在用tempdb..sp_rename去重命名。具体如下。

0
Responses to ... 创建动态PIVOT TABLE

Post a Comment