SQL SERVER中的行传列

--建表语句
IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'SCORE_INFO') DROP TABLE SCORE_INFO;
CREATE TABLE SCORE_INFO(
SCORE_ID INT IDENTITY(1,1) NOT NULL,
USER_NAME VARCHAR(20) NOT NULL,
SCORE_NAME VARCHAR(40) NOT NULL,
SCORE_NUMBER INT NOT NULL,
SCORE_GRADE VARCHAR(20) NOT NULL,
CONSTRAINT SCORE_INFO_PK1 PRIMARY KEY(SCORE_ID),
CONSTRAINT SCORE_INFO_AK1 UNIQUE(USER_NAME,SCORE_NAME,SCORE_NUMBER)
);

--插入数据
INSERT INTO SCORE_INFO (USER_NAME,SCORE_NAME,SCORE_NUMBER,SCORE_GRADE) VALUES('张三','全栈','100','优秀');
INSERT INTO SCORE_INFO (USER_NAME,SCORE_NAME,SCORE_NUMBER,SCORE_GRADE) VALUES('张三','上位机','95','优秀');
INSERT INTO SCORE_INFO (USER_NAME,SCORE_NAME,SCORE_NUMBER,SCORE_GRADE) VALUES('张三','WEB开发','81','良好');
INSERT INTO SCORE_INFO (USER_NAME,SCORE_NAME,SCORE_NUMBER,SCORE_GRADE) VALUES('张三','架构班','70','一般');
INSERT INTO SCORE_INFO (USER_NAME,SCORE_NAME,SCORE_NUMBER,SCORE_GRADE) VALUES('张三','高级班','50','不合格');

--按数值型行传列
SELECT
[USER_NAME],
SUM(CASE WHEN [SCORE_NAME] = '全栈' THEN [SCORE_NUMBER] ELSE 0 END) AS '全栈',
SUM(CASE WHEN [SCORE_NAME] = '上位机' THEN [SCORE_NUMBER] ELSE 0 END) AS '上位机',
SUM(CASE WHEN [SCORE_NAME] = 'WEB开发' THEN [SCORE_NUMBER] ELSE 0 END) AS 'WEB开发',
SUM(CASE WHEN [SCORE_NAME] = '架构班' THEN [SCORE_NUMBER] ELSE 0 END) AS '架构班',
SUM(CASE WHEN [SCORE_NAME] = '高级班' THEN [SCORE_NUMBER] ELSE 0 END) AS '高级班'
FROM
[SCORE_INFO]
GROUP BY [USER_NAME];

--按字符型行传列
SELECT
[USER_NAME],
MAX(CASE WHEN [SCORE_NAME] = '全栈' THEN [SCORE_GRADE] END) AS '全栈',
MAX(CASE WHEN [SCORE_NAME] = '上位机' THEN [SCORE_GRADE] END) AS '上位机',
MAX(CASE WHEN [SCORE_NAME] = 'WEB开发' THEN [SCORE_GRADE] END) AS 'WEB开发',
MAX(CASE WHEN [SCORE_NAME] = '架构班' THEN [SCORE_GRADE] END) AS '架构班',
MAX(CASE WHEN [SCORE_NAME] = '高级班' THEN [SCORE_GRADE] END) AS '高级班'
FROM
[SCORE_INFO]
GROUP BY [USER_NAME];