点击(此处)折叠或打开
- /*
实现行转列
a
nameobjecscore
aEN89
aCH78
aHO99
bEN34
bCH88
bHO66
要求输出结果为:
nameENCHHO
a897899
b348866
*/
USE tempdb
IF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL
DROP TABLE t
CREATE TABLE t
(
NAMECHAR(10),
objecCHAR(10),
scoreFLOAT
)
INSERT INTO t
VALUES('a','EN',89),('a','CH',78),('a','HO',99),('b','EN',34),('b','CH',88),('b','HO',66)
SELECT * FROM T
SELECT 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 ) HO
FROM t
GROUP BY NAME
--=======================================================================================================================================================
/*
实现行转列
a
nameobjecscore
aEN89
aCH78
aHO99
bEN34
bCH88
bHO66
要求输出结果为:
nameobjectotalsorce
aEN,CH,HO266
bEN,CH,HO188
*/
USE tempdb
IF (SELECT 1 FROM sys.sysobjects WHERE name ='t' AND type='u') is NOT NULL
DROP TABLE t
CREATE TABLE t
(
NAMECHAR(10),
objecCHAR(10),
scoreFLOAT
)
INSERT INTO t
VALUES('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 #tmp
FROM T temp
GROUP BY name
SELECT NAME ,SUBSTRING(objec,0,LEN(objec)-1) objec ,totalscroe
FROM #tmp