DB: 将sql查询结果的行转换为列

有的时候,需要将sql查询结果建立成视图以供使用(或者每次使用该sql作为其它查询的子语句)。但是,在某些情况下,比如出于统计的需要,需将这种查询结果的某一字段的各行转换为列。

方法一:静态方式。

使用CASE列举要转为列的值。

例如:

CREATE  TABLE [StudentScores]
(
   [UserName]         NVARCHAR(20),        --学生姓名
    [Subject]          NVARCHAR(30),        --科目
    [Score]            FLOAT,               --成绩
)

INSERT INTO [StudentScores] SELECT 'Nick', '语文', 80

INSERT INTO [StudentScores] SELECT 'Nick', '数学', 90

INSERT INTO [StudentScores] SELECT 'Nick', '英语', 70

INSERT INTO [StudentScores] SELECT 'Nick', '生物', 85

INSERT INTO [StudentScores] SELECT 'Kent', '语文', 80

INSERT INTO [StudentScores] SELECT 'Kent', '数学', 90

INSERT INTO [StudentScores] SELECT 'Kent', '英语', 70

INSERT INTO [StudentScores] SELECT 'Kent', '生物', 85

如果我想知道每位学生的每科成绩,而且每个学生的全部成绩排成一行,这样方便我查看、统计,导出数据

SELECT
  UserName,
  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

方法二:动态方式。

使用动态sql语句实现。

举例:

T_TEST表:

T_TEST5

DECLARE @cmdText VARCHAR(8000);
DECLARE @tmpSql VARCHAR(8000);

SET @cmdText = ‘SELECT personid ,’ + CHAR(10);

SELECT @cmdText = @cmdText + ‘ CASE title WHEN ”’ + title + ”’ THEN SUM(score) ELSE 0 END AS ”’ + title
+ ”’,’ + CHAR(10) FROM (SELECT DISTINCT title FROM t_test ) T

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2)

–注意这里,如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)

SET @cmdText = @cmdText + ‘ FROM t_test GROUP BY personid, title ‘;
print @cmdText

—此刻cmdText的值–

–SELECT personid ,
–CASE title WHEN ‘C语言’ THEN SUM(score) ELSE 0 END AS ‘C语言’,
–CASE title WHEN ‘计算机应用技术’ THEN SUM(score) ELSE 0 END AS ‘计算机应用技术’,
–CASE title WHEN ‘图论’ THEN SUM(score) ELSE 0 END AS ‘图论’ FROM t_test GROUP BY –personid, title


SET @tmpSql =’SELECT personid,’ + CHAR(10);
SELECT @tmpSql = @tmpSql + ‘ ISNULL(SUM(‘ + title + ‘), 0) AS ”’ + title + ”’,’ + CHAR(10)
FROM (SELECT DISTINCT title FROM t_test ) T

SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ‘ FROM (‘ + CHAR(10);
print @tmpSql

—此刻tmpsql的值–

–SELECT personid,
–ISNULL(SUM(C语言), 0) AS ‘C语言’,
–ISNULL(SUM(计算机应用技术), 0) AS ‘计算机应用技术’,
–ISNULL(SUM(图论), 0) AS ‘图论’ FROM (


SET @cmdText = @tmpSql + @cmdText + ‘) T GROUP BY personid ‘;
PRINT @cmdText

—此刻cmdText的值–

–SELECT personid,
–ISNULL(SUM(C语言), 0) AS ‘C语言’,
–ISNULL(SUM(计算机应用技术), 0) AS ‘计算机应用技术’,
–ISNULL(SUM(图论), 0) AS ‘图论’ FROM (
–SELECT personid ,
–CASE title WHEN ‘C语言’ THEN SUM(score) ELSE 0 END AS ‘C语言’,
–CASE title WHEN ‘计算机应用技术’ THEN SUM(score) ELSE 0 END AS ‘计算机应用技术’,
–CASE title WHEN ‘图论’ THEN SUM(score) ELSE 0 END AS ‘图论’ FROM t_test GROUP BY –personid, title ) T GROUP BY personid


EXECUTE (@cmdText);

结果:

T_TEST522

增加统计列:

DECLARE @cmdText VARCHAR(8000);
DECLARE @tmpSql VARCHAR(8000);

SET @cmdText = ‘SELECT personid ,’ + CHAR(10);

SELECT @cmdText = @cmdText + ‘ CASE title WHEN ”’ + title + ”’ THEN SUM(score) ELSE 0 END AS ”’ + title
+ ”’,’ + CHAR(10) FROM (SELECT DISTINCT title FROM t_test ) T

SET @cmdText = LEFT(@cmdText, LEN(@cmdText) -2) –注意这里,如果没有加CHAR(10) 则用LEFT(@cmdText, LEN(@cmdText) -1)

SET @cmdText = @cmdText + ‘,sum(score) as smscore FROM t_test GROUP BY personid, title ‘;
print @cmdText
SET @tmpSql =’SELECT personid,’ + CHAR(10);
SELECT @tmpSql = @tmpSql + ‘ ISNULL(SUM(‘ + title + ‘), 0) AS ”’ + title + ”’,’ + CHAR(10)
FROM (SELECT DISTINCT title FROM t_test ) T

SET @tmpSql = LEFT(@tmpSql, LEN(@tmpSql) -2) + ‘,CAST(AVG(smscore) as decimal(10,2)) as 成绩 FROM (‘ + CHAR(10);
print @tmpSql
SET @cmdText = @tmpSql + @cmdText + ‘) T GROUP BY personid ‘;
PRINT @cmdText
EXECUTE (@cmdText);

最终的sql:

SELECT personid,
ISNULL(SUM(C语言), 0) AS ‘C语言’,
ISNULL(SUM(计算机应用技术), 0) AS ‘计算机应用技术’,
ISNULL(SUM(图论), 0) AS ‘图论’,CAST(AVG(smscore) as decimal(10,2)) as 成绩 FROM (
SELECT personid ,
CASE title WHEN ‘C语言’ THEN SUM(score) ELSE 0 END AS ‘C语言’,
CASE title WHEN ‘计算机应用技术’ THEN SUM(score) ELSE 0 END AS ‘计算机应用技术’,
CASE title WHEN ‘图论’ THEN SUM(score) ELSE 0 END AS ‘图论’,sum(score) as smscore FROM t_test GROUP BY personid, title ) T GROUP BY personid

T_TEST5222

//参考:http://www.cnblogs.com/kerrycode/archive/2010/07/28/1786547.html

http://www.cnblogs.com/maanshancss/archive/2013/03/13/2957108.html

http://database.51cto.com/art/201007/214466.htm

http://www.cnblogs.com/longle/archive/2011/10/08/rowstocolumns.html
http://blog.sina.com.cn/s/blog_54eeb5d90102weo3.html

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s