34,587
社区成员
发帖
与我相关
我的任务
分享
Select * from t_AuxItem where fitemclassid =3002
SELECT cname1,fname,color,
sum(case size when '41' then isnull(fqty,0) else 0 end) [s1],
sum(case size when '42' then isnull(fqty,0) else 0 end) [s2],
sum(case size when '43' then isnull(fqty,0) else 0 end) [s3],
sum(case size when '44' then isnull(fqty,0) else 0 end) [s4],
sum(case size when '45' then isnull(fqty,0) else 0 end) [s5],
sum(case size when '46' then isnull(fqty,0) else 0 end) [s6],
sum(case size when '47' then isnull(fqty,0) else 0 end) [s7],
sum(case size when '48' then isnull(fqty,0) else 0 end) [s8],
sum(case size when '49' then isnull(fqty,0) else 0 end) [s9],
max(fconsignprice)as price
into #lj_temp3
FROM #lj_temp2
group by cname1,fname,color
order by cname1,fname,color
列转行的问题搞定了,还有一个问题我怎么把某一行放到表的最后一行显示
create table T_AColor
(
cname1 varchar(50),
fname varchar(50),
fqty int,
fconsignp int,
color varchar(10),
size int
)
insert into T_AColor select '安徽安','JLR5859',1,175,12,45
union all select '安徽安','JLR5822',1,185,11,45
union all select '山东济','JLR5809',1,180,9,45
union all select '山东济','JLR5819',2,160,20,43
declare @sql varchar(8000)
set @sql = 'select cname1,fName,color '
select @sql = @sql + ' , max(case size when ' + CAST(a.size as varchar) + ' then fqty else 0 end) '''+CAST(a.size as varchar)+''''
from (select distinct ISNULL(size,0) as size from T_AColor ) a
set @sql = @sql + ',max(fconsignp) as fconsignp,max(fqty) as ''小计'' from T_AColor group by fname,cname1,color'
print(@sql)
exec(@sql)
select @sql = @sql + ' , sum(case size when '+ size +' then color else 0 end) [' + size + ']'
用sum
select @sql = @sql + ' , max(case size when '+ size +' then 1 else 0 end) [' + size + ']'