Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- =============================================
- -- Author: OSCAR NAVARRO
- -- Create date: <Create Date,,>
- -- Description: <Description,,>
- -- =============================================
- ALTER PROCEDURE [dbo].[SP_CONTROL_INICIATIVA](@IDCLIENTE BIGINT, @IDINICIATIVA BIGINT)
- AS
- BEGIN
- DECLARE @ATTR_CABECERA VARCHAR(MAX) = '',@SQL VARCHAR(MAX), @ATTR_PVT VARCHAR(MAX);
- 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, ''));
- 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, ''));
- SET @SQL = 'SELECT *
- FROM (
- SELECT
- B.NOMBRE_INICIATIVA
- ,A.NOMBRE_CONTROL
- ,C.ID
- FROM MS_CONFIG_CLIENTE A
- INNER JOIN MS_CONTROL_CLIENTE B ON A.ID_CLIENTE = B.ID_CLIENTE
- INNER JOIN MS_CONTROL_CLIENTE_VALUES C ON A.ID = C.ID_CONFIG AND B.ID = C.ID_INICIATIVA
- WHERE A.ID_CLIENTE = '+ CAST(@IDCLIENTE AS VARCHAR(20)) + '
- AND B.ID = '+ CAST(@IDINICIATIVA AS VARCHAR(20)) + '
- ) AS DATOS PIVOT
- (
- SUM(ID)
- FOR NOMBRE_CONTROL IN (#CONTROLES#)
- )AS PVT_TABLE';
- SET @ATTR_PVT = SUBSTRING(@ATTR_PVT,0,LEN(@ATTR_PVT))
- SET @SQL = REPLACE(@SQL,'#CONTROLES#', @ATTR_PVT);
- EXEC(@SQL);
- END
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement