SQL 怎样做成按要求查询


怎样通过SQL语句变成 下面的效果：

.net java SQL

张三 80 85 95

李四 86 92

王五 96 88 78

[解决办法]

SQL code

--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([name] varchar(4),[sub] varchar(4),[score] int)insert [test]select '张三','.net',80 union allselect '张三','java',85 union allselect '张三','SQL',95 union allselect '李四','.net',86 union allselect '李四','java',92 union allselect '王五','.net',96 union allselect '王五','java',88 union allselect '王五','SQL',78select * from     [test] pivot     (max([Score]) for [sub] in([.net],[java],[SQL]))b/*name    .net    java    SQL李四    86    92    NULL王五    96    88    78张三    80    85    95*/
[解决办法]
SQL code
create table tbc (name VARCHAR(100), sub VARCHAR(100), score INT)insert into tbcSELECT '张三', '.net', 80 UNIONSELECT '张三', 'java', 85 UNIONSELECT '张三', 'SQL', 95 UNIONSELECT '李四', '.net', 86 UNIONSELECT '李四', 'java', 92 UNION   SELECT '王五', '.net', 96 UNIONSELECT '王五', 'java', 88 UNIONSELECT '王五', 'SQL', 78select name,[.net],[java],[sql]from tbcpivot (max(score) for sub in([.net],[java],sql)) as d name    .net    java    sql李四    86    92    NULL王五    96    88    78张三    80    85    95