测试数据
create table userTest( id int ,name varchar(20), passwd int, score int, age int);
insert into userTest values(1,'小明',111,67,21);
insert into userTest values(2,'小红',112,66,22);
insert into userTest values(3,'小梅',113,89,23);
insert into userTest values(4,'小东',114,99,24);
select * from userTest
行转列
select id , name, newColumn, newValue from userTest unpivot (newValue for newColumn in (passwd, score, age) )
说明:
1newColumn行转列后的列名
2newValue 列名:该列名对应newColumn,每一行都是列名:值的对应关系
3:
newColumn in (passwd, score, age):
newColumn :newColumn 的取值范围,注意passwd, score, age的类型要相同
4
newValue for newColumn in (passwd, score, age):
newValue :对应newColumn 的值