[sqlServer]层状设计

原表结构：

Summary
存货分类档案
Columns

	Column Name	Description	Datatype	Length	Allow Nulls
	cInvCCode	存货大类编码	varchar	12	False
	cInvCName	存货大类名称	varchar	20	False
	iInvCGrade	编码级次	tinyint	1	False
	bInvCEnd	是否末级	bit	1	True
	cEcoCode	所属经济分类编码	varchar	2	True
	cBarCode	对应条形码编码	varchar	30	True
	pubufts	时间戳	timestamp	8	True

意图 ： 一级物料类别名称－－－二级物料类别名称－－－三级物料类别名称

将 此物料的类别进行层状叠加
建立试图

ALTER  view v_leniz_step_inventoryclass as
select
vl.cinvccode,
vl.classCode1, isNUll(invl1.cinvcname,'') as className1,
vl.classCode2, isNUll(invl2.cinvcname,'') as className2,
vl.classCode3, isNUll(invl3.cinvcname,'') as className3,
vl.classCode4, isNUll(invl4.cinvcname,'') as className4,
vl.classCode5, isNUll(invl5.cinvcname,'') as className5,
vl.classCode6, isNUll(invl6.cinvcname,'') as className6
from 
v_leniz_inventoryclass vl
left join inventoryclass invl1 on  vl.classCode1 = invl1.cinvccode
left join inventoryclass invl2 on  vl.classCode2 = invl2.cinvccode
left join inventoryclass invl3 on  vl.classCode3 = invl3.cinvccode
left join inventoryclass invl4 on  vl.classCode4 = invl4.cinvccode
left join inventoryclass invl5 on  vl.classCode5 = invl5.cinvccode
left join inventoryclass invl6 on  vl.classCode6 = invl6.cinvccode

试图二：

create view v_leniz_stepclass as
SELECT
   cinvccode as classCode,
   CASE
      WHEN len(cinvccode)=1  Then className1
      WHEN len(cinvccode)=3  Then className1 + '-' + className2
      WHEN len(cinvccode)=5  Then className1 + '-' + className2 + '-' + className3
      WHEN len(cinvccode)=7  Then className1 + '-' + className2 + '-' + className3 + '-' + className4
      WHEN len(cinvccode)=9  Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5
      WHEN len(cinvccode)=11 Then className1 + '-' + className2 + '-' + className3 + '-' + className4 + '-' + className5 + '-' + className6
      ELSE ''
   END AS className
FROM v_leniz_step_inventoryclass