?
Solved

Ordering dynamic columns by a column that isn't selected

Posted on 2011-10-20
2
Medium Priority
?
231 Views
Last Modified: 2012-05-12
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?
0
Comment
Question by:Cluskitt
2 Comments
 
LVL 21

Accepted Solution

by:
JestersGrind earned 2000 total points
ID: 36999506
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
 
LVL 18

Author Closing Comment

by:Cluskitt
ID: 36999528
Works like a charm. Thanks!!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question