Advertisement
HC_

Untitled

HC_
Mar 13th, 2019
86
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.44 KB | None | 0 0
  1. -- =============================================
  2. -- Author: OSCAR NAVARRO
  3. -- Create date: <Create Date,,>
  4. -- Description: <Description,,>
  5. -- =============================================
  6. ALTER PROCEDURE [dbo].[SP_CONTROL_INICIATIVA](@IDCLIENTE BIGINT, @IDINICIATIVA BIGINT)
  7. AS
  8. BEGIN
  9. DECLARE @ATTR_CABECERA VARCHAR(MAX) = '',@SQL VARCHAR(MAX), @ATTR_PVT VARCHAR(MAX);
  10. SET @ATTR_CABECERA = (SELECT STUFF((SELECT ' ISNULL(SUM("'+X.NOMBRE_CONTROL+'"),0) "' + X.NOMBRE_CONTROL +'", ' FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = @IDCLIENTE ORDER BY X.ORDEN FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''));
  11. SET @ATTR_PVT = (SELECT STUFF((SELECT ' "'+X.NOMBRE_CONTROL+'", ' FROM MS_CONFIG_CLIENTE X WHERE X.ID_CLIENTE = @IDCLIENTE ORDER BY X.ORDEN FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, ''));
  12. SET @SQL = 'SELECT *
  13. FROM (
  14. SELECT
  15. B.NOMBRE_INICIATIVA
  16. ,A.NOMBRE_CONTROL
  17. ,C.ID
  18. FROM MS_CONFIG_CLIENTE A
  19. INNER JOIN MS_CONTROL_CLIENTE B ON A.ID_CLIENTE = B.ID_CLIENTE
  20. INNER JOIN MS_CONTROL_CLIENTE_VALUES C ON A.ID = C.ID_CONFIG AND B.ID = C.ID_INICIATIVA
  21. WHERE A.ID_CLIENTE = '+ CAST(@IDCLIENTE AS VARCHAR(20)) + '
  22. AND B.ID = '+ CAST(@IDINICIATIVA AS VARCHAR(20)) + '
  23.  
  24. ) AS DATOS PIVOT
  25. (
  26. SUM(ID)
  27. FOR NOMBRE_CONTROL IN (#CONTROLES#)
  28. )AS PVT_TABLE';
  29.  
  30. SET @ATTR_PVT = SUBSTRING(@ATTR_PVT,0,LEN(@ATTR_PVT))
  31. SET @SQL = REPLACE(@SQL,'#CONTROLES#', @ATTR_PVT);
  32. EXEC(@SQL);
  33.  
  34. END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement