Ordering dynamic columns by a column that isn't selected

I have the following Stored Procedure, based on a question asked here:
CREATE PROCEDURE [dbo].[usp_MapaSalarios]
	@Emp smallint,
	@AnoMes numeric(6,0),
	@CC char(1)

AS
BEGIN

DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT DISTINCT  ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT DISTINCT  ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  psa_num_empregado ''Nº'', psa_nome_abreviado Nome, cat_designacao Categoria, REPLACE(CONVERT(char(10),psa_dt_admissao_empresa,103),''/'',''-'') ''Admissão'', Dem ''Demissão'', cvc_valor_venc Vencimento, Di Dias, ' + @Columns + ' 
FROM 
(SELECT   a.psa_num_empregado, a.psa_nome_abreviado, a.cat_designacao, a.psa_dt_admissao_empresa, '''' Dem, a.cvc_valor_venc, '''' Di, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento 
FROM uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=' + CONVERT(VARCHAR(6),@AnoMes) + ' INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=''' + @CC + '''
WHERE psa_emp_empresa=' + CONVERT(VARCHAR(3),@Emp) + ' AND Regime = ''' + @CC + ''') AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_num_empregado ;'

EXECUTE sp_ExecuteSQL @Query

END

Open in new window

This is meant to create dynamic columns based on the @Emp and @AnoMes variables (@CC is irrelevant for this). Now, this works fine, except for one small detail: the columns appear in alphabetical order. But there is a field in tblVencimentos called intOrdem (smallint) that defines the order in which items should appear.
I've tried adding an ORDER BY intOrdem clause after the WHERE of the subqueries to set @Columns and @Columnnames but it complains that an order by item has to appear in the select clause.

Can someone fix this, please?
LVL 18
CluskittAsked:
Who is Participating?
 
JestersGrindCommented:
I updated the code to order by intOrdem.

Greg


CREATE PROCEDURE [dbo].[usp_MapaSalarios]
	@Emp smallint,
	@AnoMes numeric(6,0),
	@CC char(1)

AS
BEGIN

DECLARE @Columnnames NVARCHAR(MAX), @Columns NVARCHAR(MAX), @Query NVARCHAR(MAX)

SET @Columns = (SELECT STUFF(((SELECT ', ISNULL('+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc)) + ', ''0'') AS ' + 
	QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
GROUP BY c.strCodigoVenc, c.intOrdem
ORDER BY c.intOrdem
                  FOR XML PATH('')
                  )),1,1,''))
                 
SET @Columnnames = (SELECT STUFF(((SELECT DISTINCT  ', '+QUOTENAME(CONVERT(VARCHAR,c.strCodigoVenc))
FROM   
	uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=@AnoMes INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=@CC
WHERE psa_emp_empresa=@Emp
                  FOR XML PATH('')
                  )),1,1,''))

SET @Query = 'SELECT  psa_num_empregado ''Nº'', psa_nome_abreviado Nome, cat_designacao Categoria, REPLACE(CONVERT(char(10),psa_dt_admissao_empresa,103),''/'',''-'') ''Admissão'', Dem ''Demissão'', cvc_valor_venc Vencimento, Di Dias, ' + @Columns + ' 
FROM 
(SELECT   a.psa_num_empregado, a.psa_nome_abreviado, a.cat_designacao, a.psa_dt_admissao_empresa, '''' Dem, a.cvc_valor_venc, '''' Di, c.strCodigoVenc, ISNULL(CONVERT(VARCHAR, b.dblValorVencimento), '''') dblValorVencimento 
FROM uv_Cadastro a INNER JOIN
	tblMapaSalarios b ON a.psa_emp_empresa = b.FK_ID_intEmpresa AND a.psa_num_empregado = b.FK_ID_intFuncionario AND b.intAnoMes=' + CONVERT(VARCHAR(6),@AnoMes) + ' INNER JOIN
	tblVencimentos c ON b.FK_ID_intVencimento = c.FK_ID_intVencimento AND strCC=''' + @CC + '''
WHERE psa_emp_empresa=' + CONVERT(VARCHAR(3),@Emp) + ' AND Regime = ''' + @CC + ''') AS p 
PIVOT 
(MAX(dblValorVencimento) FOR strCodigoVenc IN (' + @Columnnames + ')) AS v '
+'ORDER BY psa_num_empregado ;'

EXECUTE sp_ExecuteSQL @Query

END

Open in new window

0
 
CluskittAuthor Commented:
Works like a charm. Thanks!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.