34,588
社区成员
发帖
与我相关
我的任务
分享
WITH DATA(物料编码,物料名称,规格型号,单位名称,批号,仓库代码,仓库名称,数量) AS(
select '001', 'A1','10毫米','条','20180106','001','材料仓1',100
union all
select '001','A1','10毫米', '条','20180106','001','材料仓2',200
union all
select '001','A1','10毫米', '条','20180106','001','材料仓3',300
union all
select '001','A1','10毫米', '条','20180208','001','材料仓1',100
union all
select '001','A1','10毫米', '条','20180208','001','材料仓2' ,200
union all
select '002','A2','20毫米', '颗','T-1', '001','材料仓1',100
union all
select '002','A2','20毫米', '颗','T-2', '001','材料仓2',200
union all
select '002','A2','20毫米', '颗','T-3', '001','材料仓3',300
)
SELECT * FROM(
SELECT 批号,物料编码,物料名称,规格型号,单位名称,数量,仓库名称 FROM DATA
) PDATA
PIVOT( SUM(数量) FOR 仓库名称 IN(材料仓1,材料仓2,材料仓3) )P
create table #ta
(item_code varchar(10),
item_name varchar(10),
item_type nvarchar(20),
unit_type nvarchar(10),
bat_no varchar(20),
warehouse_id varchar(10),
warehouse_name nvarchar(20),
qty int)
insert into #ta
select '001', 'A1','10毫米','条','20180106','001','材料仓1',100
union all
select '001','A1','10毫米', '条','20180106','001','材料仓2',200
union all
select '001','A1','10毫米', '条','20180106','001','材料仓3',300
union all
select '001','A1','10毫米', '条','20180208','001','材料仓1',100
union all
select '001','A1','10毫米', '条','20180208','001','材料仓2' ,200
union all
select '002','A2','20毫米', '颗','T-1', '001','材料仓1',100
union all
select '002','A2','20毫米', '颗','T-2', '001','材料仓2',200
union all
select '002','A2','20毫米', '颗','T-3', '001','材料仓3',300
declare @str nvarchar(1000)
select @str=ISNULL(@str+',','')+QUOTENAME(warehouse_name,'[') from (select distinct warehouse_name from #ta) as A
print @str
declare @sql nvarchar(2000)
set @sql='select * from (select bat_no,item_code,item_name,item_type,unit_type,warehouse_name,qty from #ta) A
pivot (sum(qty) for warehouse_name in('+@str+')) B'
exec(@sql)