CTE Common table expression RECURSIVE (Fibonacci, Factorial)

EJEMPLO 1

WITH CTE

AS

(

 SELECT i = 1

 UNION ALL

 SELECT       i + 1

 FROM        CTE

 WHERE       i <= 10

)

SELECT *

FROM CTE

OTRO EJEMPLO DE RECURSIVIDAD

DECLARE @Cadena     VARCHAR (30);

SET @Cadena ='GPXDCWUJREMKIALVOTFNYBZSHQ';

WITH BURBUJA

AS

  (

   SELECT Id = 1

   UNION ALL -- EL CÓDIGO DE ARRIBA SE EJECUTA UNA SOLA VEZ, MIENTRAS QUE ABAJO ES RECURSIVO

   SELECT    Id + 1

   FROM             BURBUJA

   WHERE     Id < LEN (@Cadena)

  )

   SELECT    Id

                    , Letra = SUBSTRING (@Cadena, Id, 1)

   FROM             BURBUJA

   ORDER BY  Letra

EJEMPLO 3

WITH CTE

AS

  (

   SELECT Id = 1

   UNION ALL -- EL CÓDIGO DE ARRIBA SE EJECUTA UNA SOLA VEZ, MIENTRAS QUE ABAJO ES RECURSIVO

   SELECT    Id  + 1

   FROM             CTE

   WHERE     Id < 17

  )

   -- AQUI ESTÁ EL TRUCO PARA METER ESPACIOS EN UN TIPO DE DATO NUMÉRICO (arriba)

   SELECT    Id = REPLICATE(' ', Id) +  CONVERT (VARCHAR(20), Id)

                   

   FROM             CTE

   ORDER BY CONVERT(TINYINT,LTRIM(ID))

FACTORIAL

DECLARE @Factorial  TINYINT = 5;

WITH Factorial

AS

(

       SELECT   Id = 1

                    ,Resultado = 1

       UNION ALL

       SELECT Id + 1

                    ,Resultado = Resultado * (Id + 1)

       FROM   Factorial

       WHERE  Id < @Factorial

)

 SELECT             *

 FROM        Factorial

FIBONACCI

DECLARE @Fibonacci  TINYINT = 10;

WITH FibonacciCalculation

AS

(

      

       SELECT  

                     Id                 = 2

                    ,F                  = 1         

                    ,Contador    = 1         

      

       UNION ALL

       SELECT Id                 = Id + 1

                    ,F                  =  Contador

                    ,Resultado    =  F + Contador

       FROM   FibonacciCalculation

       WHERE  Id < @Fibonacci

)

 SELECT             Id = 1, Fibonacci = 1   -- para evitar evaluar todos los numeros 

                    UNION ALL

 SELECT            

                    Id

                    ,Fibonacci = F

 FROM        FibonacciCalculation

 OPTION (MAXRECURSION 0);

Especificar Parametros Con: un Valor ó NULL; para FILTRAR el valor o traer todo

DECLARE @ID INT

SET @ID = NULL;

WITH T1

AS

(

SELECT ID = 1

UNION ALL

SELECT ID = 2

)

SELECT *

FROM T1

WHERE ID = @ID OR @ID IS NULL

En este caso, como 

Es este caso, como el parámetro tiene un valor NULL, regresa todo.


si el parámetro tuviera un valor,  filtraría bien ese valor.

Extraer estructura de una tabla temporal CTE

Obtiene las columnas (estructura) de una tabla temporal de un CTE

SELECT column_ordinal, name, system_type_name, max_length, precision, scale, collation_name, is_nullable

FROM sys.dm_exec_describe_first_result_set

(

N'

WITH RESERVAS

AS

(

 SELECT             *

 FROM        Hechos.OfertasServiciosConexos

 WHERE       Fecha               = ''2020-05-07''

 AND         ClaveGenerador      = ''07   CIP-U01''

)

SELECT       *

FROM         RESERVAS

'

, NULL, NULL);

FILAS EN UNA SOLA COLUMNA, SQL-T

WITH ORDERS

 AS

 (

 select OrderId = 1,ProductId = 100

 union select 1,158

 union select 1,234

 union select 2,125

 union select 3,105

 union select 3,101

 union select 3,212

 union select 3,250

 )

 select distinct

       orderid

   ,REPLACE(LTRIM(REPLACE((  SELECT ' ' + CAST(ProductId as varchar)

       FROM ORDERS d

       WHERE d.OrderId = o.OrderId

       FOR XML PATH('')

   ),'&#x20;','')),' ', ', ') as Products

 from ORDERS o

RESULTADO:

PIVOT DINÁMICO USANDO CTE, WITH

-- ===================================================================

-- Autor:                 MANUEL OMAR OLGUÍN HERNÁNDEZ

-- Fecha:                 2020 MAYO 8

-- Versión:                1.0

-- Requerimiento:   PIVOT TABLE FORMED USING XML

-- Descripcion:            PIVOT TABLE sin necesidad de especificar explicitamente los nombres de las columnas

-- ================================================================

Select ID = 1, 'Tom' as Name ,'Bombadill' as Surname ,99999 as Age ,'Withywindle' as Address

UNION ALL

Select ID = 2, 'OMAR' as Name ,'OLGUIN' as Surname ,40 as Age ,'MEXICO' as Address

;with SampleCTE

as

(

Select ID = 1, 'Tom' as Name ,'Bombadill' as Surname ,99999 as Age ,'Withywindle' as Address

UNION ALL

Select ID = 2, 'OMAR' as Name ,'OLGUIN' as Surname ,40 as Age ,'MEXICO' as Address

)

Select A.ID, c.*

From SampleCTE A

Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)

Cross Apply (

                    Select       Item = a.value('local-name(.)','varchar(100)') ,Value = a.value('.','varchar(max)')

                    From         B.XMLData.nodes('/row') as C1(n)

                    Cross Apply C1.n.nodes('./@*') as C2(a)

                    Where a.value('local-name(.)','varchar(100)') not in ('ID','ExcludeOtherCol')

                    ) C