表名:method
字段:ddbh,site,method,size,value,unit (类型全为varchar)
记录1:yp100,腰围,直量,XL,15,英寸
记录2:yp100,腰围,直量,M,30,英寸
记录3:yp100,腰围,侧量,XL,15,英寸 想要的结果:
列名: ddbh,site,method,XL,M,unit
yp100,腰围,直量,15,30,英寸
yp100,腰围,侧量,15,null,英寸要求ddbh为参数传进来
字段:ddbh,site,method,size,value,unit (类型全为varchar)
记录1:yp100,腰围,直量,XL,15,英寸
记录2:yp100,腰围,直量,M,30,英寸
记录3:yp100,腰围,侧量,XL,15,英寸 想要的结果:
列名: ddbh,site,method,XL,M,unit
yp100,腰围,直量,15,30,英寸
yp100,腰围,侧量,15,null,英寸要求ddbh为参数传进来
@ddbh varchar(100)
as
begin
select ddbh,site,method,xl =max(case when size = 'XL' then size else null end),
M = max(case when size = 'M' then size else null end),unit from method where ddbh = @ddbh
end
go
@ddbh varchar(100)
as
begin
select ddbh,
site,
method,
xl =max(case when size = 'XL' then size else null end),
M = max(case when size = 'M' then size else null end),
unit
from method
where ddbh = @ddbh
group by ddbh,
site,
method,
unit
end
go
M = max(case when size = 'M' then size else null end),unit from method where .....
函数转换,写成动态的sql吧
CREATE PROCEDURE [dbo].[selectmethod]
(
@ddbh VARCHAR(10)
)
AS
BEGIN
DECLARE @sql VARCHAR(8000)
SET @sql = 'SELECT ddbh,site,method'
SELECT @sql = @sql + ',MIN(CASE WHEN size = '''+ size + ''' THEN value END) AS ['+ size +']'
FROM (SELECT DISTINCT size FROM method) A
ORDER BY size DESC SELECT @sql = @sql + ',unit FROM method WHERE ddbh = ''' + @ddbh
+ ''' GROUP BY ddbh,site,method,unit'
EXEC (@sql)
END
GO
@ddbh varchar(100)
as
begin
select ddbh,
site,
method,
xl =max(case when size = 'XL' then size else null end),
M = max(case when size = 'M' then size else null end),
unit
from method
where ddbh = @ddbh
group by ddbh,
site,
method,
unit
end
go
@ddbh varchar(100)
as
begin
select ddbh,
site,
method,
xl =max(case when size = 'XL' then size else null end),
M = max(case when size = 'M' then size else null end),
unit
from method
where ddbh = @ddbh
group by ddbh,
site,
method,
unit
end
go