SET NOCOUNT ON
;
DECLARE
@str_level varchar(2000)
,@str_len int = 0
IF OBJECT_ID(N'tempdb..#level') IS NOT NULL
DROP TABLE #level
;
CREATE TABLE #level (
id int IDENTITY(1,1)
PRIMARY KEY
,level_name varchar(2000)
)
;
SET @str_level=RTRIM(LTRIM(' ch_men-hp_hp-one_23_45_6_8_4 '))+'_'
WHILE @str_len<LEN(@str_level)
BEGIN
SET @str_len=CHARINDEX(N'_',@str_level,@str_len+1)
INSERT INTO #level
SELECT LEFT(@str_level,@str_len-1)
END
DECLARE
@str_col varchar(2000)=''
,@str varchar(max)
SELECT
@str_col =@str_col+',['+CAST(id AS varchar(2000))+']'
FROM #level
SET @str='
SELECT
'
+ STUFF(@str_col,1,1,'')+
'
FROM #level A
PIVOT(
MAX(A.level_name )
FOR ID IN
(
'+ STUFF(@str_col,1,1,'')+
'
)) B'
EXEC (@str)
ALTER FUNCTION [dbo].[fn_RefLevel] (@RefValue nvarchar(2000))
RETURNS @RefValueDetail TABLE
(
one nvarchar(200),
two nvarchar(200),
three nvarchar(200),
four nvarchar(200),
five nvarchar(200),
six nvarchar(200),
seven nvarchar(200),
eight nvarchar(200),
nine nvarchar(200)
)
AS
begin
declare @RefValue2 varchar(2000);
set @RefValue2= REPLACE(@RefValue,'-','_')+'_1';
--select @RefValue2;
declare @id int;
set @id=1;
with e1 (N) as
(
select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
),
e2 (N) as (select a.N from e1 a cross join e1 b), -- 7*7 rows
e4 (N) as (select a.N from e2 a cross join e2 b), -- 49*49 rows
cteTally (N) as
(
select row_number() over (order by (select null)) from e4
),
cteRefDetail(N) as
(
select N FROM cteTally
WHERE N < LEN(@RefValue2) AND SUBSTRING(@RefValue2,N,1) = '_'
),
cteRef(id,shu,n) as(
select ROW_NUMBER() OVER(ORDER BY N) AS _ID,LEFT(@RefValue,N-1) as shu,'1' as n from cteRefDetail
)
insert into @RefValueDetail
select left([1],200),left([2],200),left([3],200),left([4],200),left([5],200)
,left([6],200),left([7],200),left([8],200),left([9],200)
from cteRef
pivot(max(shu) for id in ([1],[2],[3],[4],[5],[6],[7],[8],[9])) as pvt;
return
End
