如何将PIVOT从SQL更改为MySQL？ [英] How to change PIVOT from SQL to MySQL?

问题描述

 [2:33:27 PM] Harsha Vardhana Reddy：声明  @ Table6   table （PickedUp  varchar （ 50 ），day1  Int ，day2  Int ，day3  Int ，day4  Int ，day5  Int ，day6  Int ，day7  Int ，day8  Int ，day9  Int ，day10  Int ，day11  Int ，day12  Int ，day13  Int ，day14  Int ，day15  Int ，day16  Int ，day17  I nt ，day18  Int ，day19  Int ，day20  Int ，day21  Int ，day22  Int ，day23  Int ，day24  Int ，day25  Int ，day26  Int ，day27  Int ，day28  Int ，day29  Int ，day0  Int ，day31  Int ）
 插入 进入  @ Table6 （PickedUp，day1，day2 ，day3，day4，day5，day6，day7，day8，day9，day10，day11，day12，day13，day14，day15，day16，day17，day18，day19，day20，day21，day22，day23，day24，day25，day26，day27 ，day28，day29，day0，day31）
  SELECT  '  PickedUp'  AS  PickedUp，[ 1 ]，[ 2 ]，[  3 ]，[ 4 ]，[ 5 ]，[ 6 ]，[ 7 ]，[ 8 ]，[ 9 ]，[ 10 ]，[ 11 ]，[ 12 ]，[ 13 ]，[ 14 ]，[ 15 ]，[ 16 ] ，[ 17 ]，[ 18 ]，[ 19 ]，[ 20 ]，[ 21 ]，[ 22 ]，[ 23 ]，[ 24 ]，[ 25 ]，[ 26 ]，[ 27 ]，[ 28 ]，[ 29 ]，[ 30 ]，[ 31 ] 
 从 
（选择天，PickedUp 从  @ Table5 ） As  SourceTable 
 PIVOT 
（
 SUM（PickedUp）对于天在（[ 1 ]，[ 2 ]，[ 3 ]，[ 4 ]，[ 5 ]，[ 6 ]，[ 7 ]，[ 8 ]，[ 9 ]，[ 10 ]，[ 11 ]，[ 12 ]，[ 13 ]，[ 14 ]，[ 15 ]，[ 16 ]，[< span class =code-digit> 17 ]，[ 18 ]，[ 19  ]，[ 20 ]，[ 21 ]，[ 22 ]，[ 23 ]，[ 24 ]，[ 25 ]，[ 26 ]，[ 27 ]，[ 28 ]，[ 29 ]，[ 30 ]， [ 31 ]）
） AS  pivotTable; 
 插入 进入  @ Final  （描述，Day1，Day2，Day3，Day4，Day5，Day6，Day7，Day8，Day9，Day10，Day11，Day12，Day13，Day14，Day15，Day16，Day17，Day18，Day19，Day20，Day21，Day22，Day23，Day24 ，Day25，Day26，Day27，Day28，Day29，Day30，Day31）
 选择 PickedUp，day1，day2，day3，day4，day5，day6，day7， day8，day9，day10，day11，day12，day13，day14，day15，day16，day17，day18，day19，day20，day21，day22，day23，day24，day25，day26，day27，day28，day29，day0，day31 来自  @ Table6  

解决方案

< blockquote>查看我过去的回答：自定义查询结果 [ ^ ]

[2:33:27 PM] Harsha Vardhana Reddy:   Declare @Table6 table(PickedUp varchar(50),day1 Int,day2 Int,day3 Int,day4 Int,day5 Int,day6 Int,day7 Int,day8 Int,day9 Int,day10 Int,day11 Int,day12 Int,day13 Int,day14 Int,day15 Int,day16 Int,day17 Int,day18 Int,day19 Int,day20 Int,day21 Int,day22 Int,day23 Int,day24 Int,day25 Int,day26 Int,day27 Int,day28 Int,day29 Int,day0 Int, day31 Int)
           Insert Into @Table6(PickedUp,day1 ,day2 ,day3 ,day4 ,day5 ,day6 ,day7 ,day8 ,day9 ,day10 ,day11 ,day12 ,day13 ,day14 ,day15 ,day16 ,day17 ,day18 ,day19 ,day20 ,day21 ,day22 ,day23 ,day24 ,day25 ,day26 ,day27 ,day28 ,day29 ,day0 , day31 )
           SELECT 'PickedUp' AS PickedUp,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31]
           From
           (Select days,PickedUp From @Table5) As SourceTable
           PIVOT
           (
             SUM(PickedUp) For Days In([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31] )
           )AS pivotTable;
Insert Into @Final(Description ,Day1 ,Day2 ,Day3 ,Day4 ,Day5 ,Day6 ,Day7 ,Day8 ,Day9 ,Day10 ,Day11 ,Day12 ,Day13 ,Day14 ,Day15 ,Day16 ,Day17 ,Day18 ,Day19 ,Day20,Day21 ,Day22 ,Day23 ,Day24 ,Day25 ,Day26 ,Day27 ,Day28 ,Day29 ,Day30,Day31)
Select PickedUp,day1 ,day2 ,day3 ,day4 ,day5 ,day6 ,day7 ,day8 ,day9 ,day10 ,day11 ,day12 ,day13 ,day14 ,day15 ,day16 ,day17 ,day18 ,day19 ,day20 ,day21 ,day22 ,day23 ,day24 ,day25 ,day26 ,day27 ,day28 ,day29 ,day0 , day31 from @Table6

解决方案

See my past answer: customize query result[^]

这篇关于如何将PIVOT从SQL更改为MySQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！