数据透视表数据

debugcn 发表于 Dev

索拉卜

我有以下数据

  NAME | RIGHTS |
Steven | add |
Steven | update |
Steven | delete |
Mark   | update |
Mark   | delete |
Joseph | don’t have Rights |
Spike  | add |
Spike  | update |
Spike  | delete |

而这些数据，我想作为

  NAMEs | don’t have Rights | add| update | delete |

 Steven | 0 |1|1|1|

 Mark   |0|0|1|1|

 Joseph |1|0|0|0|

 Spike  |0|1|1|1|

请注意，我对权利的类型一无所知，它可以超过100

索拉卜

我得到了答案

DECLARE @cols AS NVARCHAR(MAX),
   @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',[' + Rights +']'
                    from MyTableName 
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT Name,' + @cols + ' from 
             (
                SELECT Name, Rights,1 as xCount FROM MyTableName
            ) x
            pivot 
            (
                count(xCount)
                for Rights in (' + @cols + ')
            ) p '

execute(@query);

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。