Inconsistent new line behavior with VALUES clause

a.higginsa.higgins Posts: 90 Bronze 2
edited October 11, 2016 1:59PM in SQL Prompt
The settings for the VALUES clause does not seem to be correctly determining when to insert a new line, depending on the 'Commas' option within lists.

If I set Global --> Lists --> Commas --> Place commas before items, the two sets within my VALUES clause aren't separated by a new line: 
;WITH Departments AS
	(
		SELECT
			'DEPT1'			 AS Dept_1
		   ,'Department One' AS Dept_1_Desc
		   ,'DEPT2'			 AS Dept_2
		   ,'Department Two' AS Dept_2_Desc
	)
SELECT
	unpvt.Department
   ,unpvt.DepartmentDescription
   ,unpvt.DepartmentNumber
FROM
	Departments
	CROSS APPLY
	(
		VALUES
			(
				Departments.Dept_1
			   ,Departments.Dept_1_Desc
			   ,1
			), (                                                                                          -- this opening parentheses should be on a new line
				   Departments.Dept_2
				  ,Departments.Dept_2_Desc
				  ,2
			   )
	) unpvt (Department, DepartmentDescription, DepartmentNumber)

However, if I set Global --> Lists --> Commas --> Place commas after items, the parentheses within my VALUES clause are lined up correctly: 
;WITH Departments AS
	(
		SELECT
			'DEPT1'			 AS Dept_1,
			'Department One' AS Dept_1_Desc,
			'DEPT2'			 AS Dept_2,
			'Department Two' AS Dept_2_Desc
	)
SELECT
	unpvt.Department,
	unpvt.DepartmentDescription,
	unpvt.DepartmentNumber
FROM
	Departments
	CROSS APPLY
	(
		VALUES
			(
				Departments.Dept_1,
				Departments.Dept_1_Desc,
				1
			),
			(                                                                                               -- yay! This is on a new line now.
				Departments.Dept_2,
				Departments.Dept_2_Desc,
				2
			)
	) unpvt (Department, DepartmentDescription, DepartmentNumber)

Since I like to have my commas before items, can this be changed to correctly interpret the VALUES clause and add a new line between sets?
· Share on Twitter

Comments

Sign In or Register to comment.