mssql数据库表行转列,列转行终极方案

sql语句实现行转列的3种方法实例

sql语句实现⾏转列的3种⽅法实例前⾔⼀般在做数据统计的时候会⽤到⾏转列，假如要统计学⽣的成绩，数据库⾥查询出来的会是这样的，但这并不能达到想要的效果，所以要在查询的时候做⼀下处理，下⾯话不多说了，来⼀起看看详细的介绍。

CREATE TABLE TestTable([Id] [int] IDENTITY(1,1) NOT NULL,[UserName] [nvarchar](50) NULL,[Subject] [nvarchar](50) NULL,[Source] [numeric](18, 0) NULL) ON [PRIMARY]goINSERT INTO TestTable ([UserName],[Subject],[Source])SELECT N'张三',N'语⽂',60 UNION ALLSELECT N'李四',N'数学',70 UNION ALLSELECT N'王五',N'英语',80 UNION ALLSELECT N'王五',N'数学',75 UNION ALLSELECT N'王五',N'语⽂',57 UNION ALLSELECT N'李四',N'语⽂',80 UNION ALLSELECT N'张三',N'英语',100GO这⾥我⽤了三种⽅法来实现⾏转列第⼀种：静态⾏转列select UserName 姓名,sum(case Subject when '语⽂' then Source else 0 end) 语⽂,sum(case Subject when '数学' then Source else 0 end) 数学,sum(case Subject when '英语' then Source else 0 end) 英语 from TestTable group by UserName⽤povit⾏转列select * from(select UserName,Subject,Source from TestTable) testpivot(sum(Source) for Subject in(语⽂,数学,英语)) pvt⽤存储过程⾏转列alter proc pro_test@userImages varchar(200),@Subject varchar(20),@Subject1 varchar(200),@TableName varchar(50)asdeclare @sql varchar(max)='select * from (select '+@userImages+' from'+@TableName+') tabpivot(sum('+@Subject+') for Subject('+@Subject1+')) pvt'exec (@sql)goexec pro_test 'UserName,Subject,Source','TestTable','Subject','语⽂，数学，英语'它们的效果都是这样的以上三种⽅式实现⾏转列，我们可以根据⾃⼰的需求采⽤不同的⽅法总结以上就是这篇⽂章的全部内容了，希望本⽂的内容对⼤家的学习或者⼯作具有⼀定的参考学习价值，如果有疑问⼤家可以留⾔交流，谢谢⼤家对的⽀持。

mssql数据库表行转列，列转行终极方案

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 就可以了。

MySQL行转列列转行

MySQL⾏转列列转⾏转载：⾏转列准备数据：CREATE TABLE tb_score(id INT(11) NOT NULL auto_increment,userid VARCHAR(20) NOT NULL COMMENT '⽤户id',subject VARCHAR(20) COMMENT '科⽬',score DOUBLE COMMENT '成绩',PRIMARY KEY(id))ENGINE = INNODB DEFAULT CHARSET = utf8;INSERT INTO tb_score(userid,subject,score) VALUES ('001','语⽂',90);INSERT INTO tb_score(userid,subject,score) VALUES ('001','数学',92);INSERT INTO tb_score(userid,subject,score) VALUES ('001','英语',80);INSERT INTO tb_score(userid,subject,score) VALUES ('002','语⽂',88);INSERT INTO tb_score(userid,subject,score) VALUES ('002','数学',90);INSERT INTO tb_score(userid,subject,score) VALUES ('002','英语',75.5);INSERT INTO tb_score(userid,subject,score) VALUES ('003','语⽂',70);INSERT INTO tb_score(userid,subject,score) VALUES ('003','数学',85);INSERT INTO tb_score(userid,subject,score) VALUES ('003','英语',90);INSERT INTO tb_score(userid,subject,score) VALUES ('003','政治',82);查询数据表中的内容：SELECT*FROM tb_score;如果我们需要将 subject 字段的多⾏内容选出来，作为结果集中的不同列，并根据 userid 进⾏分组显⽰对应的score，怎么实现呢？使⽤ case...when....then 进⾏⾏转列SELECT userid,SUM(CASE `subject` WHEN'语⽂'THEN score ELSE0END) AS'语⽂',SUM(CASE `subject` WHEN'数学'THEN score ELSE0END) AS'数学',SUM(CASE `subject` WHEN'英语'THEN score ELSE0END) AS'英语',SUM(CASE `subject` WHEN'政治'THEN score ELSE0END) AS'政治'FROM tb_scoreGROUP BY userid;使⽤ IF() 进⾏⾏转列SELECT userid,SUM(IF(`subject`='语⽂',score,0)) AS'语⽂',SUM(IF(`subject`='数学',score,0)) AS'数学',SUM(IF(`subject`='英语',score,0)) AS'英语',SUM(IF(`subject`='政治',score,0)) AS'政治'FROM tb_scoreGROUP BY userid;注意点：SUM() 是为了能够使⽤ GROUP BY 根据 userid 进⾏分组，因为每⼀个 userid 对应的 subject="语⽂" 的记录只有⼀条，所以SUM() 的值就等于对应那⼀条记录的 score 的值。

Sql2008的行列转换之行转列

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)这种写法是列名数量不固定的时候，需要动态⽣成。

sql行列转换的函数

sql行列转换的函数在SQL中，行列转换是通过将一列数据转换为多列或者将多列数据转换为一列来实现的。

这种转换可以通过使用聚合函数、CASE语句和PIVOT/UNPIVOT操作来完成。

以下是行列转换的相关参考内容：1. 使用聚合函数：聚合函数是SQL中非常重要的函数之一，可以用于将多行数据合并为一行。

在行列转换中，常用的聚合函数有SUM、COUNT、MAX和MIN等。

可以使用这些聚合函数将多行数据转换为一列。

例如，可以使用SUM函数将多个订单金额合并为一个总金额。

2. 使用CASE语句：CASE语句是SQL中的条件语句，可以根据条件选择不同的结果。

在行列转换中，可以使用CASE语句根据某个条件将多列数据转换为一列。

例如，可以使用CASE语句根据客户的信用等级将客户的名称转换为不同的等级。

3. 使用PIVOT/UNPIVOT操作：PIVOT和UNPIVOT是SQL中用于行列转换的操作符。

PIVOT操作可以将多行数据转换为多列，而UNPIVOT操作可以将多列数据转换为多行。

这些操作非常有用，尤其是在需要将多个属性的值转换为列的情况下。

例如，可以使用PIVOT操作将每个地区的销售额转换为列，以便更容易进行比较和分析。

4. 使用临时表或者表变量：在行列转换中，有时候需要使用临时表或者表变量来处理数据。

可以将原始数据存储在一个临时表或者表变量中，然后使用INSERT INTO语句将数据转换为多列或者一列。

5. 使用动态SQL：动态SQL是一种在查询执行期间动态生成SQL语句的方法。

在行列转换中，可以使用动态SQL来生成不同的SELECT语句，以实现将多列转换为一列或者将一列转换为多列。

总结：行列转换是SQL中非常常见和重要的操作之一。

通过使用聚合函数、CASE语句、PIVOT/UNPIVOT操作、临时表或者表变量以及动态SQL等方法，可以实现将一列数据转换为多列或者将多列数据转换为一列。

这些方法在实际应用中非常有用，可以大大提高数据的可读性和分析能力。

数据库列转行、行转列

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⾏转列，列转⾏⾏列互转复制代码create table test(id int,name varchar(20),quarter int,profile int)insert into test values(1,'a',1,1000)insert into test values(1,'a',2,2000)insert into test values(1,'a',3,4000)insert into test values(1,'a',4,5000)insert into test values(2,'b',1,3000)insert into test values(2,'b',2,3500)insert into test values(2,'b',3,4200)insert into test values(2,'b',4,5500)select * from test--⾏转列select id,name,[1] as"⼀季度",[2] as"⼆季度",[3] as"三季度",[4] as"四季度",[5] as"5"fromtestpivot(sum(profile)for quarter in([1],[2],[3],[4],[5]))as pvtcreate table test2(id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)insert into test2 values(1,'a',1000,2000,4000,5000)insert into test2 values(2,'b',3000,3500,4200,5500)select * from test2--列转⾏select id,name,quarter,profilefromtest2unpivot(profilefor quarter in([Q1],[Q2],[Q3],[Q4]))as unpvt复制代码sql替换字符串 substring replace复制代码--例⼦1：update tbPersonalInfo set TrueName = replace(TrueName,substring(TrueName,2,4),'**') where ID = 1--例⼦2：update tbPersonalInfo set Mobile = replace(Mobile,substring(Mobile,4,11),'********') where ID = 1--例⼦3：update tbPersonalInfo set Email = replace(Email,'chinamobile','******') where ID = 1复制代码SQL查询⼀个表内相同纪录 having如果⼀个ID可以区分的话，可以这么写select * from表where ID in (select ID from表 group by ID having sum(1)>1)如果⼏个ID才能区分的话，可以这么写select * from表where ID1+ID2+ID3 in(select ID1+ID2+ID3 from表 group by ID1,ID2,ID3 having sum(1)>1)其他回答：数据表是zy_bho,想找出ZYH字段名相同的记录复制代码--⽅法1：SELECT *FROM zy_bho a WHERE EXISTS(SELECT 1 FROM zy_bho WHERE [PK] <> a.[PK] AND ZYH = a.ZYH)--⽅法2：select a.* from zy_bho a join zy_bho bon (a.[pk]<>b.[pk] and a.zyh=b.zyh)--⽅法3：select * from zy_bbo where zyh in(select zyh from zy_bbo group by zyh having count(zyh)>1)--其中pk是主键或是 unique的字段。

[数据库]SQL查询语句表行列转换及一行数据转换成两列

[数据库]SQL查询语句表⾏列转换及⼀⾏数据转换成两列本⽂主要讲述了SQL查询语句表之间的⾏列转换，同时也包括如何将⼀⾏数据转换成两列数据的⽅法、⼦查询的应⽤、decode函数的⽤法。

希望⽂章对你有所帮助~1.创建数据库表及插⼊数据2.⼦查询统计不同性质的学⽣总数3.⼀⾏数据转换成两列数据 union all4.表⾏列数据转换(表转置)1.创建数据库表及插⼊数据创建、创建学⽣表并设置主键、插⼊数据代码如下：[sql]1. --创建数据库2. create database StudentMS3.4. --使⽤数据库5. use StudentMS6.7. --创建学⽣表 (属性:姓名、学号(pk)、学院、出⽣⽇期、性别、籍贯)8. create table xs9. (10. name varchar(10) not null,11. id varchar(10) not null,12. xy varchar(10),13. birthday datetime,14. xb char(2),15. jg varchar(8)16. )17.18. --创建学⽣表主键:学号19. alter table xs20. add constraint21. pk_xs primary key(id)22.23. --插⼊数据24. insert into xs25. (id, name, xb, birthday, xy, jg)26. values('1160001', '刘备', '男', '1991-11-5', '软件学院', '河北省');输出数据如下图所⽰：2.⼦查询统计不同性质的学⽣总数使⽤⼦查询统计不同学院总⼈数、不同性别总⼈数和河北/河南学⽣总⼈数。

[sql]1. --⼦查询统计⼈数2. select a.a_num as 软院⼈数, b.b_num as 计院⼈数, c.c_num as ⾃动化⼈数,3. d.d_num as 男⽣⼈数, e.e_num as ⼥⽣⼈数, f.f_num as 河北河南⼈数4. from5. (select count(*) as a_num from xs where xy='软件学院') a,6. (select count(*) as b_num from xs where xy='计算机学院') b,7. (select count(*) as c_num from xs where xy='⾃动化学院') c,8. (select count(*) as d_num from xs where xb='男') d,9. (select count(*) as e_num from xs where xb='⼥') e,10. (select count(*) as f_num from xs where jg in ('河北省','河南省')) f;输出结果：PS：若中⽂汉字太长报错，则需引⽤双引号。