sql列转行的问题，总说语法错误，在线等，拜谢了！
" declare @s varchar(1000) "
" set @s = ' ' "
" select @s = @s+ ',max(case Name when ' ' ' + Name + ' ' ' then Data end) ' from 参数表 group by Name order by Name "
" set @s = left(@s,len(@s)-1) + 'from 参数表 group by Name ' "
" EXEC(@s) "
提示第一行 ', '附近有语法错误
可是网上查了N多大家都是这么写的呀
[解决办法]
--try
declare @s varchar(1000)
--set @s = ' '
select @s = coalesce(@s+ ', ', 'select ')+ 'max(case Name when ' ' ' + Name + ' ' ' then Data end) ' from 参数表 group by Name order by Name
--set @s = left(@s,len(@s)-1) + 'from 参数表 group by Name '
set @s = @s + ' from 参数表 group by Name '
EXEC(@s)
[解决办法]
declare @s varchar(1000)
set @s = ' '
select @s = 'select '+ 'max(case Name when ' ' ' + Name + ' ' ' then Data end) ' from 参数表 group by Name order by Name
set @s = left(@s,len(@s)) + 'from 参数表 group by Name '
EXEC(@s)
[解决办法]
---行转列??Try
declare @s varchar(1000)
set @s = ' '
select @s = @s+ ',max(case when Name= ' ' '+ Name+ ' ' ' then Data else ' ' ' ' end) As [ '+Name+ '] ' from 参数表 group by Name order by Name
--- " set @s = left(@s,len(@s)-1) + 'from 参数表 group by Name ' "
set @s= 'Select '+stuff(@s,1,1, ' ')+ ' from 参数表 '
EXEC(@s)
[解决办法]
declare @s varchar(1000)
set @s = ' '
Select @s=N 'Select Col '
select @s = @s+N ',max(case Name when ' ' ' + Name + ' ' ' then Data end) ' from 参数表 group by Name order by Name "
set @s = left(@s,len(@s)-1) + 'from 参数表 group by Name '
EXEC(@s)
[解决办法]
declare @s varchar(1000)
--set @s = ' '
select @s = coalesce(@s+ ', ', 'select ')+ 'max(case Name when ' ' ' + Name + ' ' ' then Data end) ' from 参数表 group by Name order by Name
--set @s = left(@s,len(@s)-1) + 'from 参数表 group by Name '
set @s = @s + ' from 参数表 group by Name '
print(@s)
--EXEC(@s)
--结果贴出来
[解决办法]
--PRINT都不行？新开一个查询窗口，就执行这几句，把 @s 打印出来，贴出：
declare @s varchar(1000)
select @s = coalesce(@s+ ', ', 'select ')+ 'max(case Name when ' ' ' + Name + ' ' ' then Data end) ' from 参数表 group by Name order by Name
set @s = @s + ' from 参数表 group by Name '
print(@s)
[解决办法]
我们的语句都没有错
怎么你????????????????要么给出你的表结构吧
------------------------------
Create Table 参数表(name varchar(10),data int)
Insert 参数表 Select '张三 ',100
Union All Select '李四 ',200
Union All Select '王五 ',300
Union All Select '老六 ',400
Select * From 参数表
---行转列语句
declare @s varchar(1000)
set @s = ' '
select @s = @s+ ',max(case when Name= ' ' '+ Name+ ' ' ' then Data else 0 end) As [ '+Name+ '] ' from 参数表 group by Name order by name
set @s= 'Select '+stuff(@s,1,1, ' ')+ ' from 参数表 ' ---这句去掉max前面那个,号
EXEC(@s)
---清除测试环境
Drop Table 参数表
---结果
/*
老六 李四 王五 张三
----------- ----------- ----------- -----------
400 200 300 100
*/
