0

I'm using SQL Server 2014, and working with the following table which has repeating values:

COLUMN_NAME COLUMN_POS
SAL         3
SAL         3
EMP         1
CITY        2
NAME        4
JOB_TITLE   5
JOB_TITLE   5
EMP         1
CITY        2
NAME        4

I need to output a distinct list of the COLUMN_NAME values, sorted by the COLUMN_POS without the COLUMN_POS being included in the query results.

I'm using this later on in the query for the purposes of Pivoting columns dynamically. Desired output:

COLUMN_NAME 
EMP         
CITY        
SAL         
NAME        
JOB_TITLE

I've tried:

(1)

SELECT DISTINCT COLUMN_NAME FROM #TEMP  ORDER BY COLUMN_POS ;

(2)

SELECT  COLUMN_NAME FROM #TEMP  GROUP BY COLUMN_POS ORDER BY COLUMN_POS ;

(3)

WITH CTE AS (SELECT  COLUMN_NAME FROM #TEMP  GROUP BY COLUMN_POS ORDER BY COLUMN_POS ) 
 SELECT  DISTINCT COLUMN_NAME FROM #TEMP

Which all return errors, indicating COLUMN_POS is not in the SELECT list, or The ORDER BY clause is invalid in views,

Sample values:

CREATE TABLE #temp (
COLUMN_NAME NVARCHAR(MAX)
,COLUMN_POS int 
) 

INSERT INTO #TEMP
(COLUMN_NAME,COLUMN_POS) 
values 

('SAL',3 ),
('EMP', 1),
('CITY', 2),
('NAME', 4),
('JOB_TITLE', 5),
('CITY', 2),
('NAME', 4),
('NAME', 4)
Dale K
  • 25,246
  • 15
  • 42
  • 71
Depth of Field
  • 307
  • 2
  • 16
  • A view is an unordered set, like a table. `ORDER BY` is for display purposes and goes on the outer query. You can always select it out of the CTE and not select it in the outer query. As for the first two, if you have grouped by one column, you logically no longer have any of the other columns. Ergo you need to group by `COLUMN_NAME` also – Charlieface Jan 21 '21 at 03:47
  • Does this answer your question? [GROUP BY / aggregate function confusion in SQL](https://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql) – Charlieface Jan 21 '21 at 03:53

1 Answers1

2

Assuming you have the same position for every distinct name (which your sample data indicates) then whats wrong with:

SELECT COLUMN_NAME
FROM #Temp
GROUP BY COLUMN_NAME, COLUMN_POS
ORDER BY COLUMN_POS;
Dale K
  • 25,246
  • 15
  • 42
  • 71