DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' exec(@sql) into #tempselect * from #temp where .....
DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname GROUP BY spname SET @sql='select * from (SELECT MAX(Spshuliang) AS Spshuliang,spname FROM t_Xiaoshou GROUP BY spname) as T pivot (max(Spshuliang) for spname in ('+@sql+'))a' exec(@sql)把需要转的列生成内嵌表,如上执行看看结果是否正确
一:我要运行结果不显示Khbianhao,Psybianhao,Spbianhao,Xsshijian,应该怎么搞。 SET @sql='select [要显示的列]'+@sql+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' 那就把要显示列写一下,如上面那样就可以了二我要可以根据psyname(或者psybianhao) 和 Xsshijian查询应该怎么搞。 没明白,是根据上面那个生成的结果查,还是结果要有那几列,还是条件是根据这几个
大哥你这个有点问题啊,我截图你看一下 DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname --SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' SET @sql='select khname '+@sql+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' exec(@sql) 运行结果:
DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname --SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' SET @sql='select khname, '+@sql+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' exec(@sql)加个半角逗号
不对啊,提示into附近有错误--创建与exec(@sql)相同的表结构 #table,然后插入,随便想怎样就怎样DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' insert into #table exec(@sql)
DECLARE @columnlist VARCHAR(8000) DECLARE @sql VARCHAR(8000)SELECT @columnlist= ISNULL(@columnlist+',','')+spname FROM t_Xiaoshou GROUP BY spname -- 保留的固定字段+动态字段 SET @sql='select Khname,Psyname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@columnlist+'))a'exec(@sql) --psyname 做动态字段 SELECT @columnlist= ISNULL(@columnlist+',','')+psyname FROM t_Xiaoshou GROUP BY psyname SET @sql='select Khname,spname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for Psyname in ('+@columnlist+'))a'--psybianhao 做动态字段 SELECT @columnlist= ISNULL(@columnlist+',','')+psybianhao FROM t_Xiaoshou GROUP BY psybianhao SET @sql='select Khname,Psyname,spname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for psybianhao in ('+@columnlist+'))a' --Xsshijian 做动态字段 SELECT @columnlist= ISNULL(@columnlist+',','')+Xsshijian FROM t_Xiaoshou GROUP BY Xsshijian SET @sql='select Khname,Psyname,spname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for Xsshijian in ('+@columnlist+'))a'
不对啊,提示into附近有错误--创建与exec(@sql)相同的表结构 #table,然后插入,随便想怎样就怎样DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a' insert into #table exec(@sql)
这样不就是想当于建立了一个新的表格了么,可是spname 是动态的啊
select * from t_Xiaoshou where Xsshijian='' 明白了么
DECLARE @sql VARCHAR(8000) SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname SET @sql='select khname, '+@sql+' from (select * from t_Xiaoshou where Xsshijian='''')T pivot (max(Spshuliang) for spname in ('+@sql+'))a' exec(@sql)是这个意思吗
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou
GROUP BY spname
SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
exec(@sql)
into #tempselect * from #temp
where .....
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou GROUP BY spname
GROUP BY spname
SET @sql='select * from (SELECT MAX(Spshuliang) AS Spshuliang,spname FROM t_Xiaoshou GROUP BY spname) as T pivot (max(Spshuliang) for spname in ('+@sql+'))a'
exec(@sql)把需要转的列生成内嵌表,如上执行看看结果是否正确
SET @sql='select [要显示的列]'+@sql+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
那就把要显示列写一下,如上面那样就可以了二我要可以根据psyname(或者psybianhao) 和 Xsshijian查询应该怎么搞。
没明白,是根据上面那个生成的结果查,还是结果要有那几列,还是条件是根据这几个
大哥你这个有点问题啊,我截图你看一下
DECLARE @sql VARCHAR(8000)
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou
GROUP BY spname
--SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
SET @sql='select khname '+@sql+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
exec(@sql)
运行结果:
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou
GROUP BY spname
--SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
SET @sql='select khname, '+@sql+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
exec(@sql)加个半角逗号
那么问题来了。
二我要可以根据psyname(或者psybianhao) 和 Xsshijian查询应该怎么搞。
没明白,是根据上面那个生成的结果查,还是结果要有那几列,还是条件是根据这几个
大哥能把(根据上面那个生成的结果查,还是结果要有那几列)这两种都说一下么,我怎么觉得他们应该是一样的呢,,,,
前面和后面,效果应该会一样,
但,后面这个可以在出结果前就过滤,也就是EXEC的时候就执行
前面的话,可以保存到临时表或其它地方,然后,想查哪列就查哪列
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou
GROUP BY spname
SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
insert into #table
exec(@sql)
DECLARE @sql VARCHAR(8000)SELECT @columnlist= ISNULL(@columnlist+',','')+spname FROM t_Xiaoshou
GROUP BY spname -- 保留的固定字段+动态字段
SET @sql='select Khname,Psyname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@columnlist+'))a'exec(@sql)
--psyname 做动态字段
SELECT @columnlist= ISNULL(@columnlist+',','')+psyname FROM t_Xiaoshou GROUP BY psyname
SET @sql='select Khname,spname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for Psyname in ('+@columnlist+'))a'--psybianhao 做动态字段
SELECT @columnlist= ISNULL(@columnlist+',','')+psybianhao FROM t_Xiaoshou GROUP BY psybianhao
SET @sql='select Khname,Psyname,spname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for psybianhao in ('+@columnlist+'))a'
--Xsshijian 做动态字段
SELECT @columnlist= ISNULL(@columnlist+',','')+Xsshijian FROM t_Xiaoshou GROUP BY Xsshijian
SET @sql='select Khname,Psyname,spname,'+@columnlist+' from t_Xiaoshou pivot (max(Spshuliang) for Xsshijian in ('+@columnlist+'))a'
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou
GROUP BY spname
SET @sql='select * from t_Xiaoshou pivot (max(Spshuliang) for spname in ('+@sql+'))a'
insert into #table
exec(@sql)
这样不就是想当于建立了一个新的表格了么,可是spname 是动态的啊
明白了么
SELECT @sql= ISNULL(@sql+',','')+spname FROM t_Xiaoshou
GROUP BY spname
SET @sql='select khname, '+@sql+' from (select * from t_Xiaoshou where Xsshijian='''')T pivot (max(Spshuliang) for spname in ('+@sql+'))a'
exec(@sql)是这个意思吗