这是sql server 中的sql 语句 作用是 动态的行转列
drop table #sc2
select sc.snum,sname,cname,score into #sc2 from sc,s,c where sc.snum in(select snum from sclass,class where sclass.classNum=class.classNum and class.className='物联网工程151') and s.snum=sc.snum and c.cnum=sc.cnum
DECLARE @sql_str NVARCHAR(MAX)
DECLARE @sql_col NVARCHAR(MAX)
DECLARE @tableName SYSNAME --行转列表
DECLARE @groupColumn SYSNAME --分组字段
DECLARE @row2column SYSNAME --行变列的字段
DECLARE @row2columnValue SYSNAME --行变列值的字段
SET @tableName = '#sc2'
SET @groupColumn = 'sname'
SET @row2column = 'cname'
SET @row2columnValue = 'score'
--从行数据中获取可能存在的列
SET @sql_str = N'
SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])
FROM ['+@tableName+'] GROUP BY ['+@row2column+']'
--PRINT @sql_str
EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT
--PRINT @sql_col
SET @sql_str = N'
SELECT * FROM (
SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT
(SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt
ORDER BY pvt.['+@groupColumn+']'
--PRINT (@sql_str)
EXEC (@sql_str)
qt中的语句
QString string =
" select sc.snum,sname,cname,score into sc2 from sc,s,c where sc.snum in(select snum from sclass,class where sclass.classNum=class.classNum and class.className='物联网工程151') and s.snum=sc.snum and c.cnum=sc.cnum"
" DECLARE @sql_str NVARCHAR(MAX)"
" DECLARE @sql_col NVARCHAR(MAX)"
" DECLARE @tableName SYSNAME "//--行转列表
" DECLARE @groupColumn SYSNAME "//--分组字段
" DECLARE @row2column SYSNAME"// --行变列的字段
" DECLARE @row2columnValue SYSNAME "//--行变列值的字段
" SET @tableName = 'sc2'"
" SET @groupColumn = 'sname'"
" SET @row2column = 'cname'"
" SET @row2columnValue = 'score'"
// " --从行数据中获取可能存在的列"
" SET @sql_str = N'"
" SELECT @sql_col_out = ISNULL(@sql_col_out + '','','''') + QUOTENAME(['+@row2column+'])"
" FROM ['+@tableName+'] GROUP BY ['+@row2column+']'"
// " --PRINT @sql_str"
" EXEC sp_executesql @sql_str,N'@sql_col_out NVARCHAR(MAX) OUTPUT',@sql_col_out=@sql_col OUTPUT"
// " --PRINT @sql_col"
" SET @sql_str = N'"
" SELECT * FROM ("
" SELECT ['+@groupColumn+'],['+@row2column+'],['+@row2columnValue+'] FROM ['+@tableName+']) p PIVOT"
" (SUM(['+@row2columnValue+']) FOR ['+@row2column+'] IN ( '+ @sql_col +') ) AS pvt"
" ORDER BY pvt.['+@groupColumn+']'"
// " --PRINT (@sql_str)"
" EXEC (@sql_str)"
" DROP TABLE sc2";
QSqlQuery query;
query.prepare(string);
query.exec();
QSqlQueryModel *model = new QSqlQueryModel;
model->setQuery(query);
tableView->setModel(model);
在sql server 2012下可正确 执行 如下图
在QT 5.8下 执行却没有结果 如下图
程序中 其他的函数里执行的sql语句 都是可以返回结果的
不过都比较简单
贴上来的这个sql语句虽然比较复杂 但是 我在QT把sql语句打印出来 在复制到 sql server
里运行 也是正确的
不太清楚是哪里有问题