T-SQL 经典行专列、列转行,分页及存储过程

Sql的⾏列（纵横表）转换创建表scores⼀、传统的⾏列转换纵表转横表我们要转成的横表是这样⼦的：既然这个表只有两列，那么可以根据姓名进⾏分组。

先把姓名拼凑出来，后⾯的分数我们再想办法。

select姓名from scores group by 姓名结果：分析：1. 我们先拿到语⽂这个科⽬的分数。

既然我们⽤到了group by 语句，这⾥肯定要⽤聚合函数来求分数。

2. ⽽且我们只需要语⽂这⼀科的成绩，分组出来的⼀共有 3列，分别是语⽂、数学、物理。

那么就需要判断科⽬来取分数。

这⾥符合我们需求的 case 语句就登场了。

它和c#中switch-case 作⽤⼀样。

sql case 语句语法：case字段when 值1 then 结果when 值2 then 结果2...else默认结果endselect姓名,SUM(case课程 when '语⽂' then 分数else0 end) as语⽂from scores group by 姓名结果:既然语⽂的分数取到了，其他科⽬改变下条件就可以了。

完整的sql:select姓名,SUM(case课程 when '语⽂' then 分数else0 end) as语⽂,SUM(case课程 when '数学' then 分数else0 end) as数学,SUM(case课程 when '物理' then 分数else0 end) as物理from scores group by 姓名横表转纵表我们先把刚刚转好的表，插⼊⼀个新表Scores2中。

select姓名,SUM(case课程 when '语⽂' then 分数else0 end) as语⽂,SUM(case课程 when '数学' then 分数else0 end) as数学,SUM(case课程 when '物理' then 分数else0 end) as物理into scores2from scores group by 姓名我们也先把张三和李四的语⽂成绩查出来。

sqlserver⾏转列及列转⾏的使⽤在我们使⽤的数据库表中经常需要⽤到⾏列互相转换的情况，使⽤sql 的关键词 UNPIVOT（列转⾏）和PIVOT（⾏转列）可轻松实现⾏列转换。

⼀、列转⾏：员⼯⽉份排班表存储是采⽤1号~31号作为列的⽅式进⾏存储的现通过 UNPIVOT 将每天的班次⽤⾏进⾏展⽰，sql 如下：SELECT distinct t.Pb_Job_No,t.Year_Month ,convert(int, REPLACE( t.day,'day','')) as day,classno FROM Scheduling_InfoUNPIVOT(classno FOR day IN(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)) Twhere Year_Month='2020-05'and Pb_Job_No='0997'order by Pb_Job_No,day这⾥的关键词是UNPIVOT(classno FOR day IN('⽇期列名') ,其中 ‘day’是存储⽇期的列，classno 是存储原有班次的列查询结果如下：⼆、⾏转列：如果将上述列转⾏查询的结果表定义为 Scheduling_DayInfo，进⾏逆转为原始表，那么sql 语句为：SELECT Pb_Job_No,[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]fromScheduling_DayInfo　PIVOT ( max(classno)　FOR[day]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]) )t结果为：此处的 in 必须是列 day 中的值，使⽤pivot 需要⽤到聚合函数（sum，count，avg，max，min 等），使⽤的场景如考试成绩 sum(score) ,年度销售业绩等，由于此处不需要统计但是⼜必须⽤聚合函数，所以使⽤max 凑合，因为这些函数可以接受字符类型的参数。

sql语句中⾏转列，以及列转⾏⾏转列：图1： --------------------------------------------》》》》 图2：sql执⾏原理：根据id分组，然后select后⾯创建多次查询，⽣成列信息（利⽤case语句给分group by后的语句分类）-- ⾏转列select t.id,sum(case name when'仓库1'then t.num else NULL end) 仓库1,sum(case name when'仓库2'then t.num else NULL end) 仓库2,sum(case name when'仓库3'then t.num else NULL end) 仓库3from tGROUP BY t.id;列转⾏：图1： --------------------------------------------》》》》 图2：第⼀步：sql执⾏原理：每个select语句只查某⼀个字段的值，创建多个查询，然后将多个select语句通过union链接，实现⼀条多列数据变成多⾏⼀列；⽐如⼀⾏仓库1，仓库2，仓库3 最后变成多⾏select p.id, '仓库1' name, p.`仓库1` numfrom pr punionselect p.id, '仓库2' name, p.`仓库2` numfrom pr punionselect p.id, '仓库3' name, p.`仓库3` numfrom pr p执⾏结果：第⼆步：去掉为null的，即不存在⾏select*from (select p.id, '仓库1' name, p.`仓库1` num from pr punionselect p.id, '仓库2' name, p.`仓库2` num from pr punionselect p.id, '仓库3' name, p.`仓库3` num from pr p ) T where T.num is not null order by id, name。

mssql数据库表⾏转列，列转⾏终极⽅案复制代码代码如下:--⾏转列问题--建⽴測試環境Create Table TEST(DATES Varchar(6),EMPNO Varchar(5),STYPE Varchar(1),AMOUNT Int)--插⼊數據Insert TEST Select '200605', '02436', 'A', 5Union All Select '200605', '02436', 'B', 3Union All Select '200605', '02436', 'C', 3Union All Select '200605', '02436', 'D', 2Union All Select '200605', '02436', 'E', 9Union All Select '200605', '02436', 'F', 7Union All Select '200605', '02436', 'G', 6Union All Select '200605', '02438', 'A', 7Union All Select '200605', '02438', 'B', 8Union All Select '200605', '02438', 'C', 0Union All Select '200605', '02438', 'D', 3Union All Select '200605', '02438', 'E', 4Union All Select '200605', '02438', 'F', 5Union All Select '200605', '02438', 'G', 1GO--測試--如果STYPE固定，可以這麼寫SelectDATES,EMPNO,SUM(Case STYPE When 'A' Then AMOUNT Else 0 End) As A,SUM(Case STYPE When 'B' Then AMOUNT Else 0 End) As B,SUM(Case STYPE When 'C' Then AMOUNT Else 0 End) As C,SUM(Case STYPE When 'D' Then AMOUNT Else 0 End) As D,SUM(Case STYPE When 'E' Then AMOUNT Else 0 End) As E,SUM(Case STYPE When 'F' Then AMOUNT Else 0 End) As F,SUM(Case STYPE When 'G' Then AMOUNT Else 0 End) As GFrom TESTGroup By DATES,EMPNOOrder By DATES,EMPNO--如果STYPE不固定，⽤動態語句Declare @S Varchar(1000)Set @S=''Select @S=@S+',SUM(Case STYPE When '''+STYPE+''' Then AMOUNT Else 0 End) As '+STYPE From (Select Distinct STYPE From TEST) A Order By STYPESet @S='Select DATES,EMPNO'+@S+' From TEST Group By DATES,EMPNO Order By DATES,EMPNO'EXEC(@S)GO--如果被转置的是数字类型的话，应⽤下列语句DECLARE @S VARCHAR(1000)SET @S='SELECT DATES,EMPNO 'SELECT @S=@S+',['+STYPE+']=SUM(CASE WHEN STYPE='''+STYPE+''' THEN AMOUNT ELSE 0 END)'FROM (Select Distinct STYPE From TEST) A Order By STYPESET @S=@S+' FROM TEST GROUP BY DATES,EMPNO'EXEC(@S)如果是列转⾏的话直接Union All就可以了例如：city style color 46 48 50 52长沙 S6MF01002 152 1 2 2 1长沙 S6MF01002 201 1 2 2 1上⾯到下⾯的样⼦city style color size qty长沙 S6MF01002 152 46 1长沙 S6MF01002 152 48 2长沙 S6MF01002 152 50 2长沙 S6MF01002 152 52 1长沙 S6MF01002 201 46 1长沙 S6MF01002 201 48 2长沙 S6MF01002 201 50 2长沙 S6MF01002 201 52 1Select City,Style,Color,[46] From Test Union allSelect City,Style,Color,[48] From Test Union allSelect City,Style,Color,[50] From Test Union allSelect City,Style,Color,[52] From Test 就可以了。

1：行转列子查询，获取一定数据集结果SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN (SELECT TOP 10 objid FROM T_MyAttentiontma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action下面用行转列语法获取最终结果select *from(SELECT objid,action,count(1) AS [count] FROM T_MyAttention WHERE objid IN (SELECT TOP 10 objid FROM T_MyAttentiontma GROUP BY objid ORDER BY count(1) DESC)GROUP BY objid,action) tpivot ( sum(count) for t.action in ([1],[2],[3],[4])) as ourpivot微软官方的图：2：列转行怎么把一条记录拆分成几条记录？User No. A B C1 1 21 34 241 2 42 25 16 RESULT:User No. Type Num1 1 A 211 1 B 341 1 C 241 2 A 421 2 B 251 2 C 16declare @t table(usserint ,no int ,a int,bint, c int) insert into @t select 1,1,21,34,24union all select 1,2,42,25,16SELECT usser,no,Type=attribute, Num=valueFROM @tUNPIVOT(value FOR attribute IN([a], [b], [c])) AS UPV列转行备注value FOR attribute IN([a], [b], [c])这句话中，a,b,c是列的名字，但是列名不能出现在上句的select语句中。

1.--行‎列转换原‎表: ‎姓名‎科目‎成绩‎‎‎张三‎语文‎ 8‎0‎‎张三‎‎数学‎90‎‎‎张三‎物理‎ 85‎‎‎李四‎语‎文‎85‎‎李‎四‎物理‎ 82‎‎‎李四‎英语‎ 9‎0‎‎李四‎‎政治‎70‎‎‎王五‎英语‎ 90‎转换后‎的表：‎姓名‎数‎学‎物理‎英语‎语文‎政‎治‎‎‎‎‎李四‎‎ 0 ‎‎ 82 ‎‎ 90 ‎‎85 ‎ 7‎0‎‎‎‎‎王五‎‎0 ‎‎ 0 ‎‎ 90 ‎‎0 ‎‎0‎‎‎‎‎张三‎‎90 ‎‎85 ‎‎ 0 ‎‎80 ‎‎0实例‎:cre‎a te t‎a ble ‎c j -‎-创建表c‎j(‎ ID‎‎ Int‎IDEN‎T ITY ‎(1,1)‎‎n ot n‎u ll, ‎--创建列‎I D,并且‎每次新增一‎条记录就会‎加1 ‎ Nam‎e‎Varc‎h ar(5‎0), ‎‎Subj‎e ct ‎V arch‎a r(50‎),‎ Res‎u lt ‎Int,‎‎ pri‎m ary ‎k ey (‎I D) ‎ -‎-定义ID‎为表cj的‎主键‎)‎;--T‎r unca‎t e ta‎b le c‎j--S‎e lect‎* fr‎o m cj‎Inse‎r t in‎t o cj‎Sele‎c t '张‎三','语‎文',80‎unio‎n all‎Sel‎e ct '‎张三','‎数学',9‎0 uni‎o n al‎lSel‎e ct '‎张三','‎物理',8‎5 uni‎o n al‎lSel‎e ct '‎李四','‎语文',8‎5 uni‎o n al‎lSel‎e ct '‎李四','‎物理',8‎2 uni‎o n al‎lSel‎e ct '‎李四','‎英语',9‎0 uni‎o n al‎lSel‎e ct '‎李四','‎政治',7‎0 uni‎o n al‎lSel‎e ct '‎王五','‎英语',9‎0--行‎列转换D‎e clar‎e @sq‎l var‎c har(‎8000)‎Set ‎@sql ‎= 'Se‎l ect ‎N ame ‎a s 姓名‎'Sel‎e ct @‎s ql =‎@sql‎+ ',‎s um(c‎a se S‎u bjec‎t whe‎n '''‎+Subj‎e ct+'‎'' th‎e n Re‎s ult ‎e lse ‎0 end‎) ['+‎S ubje‎c t+']‎'fro‎m (se‎l ect ‎d isti‎n ct S‎u bjec‎t fro‎m cj)‎as c‎j --‎把所有唯一‎的科目的名‎称都列举出‎来Sel‎e ct @‎s ql =‎@sql‎+' fr‎o m cj‎grou‎p by ‎n ame'‎‎2. 行列‎转换--合‎并原表:‎班级‎学‎号‎‎‎ 1‎‎‎1‎‎‎1 ‎‎ 2‎‎‎1 ‎‎ 3‎‎‎2 ‎‎ 1‎‎‎2 ‎‎ 2‎‎‎3 ‎‎ 1转‎换后的表:‎班级‎学号‎‎‎‎‎‎‎ 1‎ 1,‎2,3‎‎‎‎‎ 2 ‎1,2‎‎‎‎‎ 3 ‎ 1 ‎实例‎:Cre‎a te t‎a ble ‎C lass‎N o -‎-创建表C‎l assN‎o(‎ ID‎Int ‎I DENT‎I TY(1‎,1) ‎n ot n‎u ll, ‎--创建‎列ID,并‎且每次新增‎一条记录就‎会加1 ‎ Cl‎a ss ‎V arch‎a r(50‎), ‎--班级‎列‎Numb‎e r Va‎r char‎(50),‎ -‎-学号列‎ P‎r imar‎y Key‎(ID) ‎‎ --定‎义ID为表‎C lass‎N o的主键‎);-‎-Trun‎c ate ‎T able‎Clas‎s No-‎-Sele‎c t * ‎f rom ‎C lass‎N oIn‎s ert ‎I nto ‎C lass‎N oSe‎l ect ‎1,1 U‎n ion ‎a llS‎e lect‎1,2 ‎U nion‎all‎S elec‎t 1,3‎Unio‎n all‎Sele‎c t 2,‎1 Uni‎o n al‎lSel‎e ct 2‎,2 Un‎i on a‎l lSe‎l ect ‎3,1‎创建一个合‎并的函数‎--Dro‎p Fun‎c tion‎KFRe‎t urn‎C reat‎e Fun‎c tion‎KFRe‎t urn(‎@Clas‎s Var‎c har(‎50))‎R etur‎n s Va‎r char‎(8000‎)as ‎Begi‎nDec‎l are ‎@str ‎V arch‎a r(80‎00)S‎e t @s‎t r = ‎''Se‎l ect ‎@str ‎= @st‎r + c‎a st(N‎u mber‎as V‎a rcha‎r(50)‎) + ‎',' f‎r om C‎l assN‎o Whe‎r e Cl‎a ss =‎@Cla‎s sS‎e t @s‎t r = ‎S ubSt‎r ing(‎@str,‎1,len‎(@str‎)-1)En‎d--‎调用自定义‎函数得到结‎果Sel‎e ct D‎i stin‎c t Cl‎a ss,d‎b o.KF‎R etur‎n(Cla‎s s) F‎r om C‎l assN‎o3‎:列转行‎--Dro‎p Tab‎l e Co‎l umnT‎o Row‎C reat‎e tab‎l e Co‎l umnT‎o Row‎(‎I D In‎t IDE‎N TITY‎(1,1)‎ not‎null‎, --‎创建列ID‎,并且每次‎新增一条记‎录就会加1‎a‎ int‎,‎b in‎t,‎c i‎n t,‎ d ‎i nt,‎ e ‎int,‎f‎ int‎,‎g in‎t,‎h i‎n t,‎ Pri‎m ary ‎K ey(I‎D) ‎‎--定义I‎D为表Co‎l umnT‎o Row的‎主键‎)‎;--T‎r unca‎t e Ta‎b le C‎o lumn‎T oRow‎--S‎e lect‎* fr‎o m Co‎l umnT‎o Row‎I nser‎t Int‎o Col‎u mnTo‎R ow‎S elec‎t 15,‎9,1,0‎,1,2,‎4,2 U‎n ion ‎a llS‎e lect‎22,3‎4,44,‎5,6,7‎,8,7 ‎U nion‎all‎S elec‎t 33,‎44,55‎,66,7‎7,88,‎99,12‎Dec‎l are ‎@sql ‎V arch‎a r(80‎00)S‎e t @s‎q l = ‎''Se‎l ect ‎@sql ‎= @sq‎l + r‎t rim(‎n ame)‎+ ' ‎f rom ‎C olum‎n ToRo‎w uni‎o n al‎l Sel‎e ct '‎from‎SysC‎o lumn‎s Whe‎r e id‎= ob‎j ect_‎i d('C‎o lumn‎T oRow‎')Se‎t @sq‎l = S‎u bStr‎i ng(@‎s ql,1‎,len(‎@sql)‎-70)‎--70的‎长度就是这‎个字符串'‎f rom ‎C olum‎n ToRo‎w uni‎o n al‎l Sel‎e ct I‎D fro‎mCol‎u mnTo‎R ow u‎n ion ‎a ll S‎e lect‎',因为‎它会把ID‎这一列的值‎也算进去，‎所以要把它‎截掉Ex‎e c ('‎S elec‎t ' +‎@sql‎+ ' ‎f rom ‎C olum‎n ToRo‎w')‎4. 如‎何取得一个‎数据表的所‎有列名方‎法如下：先‎从syso‎b ject‎s系统表中‎取得数据表‎的syst‎e mid,‎然后再sy‎s colu‎m ns表中‎取得该数据‎表的所有列‎名。

Sql2008的⾏列转换之⾏转列今天在⼯作的时候遇到了⾏列转换的问题，记得去年有⼀段时间经常写，但是许久不⽤已经记不太得了。

好记性不如烂笔头，忙完之后赶紧记录⼀下。

关键字：PIVOT（⾏转列），UNPIVOT（列转⾏）先说说 PIVOT（⾏转列）这是我今天遇到的问题PIVOT（⾏专列）主要语法：PIVOT(聚合函数(列) FOR 列 in (…) )AS P完整语法：table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN(<column_list>))举例（这个最重要，没有例⼦只有概念的⽂章，不是好⽂章）我这⾥有⼀张表其中OperationDate这⾥⼀列是⽇期，要求是：对⽇期进⾏分组，统计Testuser每天的操作。

最重要的是⽇期要做为列名显⽰，如下图直接贴sql语句DECLARE @columnNme NVARCHAR(4000)SELECT @columnNme = ISNULL(@columnNme + ',', '') + QUOTENAME(CONVERT(varchar(100), [OperationDate], 23))FROM [Test].[dbo].[OperationData]group by CONVERT(varchar(100), [OperationDate], 23)order by CONVERT(varchar(100), [OperationDate], 23)--select(@columnNme )---上⾯⼀部分，是取出不重复的⽇期，⼀会⼉要做为列名， QUOTENAME是在“xxxx-xx-xx”这种不规则的列名合法化，它会变成[xxxx-xx-xx] , 加了两个[ ]Declare @sql NVARCHAR(4000)set @sql = 'select * from(SELECT erName,CONVERT(varchar(100), od.[OperationDate], 23) as ⽇期,COUNT(1) as 浏览数FROM [Test].[dbo].[OperationData] as odgroup by CONVERT(varchar(100), od.[OperationDate], 23),erName) as tpivot (max(浏览数) for ⽇期in ('+@columnNme +')) as result'--select(@sql)EXEC( @sql)这种写法是列名数量不固定的时候，需要动态⽣成。

Sqlserver中将数据⾏转列列转⾏（⼀）在做⼀些数据分析与数据展⽰时，经常会遇到⾏转列，列转⾏的需求，今天就来总结下：在开始之前，先来创建⼀个临时表，并且写⼊⼀些测试数据：/*第⼀步：创建临时表结构*/CREATE TABLE #Student --创建临时表(StuName nvarchar(20), --学⽣名称StuSubject nvarchar(20),--考试科⽬StuScore int--考试成绩)DROP TABLE #Student --删除临时表SELECT*FROM #Student --查询所有数据/*第⼆步：写⼊测试数据*/--张三INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','语⽂',80);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','数学',75);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('张三','英语',65);--李四INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','语⽂',36);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','数学',56);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('李四','英语',38);--王五INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','语⽂',69);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','数学',80);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('王五','英语',78);--赵六INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','语⽂',80);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','数学',80);INSERT INTO #Student(StuName,StuSubject,StuScore) values ('赵六','英语',95);数据准备好了之后，开始今天的正题：⼀：⾏转列，下⾯是转换之前与之后的截图对⽐⽅法1：使⽤Case when ⽅式SELECT StuSubject,SUM(CASE WHEN StuName='张三' THEN StuScore END) as '张三',SUM(CASE WHEN StuName='王五' THEN StuScore END) as '王五',SUM(CASE WHEN StuName='赵六' THEN StuScore END) as '赵六'FROM #StudentGROUP BY StuSubject适⽤场景：要转换成多少列确定，⽐如上⾯，已经确切知道只有张三、李四、王五、赵六四个⼈；缺点：1.如果有20个⼈，要写20个CASE 判断，写起来恶⼼，代码不优雅；2.⽆法解决列是动态产⽣的问题，⽐如按⽉份⽇期转换2⽉有可能28天，其它⽉份30天；⽅法2：使⽤PIVOT 关键字SELECT *FROM #StudentPIVOT(SUM(StuScore) FOR [StuName] IN("李四","王五","张三","赵六")) AS T适⽤场景：要转换成多少列确定，⽐如上⾯，已经确切知道只有张三、李四、王五、赵六四个⼈；缺点：1.⽆法解决列是动态产⽣的问题，⽐如按⽉份⽇期转换2⽉有可能28天，其它⽉份30天；⽅法3：使⽤PIVOT、EXEC关键Declare@StuName varchar(100);Declare@sql nvarchar(4000)--步骤1.假设列不固定，是动态产⽣的，需要先将所有列组合成⼀个长字符串，⽐如A，B，C ，SELECT@StuName=STUFF((SELECT','+ DS_descriptionFROM Base_SalaColumnWHERE DS_type='3'AND DS_means!='不参与'FOR xml path('')),1,1,'')Print@StuName--步骤2.由于动态产⽣的列，脚本不能执⾏，所以⽤Exec来执⾏，把脚本写成⼀个字符串。

1、列转行CREA TE TABLE t_col_row(ID INT,c1 V ARCHAR2(10),c2 V ARCHAR2(10),c3 V ARCHAR2(10));INSERT INTO t_col_row V ALUES (1, 'v11', 'v21', 'v31'); INSERT INTO t_col_row V ALUES (2, 'v12', 'v22', NULL); INSERT INTO t_col_row V ALUES (3, 'v13', NULL, 'v33'); INSERT INTO t_col_row V ALUES (4, NULL, 'v24', 'v34'); INSERT INTO t_col_row V ALUES (5, 'v15', NULL, NULL); INSERT INTO t_col_row V ALUES (6, NULL, NULL, 'v35'); INSERT INTO t_col_row V ALUES (7, NULL, NULL, NULL); COMMIT;SELECT * FROM t_col_row;1)UNION ALL适用范围：8i,9i,10g及以后版本SELECT id, 'c1' cn, c1 cvFROM t_col_rowUNION ALLSELECT id, 'c2' cn, c2 cvFROM t_col_rowUNION ALLSELECT id, 'c3' cn, c3 cv FROM t_col_row;若空行不需要转换，只需加一个where条件，WHERE COLUMN IS NOT NULL 即可。

sql 的行列转换在 SQL 中进行行列转换是一种常见的数据操作，它可以将数据从行的形式转换为列的形式，或者从列的形式转换为行的形式。

下面是两种常见的行列转换方法：1. 使用 `PIVOT` 语句进行行列转换：`PIVOT` 是 SQL 中专门用于进行行列转换的语句。

它允许你将一个表中的行数据按照特定的列值进行分组，并将其他列的值转换为新的列。

下面是一个简单的示例，假设有一个名为 `sales` 的表，包含以下列：`product_id`、`category` 和 `quantity`。

```sqlSELECT category, SUM(quantity) AS total_quantityFROM salesGROUP BY category;```上述示例使用 `GROUP BY` 子句按照 `category` 列进行分组，并使用 `SUM` 函数计算每个分组的 `quantity` 列的总和。

2. 使用 `UNION ALL` 和子查询进行行列转换：有时候，你可能无法直接使用 `PIVOT` 语句进行行列转换，或者你需要更复杂的转换逻辑。

在这种情况下，可以使用 `UNION ALL` 和子查询来实现。

下面是一个示例，将一个包含员工信息的表转换为按部门和职位分类的交叉表。

```sqlSELECT department, job_title, COUNT(*) AS countFROM employeesGROUP BY department, job_title;SELECT department, 'All Jobs' AS job_title, COUNT(*) AS countFROM employeesGROUP BY department;```上述示例使用了两个子查询，一个按照 `department` 和 `job_title` 进行分组，另一个按照 `department` 进行分组，并将所有职位都归为一个名为 `All Jobs` 的虚拟职位。

Oracle⾏转列、列转⾏的Sql语句总结多⾏转字符串这个⽐较简单，⽤||或concat函数可以实现SQL Code1 2select concat(id,username) str from app_user select id||username str from app_user字符串转多列实际上就是拆分字符串的问题，可以使⽤ substr、instr、regexp_substr函数⽅式字符串转多⾏使⽤union all函数等⽅式wm_concat函数⾸先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显⽰成⼀⾏,接下来上例⼦,看看这个神奇的函数如何应⽤准备测试数据 SQL Code1 2 3 4 5 6create table test(id number,name varchar2(20)); insert into test values(1,'a');insert into test values(1,'b');insert into test values(1,'c');insert into test values(2,'d');insert into test values(2,'e');效果1 : ⾏转列，默认逗号隔开SQL Code1select wm_concat(name) name from test;效果2: 把结果⾥的逗号替换成"|"SQL Code1select replace(wm_concat(name),',','|') from test;效果3: 按ID分组合并nameSQL Code1select id,wm_concat(name) name from test group by id;sql语句等同于下⾯的sql语句:SQL Code1 2 3 4 5 6-------- 适⽤范围：8i,9i,10g及以后版本（ MAX + DECODE ）select id,max(decode(rn, 1, name, null)) ||max(decode(rn, 2, ',' || name, null)) ||max(decode(rn, 3, ',' || name, null)) strfrom (select id,789101112131415161718192021222324252627282930313233343536 name,row_number () over(partition by id order by name) as rnfrom test) tgroup by idorder by 1;-------- 适⽤范围：8i,9i,10g 及以后版本 （ ROW_NUMBER + LEAD ）select id, strfrom (select id,row_number () over(partition by id order by name) as rn,name || lead (',' || name, 1) over(partition by id order by name) ||lead (',' || name, 2) over(partition by id order by name) ||lead (',' || name, 3) over(partition by id order by name) as str from test)where rn = 1order by 1;-------- 适⽤范围：10g 及以后版本 （ MODEL ）select id, substr (str, 2) strfrom test model return updated rows partition by (id) dimension by (row_number ()over(partition by id order by name) as rn) measures(cast (name as varchar2(20)) as str)rules upsert iterate (3) until(presentv(str [ iteration_number + 2 ], 1, 0) = 0)(str [ 0 ] = str [ 0 ] || ',' || str [ iteration_number + 1 ])order by 1;-------- 适⽤范围：8i,9i,10g 及以后版本 （ MAX + DECODE ）select t.id id, max (substr (sys_connect_by_path(, ','), 2)) strfrom (select id, name, row_number () over(partition by id order by name) rnfrom test) tstart with rn = 1connect by rn = prior rn + 1and id = prior idgroup by t.id;懒⼈扩展⽤法:案例: 我要写⼀个视图,类似"create or replace view as select 字段1,...字段50 from tablename" ,基表有50多个字段,要是靠⼿⼯写太⿇烦了,有没有什么简便的⽅法? 当然有了,看我如果应⽤wm_concat 来让这个需求变简单，假设我的APP_USER 表中有（id,username,password,age ）4个字段。

sql查询结果列转行方法要将SQL查询结果的列转为行，我们一般可以使用UNIONALL操作符来实现。

下面是详细步骤：1.首先，我们需要确保查询结果的列类型和取值范围是一致的。

如果有需要，可以使用CAST(或CONVERT(函数进行类型转换。

2.使用UNIONALL操作符将所有列合并成一列。

每个SELECT语句表示一个原始列，其中的列名称可以通过别名进行重命名。

例如：SELECT column1 AS column FROM tableUNIONALLSELECT column2 AS column FROM tableUNIONALLSELECT column3 AS column FROM table这样我们就将table表中的column1、column2和column3列合并成一列，并使用别名column进行表示。

3.如果查询结果中有多个表，我们需要使用JOIN操作符将这些表连接起来，然后再进行列转行的操作。

例如：SELECT column1 AS column FROM table1JOIN table2 ON table1.id = table2.idUNIONALLSELECT column2 AS column FROM table1JOIN table2 ON table1.id = table2.id这里我们将table1和table2两个表连接起来，并将table1中的column1和table2中的column2列合并成一列。

4.如果查询结果中有多个条件或是函数，我们可以使用子查询来实现列转行。

例如：SELECT columnFROMSELECT col1 + col2 AS column FROM tableWHERE conditionUNIONALLSELECT col3 - col4 AS column FROM tableWHERE conditionAS subquer这里我们将table表中col1和col2两列相加，并将结果作为column列，然后与table表中col3和col4两列相减的结果进行合并。

SQL优化之——⾏转列，列转⾏⾏转列，列转⾏是我们在开发过程中经常碰到的问题。

⾏转列⼀般通过CASE WHEN 语句来实现，也可以通过 SQL SERVER 2005 新增的运算符PIVOT来实现。

⽤传统的⽅法，⽐较好理解。

层次清晰，⽽且⽐较习惯。

但是PIVOT 、UNPIVOT提供的语法⽐⼀系列复杂的SELECT...CASE 语句中所指定的语法更简单、更具可读性。

下⾯我们通过⼏个简单的例⼦来介绍⼀下列转⾏、⾏转列问题。

我们⾸先先通过⼀个⽼⽣常谈的例⼦，学⽣成绩表(下⾯简化了些)来形象了解下⾏转列CREATE TABLE [StudentScores]([UserName] NVARCHAR(20), --学⽣姓名[Subject] NVARCHAR(30), --科⽬[Score] FLOAT, --成绩)INSERT INTO [StudentScores] SELECT 'Nick', '语⽂', 80INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70INSERT INTO [StudentScores] SELECT 'Nick', '⽣物', 85INSERT INTO [StudentScores] SELECT 'Kent', '语⽂', 80INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70INSERT INTO [StudentScores] SELECT 'Kent', '⽣物', 85如果我想知道每位学⽣的每科成绩，⽽且每个学⽣的全部成绩排成⼀⾏，这样⽅便我查看、统计，导出数据SELECTUserName,MAX(CASE Subject WHEN '语⽂' THEN Score ELSE 0 END) AS '语⽂',MAX(CASE Subject WHEN '数学' THEN Score ELSE 0 END) AS '数学',MAX(CASE Subject WHEN '英语' THEN Score ELSE 0 END) AS '英语',MAX(CASE Subject WHEN '⽣物' THEN Score ELSE 0 END) AS '⽣物'FROM dbo.[StudentScores]GROUP BY UserName查询结果如图所⽰，这样我们就能很清楚的了解每位学⽣所有的成绩了接下来我们来看看第⼆个⼩列⼦。

SqlServer⾏转列（PIVOT），列转⾏（UNPIVOT）总结PIVOT⽤于将列值旋转为列名（即⾏转列）语法：table_sourcePIVOT(聚合函数（value_column）FOR pivot_columnIN(<column_list>))UNPIVOT⽤于将列明转为列值（即列转⾏）语法：table_sourceUNPIVOT(value_columnFOR pivot_columnIN(<column_list>))注意：PIVOT、UNPIVOT是SQL Server 2005的语法，使⽤需修改数据库兼容级别在数据库属性->选项->兼容级别改为 90⼀、⾏转列1、建⽴表格ifobject_id('tb')isnotnulldroptabletbgoCreate table tb(姓名 varchar(10),课程 varchar(10),分数 int)Insert into tb values('张三','语⽂',74)Insert into tb values('张三','数学',83)Insert into tb values('张三','物理',93)Insert into tb values('李四','语⽂',74)Insert into tb values('李四','数学',84)Insert into tb values('李四','物理',94)goSelect * from tb2、使⽤SQL Server 2000静态SQLselect姓名,Max (case课程when'语⽂'then分数else 0 end)语⽂,Max (case课程when'数学'then分数else 0 end)数学,Max (case课程when'物理'then分数else 0 end)物理From tbGroup by姓名3、使⽤SQL Server 2000动态SQLdeclare@sqlvarchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程fromtb)a--同from tb group by课程，默认按课程名排序set@sql=@sql+' from tb group by姓名'exec(@sql)--使⽤isnull(),变量先确定动态部分declare@sqlvarchar(8000)select@sql=isnull(@sql+',','')+' max(case课程when '''+课程+''' then分数else 0 end) ['+课程+']'from(selectdistinct课程fromtb)asaset@sql='select姓名,'+@sql+' from tb group by姓名'exec(@sql)4、使⽤SQL Server 2005静态SQLselect*fromtb pivot(max(分数)for课程in(语⽂,数学,物理))a5、使⽤SQL Server 2005动态SQL--使⽤stuff()declare@sqlvarchar(8000)set@sql='' --初始化变量@sqlselect@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值set@sql=stuff(@sql,1,1,'')--去掉⾸个','set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'exec(@sql)--或使⽤isnull()declare@sqlvarchar(8000)–-获得课程集合select@sql=isnull(@sql+',','')+课程fromtbgroupby课程set@sql='select * from tb pivot (max(分数) for课程in ('+@sql+'))a'exec(@sql)⼆、⾏转列结果加上总分、平均分1、使⽤SQL Server 2000静态SQLselect姓名,max(case课程when'语⽂'then分数else0end)语⽂,max(case课程when'数学'then分数else0end)数学,max(case课程when'物理'then分数else0end)物理,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分fromtbgroupby姓名2、使⽤SQL Server 2000动态SQL--SQL SERVER 2000动态SQLdeclare@sqlvarchar(500)set@sql='select姓名'select@sql=@sql+',max(case课程when '''+课程+''' then分数else 0 end)['+课程+']'from(selectdistinct课程fromtb)aset@sql=@sql+',sum(分数)总分,cast(avg(分数*1.0) as decimal(18,2)) 平均分from tb group by姓名' exec(@sql)3、使⽤SQL Server 2005静态SQLselectm.*,n.总分,n.平均分from(select*fromtb pivot(max(分数)for课程in(语⽂,数学,物理))a)m,(select姓名,sum(分数)总分,cast(avg(分数*1.0)asdecimal(18,2))平均分fromtbgroupby姓名)nwherem.姓名=n.姓名4、使⽤SQL Server 2005动态SQLdeclare@sqlvarchar(8000)set@sql='' --初始化变量@sqlselect@sql=@sql+','+课程fromtbgroupby课程--变量多值赋值--同select @sql = @sql + ','+课程from (select distinct课程from tb)aset@sql=stuff(@sql,1,1,'')--去掉⾸个','set@sql='select m.* , n.总分,n.平均分from(select * from (select * from tb) a pivot (max(分数) for课程in ('+@sql+')) b) m ,(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group by姓名) n where m.姓名= n.姓名'exec(@sql)--或使⽤isnull()declare@sqlvarchar(8000)select@sql=isnull(@sql+',','')+课程fromtbgroupby课程set@sql='select m.* , n.总分,n.平均分from(select * from (select * from tb) a pivot (max(分数) for课程in ('+@sql+')) b) m ,(select姓名,sum(分数)总分, cast(avg(分数*1.0) as decimal(18,2))平均分from tb group by姓名) n where m.姓名= n.姓名'exec(@sql)⼆、列转⾏1、建⽴表格ifobject_id('tb')isnotnulldroptabletbgocreatetabletb(姓名varchar(10),语⽂int,数学int,物理int)insertintotbvalues('张三',74,83,93)insertintotbvalues('李四',74,84,94)goselect*fromtb2、使⽤SQL Server 2000静态SQL--SQL SERVER 2000静态SQL。

mssql数据库表⾏转列，列转⾏⽐较经典--⾏列互转/****************************************************************************************************************************************************** 以学⽣成绩为例⼦，⽐较形象易懂整理⼈：中国风(Roy)⽇期:2008.06.06******************************************************************************************************************************************************/ --1、⾏互列--> --> (Roy)⽣成測試數據if not object_id('Class') is nulldrop table ClassGoCreate table Class([Student] nvarchar(2),[Course] nvarchar(2),[Score] int)Insert Classselect N'张三',N'语⽂',78 union allselect N'张三',N'数学',87 union allselect N'张三',N'英语',82 union allselect N'张三',N'物理',90 union allselect N'李四',N'语⽂',65 union allselect N'李四',N'数学',77 union allselect N'李四',N'英语',65 union allselect N'李四',N'物理',85Go--2000⽅法:动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)'from Class group by[Course]exec('select [Student]'+@s+' from Class group by [Student]')⽣成静态:select[Student],[数学]=max(case when [Course]='数学' then [Score] else 0 end),[物理]=max(case when [Course]='物理' then [Score] else 0 end),[英语]=max(case when [Course]='英语' then [Score] else 0 end),[语⽂]=max(case when [Course]='语⽂' then [Score] else 0 end)fromClassgroup by [Student]GO动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')⽣成静态:select *fromClasspivot(max([Score]) for [Course] in([数学],[物理],[英语],[语⽂]))b⽣成格式:/*Student 数学物理英语语⽂------- ----------- ----------- ----------- -----------李四 77 85 65 65张三 87 90 82 78(2 ⾏受影响)*/------------------------------------------------------------------------------------------go--加上总成绩(学科平均分)--2000⽅法:动态:declare @s nvarchar(4000)set @s=''Select @s=@s+','+quotename([Course])+'=max(case when [Course]='+quotename([Course],'''')+' then [Score] else 0 end)' from Class group by[Course]exec('select [Student]'+@s+',[总成绩]=sum([Score]) from Class group by [Student]')--加多⼀列(学科平均分⽤avg([Score])) ⽣成动态:select[Student],[数学]=max(case when [Course]='数学' then [Score] else 0 end),[物理]=max(case when [Course]='物理' then [Score] else 0 end),[英语]=max(case when [Course]='英语' then [Score] else 0 end),[语⽂]=max(case when [Course]='语⽂' then [Score] else 0 end),[总成绩]=sum([Score]) --加多⼀列(学科平均分⽤avg([Score]))fromClassgroup by [Student]go--2005⽅法:动态:declare @s nvarchar(4000)Select @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字符串@s中第⼀个逗号exec('select [Student],'+@s+',[总成绩] from (select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) apivot (max([Score]) for [Course] in('+@s+'))b ')⽣成静态:select[Student],[数学],[物理],[英语],[语⽂],[总成绩]from(select *,[总成绩]=sum([Score])over(partition by [Student]) from Class) a --平均分时⽤avg([Score])pivot(max([Score]) for [Course] in([数学],[物理],[英语],[语⽂]))b⽣成格式:/*Student 数学物理英语语⽂总成绩------- ----------- ----------- ----------- ----------- -----------李四 77 85 65 65 292张三 87 90 82 78 337(2 ⾏受影响)*/go--2、列转⾏--> --> (Roy)⽣成測試數據if not object_id('Class') is nulldrop table ClassGoCreate table Class([Student] nvarchar(2),[数学] int,[物理] int,[英语] int,[语⽂] int)Insert Classselect N'李四',77,85,65,65 union allselect N'张三',87,90,82,78Go--2000:动态:declare @s nvarchar(4000)select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字符串@s中第⼀个union all+',[Score]='+quotename(Name)+' from Class'from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不转换的列order by Colidexec('select * from ('+@s+')t order by [Student],[Course]')--增加⼀个排序⽣成静态:select *from (select [Student],[Course]='数学',[Score]=[数学] from Class union allselect [Student],[Course]='物理',[Score]=[物理] from Class union allselect [Student],[Course]='英语',[Score]=[英语] from Class union allselect [Student],[Course]='语⽂',[Score]=[语⽂] from Class)torder by [Student],[Course]go--2005:动态:declare @s nvarchar(4000)select @s=isnull(@s+',','')+quotename(Name)from syscolumns where ID=object_id('Class') and Name not in('Student')order by Colidexec('select Student,[Course],[Score] from Class unpivot ([Score] for [Course] in('+@s+'))b')goselectStudent,[Course],[Score]fromClassunpivot([Score] for [Course] in([数学],[物理],[英语],[语⽂]))b⽣成格式:/*Student Course Score------- ------- -----------李四数学 77李四物理 85李四英语 65李四语⽂ 65张三数学 87张三物理 90张三英语 82张三语⽂ 78(8 ⾏受影响)*/===========================(例⼆)===================================--⾏转列问题--建⽴測試環境Create Table TEST(DATES Varchar(6),EMPNO Varchar(5),STYPE Varchar(1),AMOUNT Int)--插⼊數據Insert TEST Select '200605', '02436', 'A', 5Union All Select '200605', '02436', 'B', 3Union All Select '200605', '02436', 'C', 3Union All Select '200605', '02436', 'D', 2Union All Select '200605', '02436', 'E', 9Union All Select '200605', '02436', 'F', 7Union All Select '200605', '02436', 'G', 6Union All Select '200605', '02438', 'A', 7Union All Select '200605', '02438', 'B', 8Union All Select '200605', '02438', 'C', 0Union All Select '200605', '02438', 'D', 3Union All Select '200605', '02438', 'E', 4Union All Select '200605', '02438', 'F', 5Union All Select '200605', '02438', 'G', 1GO--測試--如果STYPE固定，可以這麼寫SelectDATES,EMPNO,SUM(Case STYPE When 'A' Then AMOUNT Else 0 End) As A,SUM(Case STYPE When 'B' Then AMOUNT Else 0 End) As B,SUM(Case STYPE When 'C' Then AMOUNT Else 0 End) As C,SUM(Case STYPE When 'D' Then AMOUNT Else 0 End) As D,SUM(Case STYPE When 'E' Then AMOUNT Else 0 End) As E,SUM(Case STYPE When 'F' Then AMOUNT Else 0 End) As F,SUM(Case STYPE When 'G' Then AMOUNT Else 0 End) As GFrom TESTGroup By DATES,EMPNOOrder By DATES,EMPNO--如果STYPE不固定，⽤動態語句Declare @S Varchar(1000)Set @S=''Select @S=@S+',SUM(Case STYPE When '''+STYPE+''' Then AMOUNT Else 0 End) As '+STYPE From (Select Distinct STYPE From TEST) A Order By STYPESet @S='Select DATES,EMPNO'+@S+' From TEST Group By DATES,EMPNO Order By DATES,EMPNO'EXEC(@S)GO--如果被转置的是数字类型的话，应⽤下列语句DECLARE @S VARCHAR(1000)SET @S='SELECT DATES,EMPNO 'SELECT @S=@S+',['+STYPE+']=SUM(CASE WHEN STYPE='''+STYPE+''' THEN AMOUNT ELSE 0 END)'FROM (Select Distinct STYPE From TEST) A Order By STYPESET @S=@S+' FROM TEST GROUP BY DATES,EMPNO'EXEC(@S)如果是列转⾏的话直接Union All就可以了例如：city style color 46 48 50 52长沙 S6MF01002 152 1 2 2 1长沙 S6MF01002 201 1 2 2 1上⾯到下⾯的样⼦city style color size qty长沙 S6MF01002 152 46 1长沙 S6MF01002 152 48 2长沙 S6MF01002 152 50 2长沙 S6MF01002 152 52 1长沙 S6MF01002 201 46 1长沙 S6MF01002 201 48 2长沙 S6MF01002 201 50 2长沙 S6MF01002 201 52 1Select City,Style,Color,[46] From TestUnion allSelect City,Style,Color,[48] From TestUnion allSelect City,Style,Color,[50] From TestUnion allSelect City,Style,Color,[52] From Test就可以了===========================(例三)===============================CREATE TABLE tb (GroupName VARCHAR(64),Price decimal(10,2))INSERT INTO tbSELECT 'VIP客户',1011.00UNION ALLSELECT'⽩⾦卡会员',225.00UNION ALLSELECT'⽩⾦卡会员1',225.00UNION ALLSELECT'⽩⾦卡会员2',225.00UNION ALLSELECT'⽩⾦卡会员3',225.00UNION ALLSELECT'⽩⾦卡会员4',225.00UNION ALLSELECT'⽩⾦卡会员4',225.00--DROP TABLE tbdeclare @s nvarchar(max)set @s=''Select @s=@s+','+quotename(GroupName)+'=max(case when [GroupName]='+quotename(GroupName,'''')+' then [price] else 0 end)' from tb group by GroupNameSELECT @s=SUBSTRING(@s,2,LEN(@s))EXEC ('select '+@s+' from tb ')/*VIP客户⽩⾦卡会员⽩⾦卡会员1 ⽩⾦卡会员2 ⽩⾦卡会员3 ⽩⾦卡会员4--------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------1011.00 225.00 225.00 225.00 225.00 225.00(1 ⾏受影响)*/。

sql行转列的几种方法SQL语言中，行转列是一种常见的数据转换操作，用于将行数据转换为列数据，以便更方便地进行数据分析和统计。

在实际的数据处理中，行转列操作有多种方法可以实现，本文将介绍其中的几种常用方法。

一、使用CASE语句CASE语句是SQL语言中的条件表达式，可以根据条件返回不同的结果。

在行转列操作中，可以使用CASE语句将多个行数据转换为对应的列数据。

例如，有一个表格包含了员工的姓名和所属部门信息，现在要将部门信息转换为列数据，可以使用如下的SQL语句：```SELECT姓名,CASE WHEN 部门 = '部门A' THEN '是' ELSE '否' END AS 部门A,CASE WHEN 部门 = '部门B' THEN '是' ELSE '否' END AS 部门B,CASE WHEN 部门 = '部门C' THEN '是' ELSE '否' END AS 部门CFROM员工表;```上述SQL语句将根据不同的部门信息，将结果集中的部门列转换为对应的列数据，输出每个员工所属部门的信息。

二、使用PIVOT函数PIVOT函数是一种高级的行转列操作方法，在某些数据库中支持。

该函数可以将行数据转换为列数据，并实现数据的聚合操作。

例如，有一个表格包含了销售人员的姓名、所属部门和销售额信息，现在要将销售额按照部门进行汇总，并将部门数据转换为列数据，可以使用如下的SQL语句：```SELECT姓名,[部门A] AS 部门A销售额,[部门B] AS 部门B销售额,[部门C] AS 部门C销售额FROM(SELECT姓名,部门,销售额FROM销售表) AS 原始数据PIVOT(SUM(销售额)FOR 部门 IN ([部门A], [部门B], [部门C])) AS 转换后的数据;```上述SQL语句中，使用了PIVOT函数将原始数据按照部门进行汇总，并将部门数据转换为列数据，输出每个销售人员在不同部门的销售额信息。

SQL进阶-⾏转列列转⾏⼀、⾏转列1、建表CREATE TABLE score(student_id VARCHAR(20) NOT NULL COMMENT '学⽣编号'DEFAULT'',student_name VARCHAR(50) NOT NULL COMMENT '学⽣姓名'DEFAULT'',gender VARCHAR(10) NOT NULL COMMENT '学⽣性别'DEFAULT'',subject_name VARCHAR(50) NOT NULL COMMENT '课程名称'DEFAULT'',score INTEGER NOT NULL COMMENT '分数'DEFAULT0)ENGINE=MyISAM DEFAULT CHARSET=gbk COMMENT='学⽣成绩';DELETE FROM score;INSERT INTO score VALUES('S001','张三','男','⾼等数学',82);INSERT INTO score VALUES('S001','张三','男','计算机导论',67);INSERT INTO score VALUES('S001','张三','男','概率论',90);INSERT INTO score VALUES('S001','张三','男','机械原理',82);INSERT INTO score VALUES('S002','李四','男','⾼等数学',78);INSERT INTO score VALUES('S002','李四','男','计算机导论',76);INSERT INTO score VALUES('S002','李四','男','概率论',65);INSERT INTO score VALUES('S002','李四','男','⼏何学',43);INSERT INTO score VALUES('S003','王五','⼥','计算机导论',88);INSERT INTO score VALUES('S003','王五','⼥','概率论',98);INSERT INTO score VALUES('S003','王五','⼥','⼏何学',85);INSERT INTO score VALUES('S004','赵六','男','⾼等数学',84);INSERT INTO score VALUES('S004','赵六','男','计算机导论',76);INSERT INTO score VALUES('S004','赵六','男','机械原理',65);INSERT INTO score VALUES('S004','赵六','男','⼏何学',48);INSERT INTO score VALUES('S005','孙七','⼥','⾼等数学',34);INSERT INTO score VALUES('S005','孙七','⼥','计算机导论',91);INSERT INTO score VALUES('S005','孙七','⼥','概率论',82);INSERT INTO score VALUES('S005','孙七','⼥','机械原理',56);INSERT INTO score VALUES('S005','孙七','⼥','⼏何学',70);2、利⽤max(CASE ... WHEN ... THEN .. ELSE END) AS ""的⽅式来实现##利⽤max(CASE ... WHEN ... THEN .. ELSE END) AS的⽅式来实现##判断如果是这门学科，就取它的成绩，否则赋值为0，然后在成绩与0⾥取最⼤值SELECTstudent_id,student_name,MAX(CASE WHEN subject_name ='⾼等数学'THEN score ELSE0END) AS'⾼等数学',MAX(CASE WHEN subject_name ='计算机导论'THEN score ELSE0END) AS'计算机导论',MAX(CASE WHEN subject_name ='概率论'THEN score ELSE0END) AS'概率论',MAX(CASE WHEN subject_name ='机械原理'THEN score ELSE0END) AS'机械原理',MAX(CASE WHEN subject_name ='⼏何学'THEN score ELSE0END) AS'⼏何学'FROM scoreGROUP BYstudent_id,student_nameORDER BYstudent_id,student_name;3、求男⼥⽣各科平均成绩##平均成绩肯定是总分除以⼈数，但是呢，有的学⽣没有某⼀门学科的成绩，我们把它变成0，##变成0其实是不妥当的，因为变成0，在做除法的时候，也会算成⼀个⼈，这样除的结果就会不准确##所以应该把0变成nullSELECTgender,AVG(CASE WHEN subject_name ='⾼等数学'THEN score ELSE NULL END) AS'⾼等数学',AVG(CASE WHEN subject_name ='计算机导论'THEN score ELSE NULL END) AS'计算机导论',AVG(CASE WHEN subject_name ='概率论'THEN score ELSE NULL END) AS'概率论',AVG(CASE WHEN subject_name ='机械原理'THEN score ELSE NULL END) AS'机械原理',AVG(CASE WHEN subject_name ='⼏何学'THEN score ELSE NULL END) AS'⼏何学'FROM scoreGROUP BYgenderORDER BYgender;总结：⾏转列，分组(GROUP BY)的列必须是除需要⾏转列之外的业务主键。

sql列转行的实现方法在数据库操作中，有时需要将表格中的列转换为行。

这种转换通常用于数据分析和报表生成。

SQL（结构化查询语言）是用于操作数据库的标准语言，因此，掌握如何使用SQL进行列转行是非常重要的。

一、列转行的概念列转行是将数据库表格中的一列或多列数据转换为新的行数据的过程。

在转换后的表格中，每个数据项都被视为一个新的行，行标题为原始数据列的名称。

这个过程可以方便地对单一或多个列的数据进行进一步的分析和处理。

二、实现方法下面介绍几种常用的SQL语句来实现列转行的方法：1. 使用PIVOT语句PIVOT语句是一种非常有用的方法，可以将数据库表格中的列转换为行。

通过使用PIVOT语句，可以将具有相同值的列组合在一起，并将它们转换为新的行。

语法如下：```sqlSELECT 列名, 新行数据FROM 表格名PIVOT (聚合函数(原列名)FOR 原列名 IN (值1, 值2, 值3, ...)) AS pivotTable;```"quantity"两列数据。

可以使用以下语句将这两列转换为行：```sqlSELECT product, quantity AS revenueFROM salesPIVOT (SUM(quantity)FOR product IN ([产品1], [产品2], [产品3]) -- 替换为实际的产品名称列表) AS pivotTable;```这将返回一个包含每个产品名称和对应销售数量的新表格。

2. 使用UNPIVOT语句与PIVOT语句相反，UNPIVOT语句将行数据转换为列。

语法如下：```sqlSELECT 原列名, 新列名FROM 表格名UNPIVOT (原列数据 FOR 原列名 IN (值1, 值2, 值3, ...) ) AS unpivotTable;```和"quantity"两列数据，以及一个名为"revenue"的新列数据。

sql行转列函数SQL行转列函数是一种用于将数据从一行转换为多行，以便重新组织数据的函数，可以帮助你将记录从表中抽取出来，以便对数据进行更详细的分析。

SQL行转列（或行到列）函数是一类特别的函数，它们允许你把一行中的多个记录转换为多列，或把多个列转换为一行。

在开发大型数据库和应用程序时，使用SQL行转列函数是一种重要的技巧。

举个例子，如果你有一个表，其中包含一个字段，其中包含可能很多值，如果你想把这个字段的值拆分成多个字段，可以使用SQL行转列函数来实现这一目标。

SQL行转列函数的常用函数包括PIVOT，UNPIVOT，FLATTEN，CROSSTAB等。

PIVOT是将数据从行转换为列，将表中的某个行字段作为新列，将多列数据作为新行。

比如，假设有一个员工表，包括员工姓名，部门，岗位，年薪，月份以及月份薪水。

使用PIVOT函数，我们就可以把月份转换成列，并根据月份显示每个员工的年薪数据。

UNPIVOT函数可以把已经被转换为列的表记录转换回行记录。

例如，可以把前面的表被拆分成多列的情况翻转回单个列，使得更容易处理表中的信息。

FLATTEN函数可以把多个列转换成一行数据。

例如，用FLATTEN函数，可以把下列表中的四列转换成一列：列1，列2，列3和列4，就像这样：列1列2，列3列4其中一列便取代了原来的四列，数据变成一行表示，比如：1,2,3,4CROSSTAB函数可以把结果拆分成若干个分组，每组由一个有列名的列和一个聚合函数的值的列组成，可以提供更直观的结果。

SQL行转列函数可以大大提高SQL查询的效率，但在使用它们时也要小心，以降低带来的风险。

这类函数操作特定格式的表，因此，一旦表中的数据更改，可能会导致查询结果不一致，从而影响查询的结果。

总而言之，SQL行转列函数是一种有用的工具，可以帮助你重新组织数据，让你能够更轻松地获取所需的记录和数据。

正确使用它们有助于提高查询和数据分析的效率，而没有使用它们的话，你可能无法获得所需的数据。

SQL经典模式--列转⾏[转载]⼀般需要将列转成⾏来使⽤，⼀定是原有的Schema设计没有考虑周全。

但是没有办法，为了保护现有的投资，不得不在糟糕的设计上周旋，⽤最⼩的代价去实现新需求。

毕竟认识都是由浅⼊深，为不健全的Schema设计付出代价，就像交税⼀样，⽆可避免。

举例：课程表：每门课程由5位⽼师教，要求包含⽼师的信息，以及⼀些课程的信息create table cource (id int, name varchar(100), teacher1 int,teacher2 int,teacher3 int, teacher4 int, teacher5 int);insert into cource values (1,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (2,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (3,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (4,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (5,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (6,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (7,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (8,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (9,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (10,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (11,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14)); insert into cource values (12,concat('Course_',round(rand()*300)),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14),round(rand()*14));⽼师表：记录了每个⽼师的年龄，级别，性别create table teacher(id int, age int, level int, gender int);insert into teacher values (1, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (2, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (3, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (4, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (5, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (6, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (7, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (8, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (9, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (10, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (11, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (12, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (13, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);insert into teacher values (14, round(rand()*20+30), round(rand()*10), round(rand()*10)%2);需求：找出⼀些课程，这些课程是由2位以上男⽼师教，并且他们的级别⼤于3，并且他们年龄在40以下的。