SQL 语句(2)

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

 

转载于:https://www.cnblogs.com/chyen/archive/2012/11/01/2749853.html