当前位置：Gxlcms > 数据库问题 > sqlserver 行转列

sqlserver 行转列

时间：2021-07-01 10:21:17 帮助过：15人阅读

1 首先我们先创建一个测试表，往里面插入测试数据，返回表记录如图2所示： --创建测试表 CREATE TABLE [dbo].[TestRows2Columns]( [Id] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) NULL, [Subject] [nvarchar](50) NULL, [Source] [numeric](18,0) NULL ) GO --插入测试数据 INSERT INTO [TestRows2Columns] ([UserName],[Subject],[Source]) SELECT N‘张三‘,N‘语文‘,60 UNION ALL SELECT N‘李四‘,N‘数学‘,70 UNION ALL SELECT N‘王五‘,N‘英语‘,80 UNION ALL SELECT N‘王五‘,N‘数学‘,75 UNION ALL SELECT N‘王五‘,N‘语文‘,57 UNION ALL SELECT N‘李四‘,N‘语文‘,80 UNION ALL SELECT N‘张三‘,N‘英语‘,100 GO SELECT * FROM [TestRows2Columns] 技术分享

 

  1.  
  2. <span style="color: #008080">--</span><span style="color: #008080">2  先以静态的方式实现行转列，效果如图3所示：</span><span style="color: #008080">
  3. --</span><span style="color: #008080">静态sql行转列</span>
  4. <span style="color: #0000ff">SELECT</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">UserName</span><span style="color: #ff0000">]</span><span style="color: #000000">,
  5. </span><span style="color: #ff00ff">SUM</span>(<span style="color: #ff00ff">CASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">WHEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">数学</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Source</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ELSE</span> <span style="color: #800000; font-weight: bold">0</span> <span style="color: #0000ff">END</span>) <span style="color: #0000ff">AS</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">[数学]</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  6. </span><span style="color: #ff00ff">SUM</span>(<span style="color: #ff00ff">CASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">WHEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">英语</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Source</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ELSE</span> <span style="color: #800000; font-weight: bold">0</span> <span style="color: #0000ff">END</span>) <span style="color: #0000ff">AS</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">[英语]</span><span style="color: #ff0000">‘</span><span style="color: #000000">,
  7. </span><span style="color: #ff00ff">SUM</span>(<span style="color: #ff00ff">CASE</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">WHEN</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">语文</span><span style="color: #ff0000">‘</span> <span style="color: #0000ff">THEN</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Source</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">ELSE</span> <span style="color: #800000; font-weight: bold">0</span> <span style="color: #0000ff">END</span>) <span style="color: #0000ff">AS</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">[语文]</span><span style="color: #ff0000">‘</span>     
  8. <span style="color: #0000ff">FROM</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">TestRows2Columns</span><span style="color: #ff0000">]</span>
  9. <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">UserName</span><span style="color: #ff0000">]</span>
  10. <span style="color: #0000ff">GO</span>
  11.
技术分享

 

  1. <span style="color: #008080">--</span><span style="color: #008080">3 接着以动态的方式实现行转列，这是使用拼接SQL的方式实现的，所以它适用于SQL Server 2000以上的数据库版本，执行脚本返回的结果如图2所示；</span><span style="color: #008080">
  2. --</span><span style="color: #008080">动态拼接行转列</span>
  3. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">8000</span><span style="color: #000000">)
  4. </span><span style="color: #0000ff">SET</span> <span style="color: #008000">@sql</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">SELECT [UserName],</span><span style="color: #ff0000">‘</span>   
  5. <span style="color: #0000ff">SELECT</span> <span style="color: #008000">@sql</span> <span style="color: #808080">=</span> <span style="color: #008000">@sql</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">SUM(CASE [Subject] WHEN </span><span style="color: #ff0000">‘‘‘</span><span style="color: #808080">+</span><span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span><span style="color: #808080">+</span><span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000"> THEN [Source] ELSE 0 END) AS </span><span style="color: #ff0000">‘‘‘</span><span style="color: #808080">+</span><span style="color: #ff00ff">QUOTENAME</span>(<span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span>)<span style="color: #808080">+</span><span style="color: #ff0000">‘‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span>   
  6. <span style="color: #0000ff">FROM</span> (<span style="color: #0000ff">SELECT</span> <span style="color: #0000ff">DISTINCT</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span> <span style="color: #0000ff">FROM</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">TestRows2Columns</span><span style="color: #ff0000">]</span>) <span style="color: #0000ff">AS</span><span style="color: #000000"> a     
  7. </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@sql</span> <span style="color: #808080">=</span> <span style="color: #808080">LEFT</span>(<span style="color: #008000">@sql</span>,<span style="color: #ff00ff">LEN</span>(<span style="color: #008000">@sql</span>)<span style="color: #808080">-</span><span style="color: #800000; font-weight: bold">1</span>) <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000"> FROM [TestRows2Columns] GROUP BY [UserName]</span><span style="color: #ff0000">‘</span>   
  8. <span style="color: #0000ff">PRINT</span>(<span style="color: #008000">@sql</span><span style="color: #000000">)
  9. </span><span style="color: #0000ff">EXEC</span>(<span style="color: #008000">@sql</span><span style="color: #000000">)
  10.  
  11.  <img alt="技术分享" src="https://img.gxlcms.com//Uploads-s/new/2020-10-13-qlqqti/20180110222922176285.png"></span>

 

  1. <span style="color: #008080">--</span><span style="color: #008080">4 在SQL Server 2005之后有了一个专门的PIVOT 和 UNPIVOT 关系运算符做行列之间的转换，下面是静态的方式实现的，实现效果如图4所示：</span>
  2.  
  3. <span style="color: #008080">--</span><span style="color: #008080">静态PIVOT行转列</span>
  4. <span style="color: #0000ff">SELECT</span> <span style="color: #808080">*</span>
  5. <span style="color: #0000ff">FROM</span><span style="color: #000000"> (
  6.     </span><span style="color: #0000ff">SELECT</span> 
  7.         <span style="color: #ff0000">[</span><span style="color: #ff0000">UserName</span><span style="color: #ff0000">]</span><span style="color: #000000">,
  8.         </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span><span style="color: #000000">,
  9.         </span><span style="color: #ff0000">[</span><span style="color: #ff0000">Source</span><span style="color: #ff0000">]</span>
  10.     <span style="color: #0000ff">FROM</span><span style="color: #000000"> TestRows2Columns
  11.       ) p 
  12. PIVOT (</span><span style="color: #ff00ff">SUM</span>(<span style="color: #ff0000">[</span><span style="color: #ff0000">Source</span><span style="color: #ff0000">]</span>) <span style="color: #0000ff">FOR</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span> <span style="color: #808080">IN</span> (<span style="color: #ff0000">[</span><span style="color: #ff0000">数学</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">英语</span><span style="color: #ff0000">]</span>,<span style="color: #ff0000">[</span><span style="color: #ff0000">语文</span><span style="color: #ff0000">]</span>)) <span style="color: #0000ff">AS</span><span style="color: #000000"> pvt
  13. </span><span style="color: #0000ff">ORDER</span> <span style="color: #0000ff">BY</span> pvt.<span style="color: #ff0000">[</span><span style="color: #ff0000">UserName</span><span style="color: #ff0000">]</span><span style="color: #000000">;
  14. </span><span style="color: #0000ff">GO</span>
  15.
技术分享

 

  1. <span style="color: #008080">--</span><span style="color: #008080">5 把上面静态的SQL基础上进行修改，这样就不用理会记录里面存储了什么，需要转成什么列名的问题了，脚本如下，效果如图4所示：</span><span style="color: #008080">
  2. --</span><span style="color: #008080">动态PIVOT行转列</span>
  3. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql_str</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">8000</span><span style="color: #000000">)
  4. </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql_col</span> <span style="color: #0000ff">VARCHAR</span>(<span style="color: #800000; font-weight: bold">8000</span><span style="color: #000000">)
  5. </span><span style="color: #0000ff">SELECT</span> <span style="color: #008000">@sql_col</span> <span style="color: #808080">=</span> <span style="color: #ff00ff">ISNULL</span>(<span style="color: #008000">@sql_col</span> <span style="color: #808080">+</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘</span>,<span style="color: #ff0000">‘‘</span>) <span style="color: #808080">+</span> <span style="color: #ff00ff">QUOTENAME</span>(<span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span>) <span style="color: #0000ff">FROM</span> TestRows2Columns <span style="color: #0000ff">GROUP</span> <span style="color: #0000ff">BY</span> <span style="color: #ff0000">[</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">]</span>
  6. <span style="color: #0000ff">SET</span> <span style="color: #008000">@sql_str</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">
  7. SELECT * FROM (
  8.     SELECT [UserName],[Subject],[Source] FROM [TestRows2Columns]) p PIVOT 
  9.     (SUM([Source]) FOR [Subject] IN ( </span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #008000">@sql_col</span> <span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">) ) AS pvt 
  10. ORDER BY pvt.[UserName]</span><span style="color: #ff0000">‘</span>
  11. <span style="color: #0000ff">PRINT</span> (<span style="color: #008000">@sql_str</span><span style="color: #000000">)
  12. </span><span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql_str</span><span style="color: #000000">)
  13.  
  14.  <img alt="技术分享" src="https://img.gxlcms.com//Uploads-s/new/2020-10-13-qlqqti/20180110222922204606.png"></span>

 

  1. <span style="color: #008080">--</span><span style="color: #008080">6 需要不断的修改成他自己环境中表名、分组列、行转列字段、字段值这几个参数，逻辑如图5所示，所以，我继续对上面的脚本进行修改，你只要设置自己的参数就可以实现行转列了，效果如图4所示：</span><span style="color: #008080">
  2. --</span><span style="color: #008080">带条件查询的参数化动态PIVOT行转列</span><span style="color: #008080">
  3. --</span><span style="color: #008080"> =============================================</span>
  4. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql_str</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  5. </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql_col</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  6. </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@sql_where</span> <span style="color: #0000ff">NVARCHAR</span>(<span style="color: #ff00ff">MAX</span><span style="color: #000000">)
  7. </span><span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@tableName</span> SYSNAME <span style="color: #008080">--</span><span style="color: #008080">行转列表</span>
  8. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@groupColumn</span> SYSNAME <span style="color: #008080">--</span><span style="color: #008080">分组字段</span>
  9. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@row2column</span> SYSNAME <span style="color: #008080">--</span><span style="color: #008080">行变列的字段</span>
  10. <span style="color: #0000ff">DECLARE</span> <span style="color: #008000">@row2columnValue</span> SYSNAME <span style="color: #008080">--</span><span style="color: #008080">行变列值的字段</span>
  11. <span style="color: #0000ff">SET</span> <span style="color: #008000">@tableName</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">TestRows2Columns</span><span style="color: #ff0000">‘</span>
  12. <span style="color: #0000ff">SET</span> <span style="color: #008000">@groupColumn</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">UserName</span><span style="color: #ff0000">‘</span>
  13. <span style="color: #0000ff">SET</span> <span style="color: #008000">@row2column</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Subject</span><span style="color: #ff0000">‘</span>
  14. <span style="color: #0000ff">SET</span> <span style="color: #008000">@row2columnValue</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">Source</span><span style="color: #ff0000">‘</span>
  15. <span style="color: #0000ff">SET</span> <span style="color: #008000">@sql_where</span> <span style="color: #808080">=</span> <span style="color: #ff0000">‘</span><span style="color: #ff0000">WHERE UserName = </span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">王五</span><span style="color: #ff0000">‘‘‘</span>
  16. <span style="color: #008080">--</span><span style="color: #008080">从行数据中获取可能存在的列</span>
  17. <span style="color: #0000ff">SET</span> <span style="color: #008000">@sql_str</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">
  18. SELECT @sql_col_out = ISNULL(@sql_col_out + </span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘‘</span><span style="color: #ff0000">,</span><span style="color: #ff0000">‘‘‘‘</span><span style="color: #ff0000">) + QUOTENAME([</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@row2column</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">]) 
  19.     FROM [</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@tableName</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">] </span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@sql_where</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000"> GROUP BY [</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@row2column</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span>
  20. <span style="color: #008080">--</span><span style="color: #008080">PRINT @sql_str</span>
  21. <span style="color: #0000ff">EXEC</span> sp_executesql <span style="color: #008000">@sql_str</span>,N<span style="color: #ff0000">‘</span><span style="color: #ff0000">@sql_col_out NVARCHAR(MAX) OUTPUT</span><span style="color: #ff0000">‘</span>,<span style="color: #008000">@sql_col_out</span><span style="color: #808080">=</span><span style="color: #008000">@sql_col</span><span style="color: #000000"> OUTPUT
  22. </span><span style="color: #008080">--</span><span style="color: #008080">PRINT @sql_col</span>
  23. <span style="color: #0000ff">SET</span> <span style="color: #008000">@sql_str</span> <span style="color: #808080">=</span> N<span style="color: #ff0000">‘</span><span style="color: #ff0000">
  24. SELECT * FROM (
  25.     SELECT [</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@groupColumn</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">],[</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@row2column</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">],[</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@row2columnValue</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">] FROM [</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@tableName</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@sql_where</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">) p PIVOT 
  26.     (SUM([</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@row2columnValue</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">]) FOR [</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@row2column</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">] IN ( </span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span> <span style="color: #008000">@sql_col</span> <span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">) ) AS pvt 
  27. ORDER BY pvt.[</span><span style="color: #ff0000">‘</span><span style="color: #808080">+</span><span style="color: #008000">@groupColumn</span><span style="color: #808080">+</span><span style="color: #ff0000">‘</span><span style="color: #ff0000">]</span><span style="color: #ff0000">‘</span>
  28. <span style="color: #008080">--</span><span style="color: #008080">PRINT (@sql_str)</span>
  29. <span style="color: #0000ff">EXEC</span> (<span style="color: #008000">@sql_str</span><span style="color: #000000">)
  30. <img alt="技术分享" src="https://img.gxlcms.com//Uploads-s/new/2020-10-13-qlqqti/20180110222922229998.png"></span>

 

 

sqlserver 行转列

标签：测试表   arch   print   exe   测试   img   分组   class   设置   

人气教程排行

本站所有资源全部来源于网络，若本站发布的内容侵害到您的隐私或者利益，请联系我们删除！