一个表为
homeworkid title .....
一个表为
userid username ....
一个表为
userid homeworkid finished ....
想查询出一个返回为
userid homework1 homework2 ....(个数看homework表记录个数)
1 finished? finished? (内容在第三个表中)
2 ... ....
如何做?
homeworkid title .....
一个表为
userid username ....
一个表为
userid homeworkid finished ....
想查询出一个返回为
userid homework1 homework2 ....(个数看homework表记录个数)
1 finished? finished? (内容在第三个表中)
2 ... ....
如何做?
drop table tb
gocreate table tb(userid varchar(10),homeworkid varchar(10),finished varchar(10))
insert into tb(userid,homeworkid,finished) values('1','A','AA')
insert into tb(userid,homeworkid,finished) values('1','B','BB')
insert into tb(userid,homeworkid,finished) values('1','C','CC')
insert into tb(userid,homeworkid,finished) values('2','A','AA')
insert into tb(userid,homeworkid,finished) values('2','B','BB')
insert into tb(userid,homeworkid,finished) values('3','A','AA')
insert into tb(userid,homeworkid,finished) values('3','C','CC')
godeclare @sql varchar(8000)
set @sql = 'select userid'
select @sql = @sql + ' , max(case homeworkid when ''' + homeworkid + ''' then finished else '''' end) [' + homeworkid + ']'
from (select distinct homeworkid from tb) as a
set @sql = @sql + ' from tb group by userid'
exec(@sql) drop table tb
/*
userid A B C
---------- ---------- ---------- ----------
1 AA BB CC
2 AA BB
3 AA CC
*/