行转列或列转行写法_hui_unix-ChinaUnix博客

行转列或列转行写法_hui_unix-ChinaUnix博客

/*实现行转列anameobjecscoreaEN89aCH78aHO99bEN34bCH88bHO66要求输出结果为：nameENCHHOa897899b348866*/USE tempdbIF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL DROP TABLE tCREATE TABLE t(NAMECHAR(10),objecCHAR(10),scoreFLOAT)INSERT INTO tVALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)SELECT * FROM TSELECT NAME ,MAX(CASE objec WHEN 'EN' THEN score ELSE 0 END ) EN,MAX(CASE objec WHEN 'CH' THEN score ELSE 0 END ) CH,MAX(CASE objec WHEN 'HO' THEN score ELSE 0 END ) HOFROM tGROUP BY NAME --=======================================================================================================================================================/*实现行转列anameobjecscoreaEN89aCH78aHO99bEN34bCH88bHO66要求输出结果为：nameobjectotalsorceaEN,CH,HO266bEN,CH,HO188*/USE tempdbIF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL DROP TABLE tCREATE TABLE t(NAMECHAR(10),objecCHAR(10),scoreFLOAT)INSERT INTO tVALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)SELECT * FROM T--为了去除objec中的最后一个逗号，插入一个临时表，并使用substring处理SELECT name ,(SELECT LTRIM(RTRIM(objec)) ',' FROM T WHERE objec=t.objec FOR XML PATH('')) objec,SUM(temp.score) totalscroe INTO #tmpFROM T tempGROUP BY name SELECT NAME ,SUBSTRING(objec,0,LEN(objec)-1) objec ,totalscroeFROM #tmp

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。