StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Why not do this flipped sideways so that you don't have to use unions, you just have to scan the data sideways:</p> <pre><code>SELECT [Year] = @LtYear, [Sum of Total Salary] = s.s, [Average Salary] = s.a, [Employee Name] = e.first_name + ' ' + e.last_name, [Work Experience] = CONVERT(VARCHAR(12), e.work_Exp FROM employee_data.dbo.employee AS e CROSS JOIN ( SELECT SUM(Total_Salary), AVG(Total_Salary) FROM employee_data.dbo.employee WHERE [Year] = @LtYear ) AS s(s,a) WHERE e.[Year] = @LtYear AND e.First_Name = @first_name AND e.Last_Name = @last_name; </code></pre> <p>Not sure if you really meant to hard-code <code>'first'</code> and <code>'last'</code> but I suspect those would be better as parameters, especially if you're going to reference them multiple times.</p> <p>Given that your requirements are driven by Excel, maybe try:</p> <pre><code>DECLARE @y CHAR(4); SET @y = CONVERT(CHAR(4), @LtYear); SELECT col1 = 'Sum of total salary for ' + @y, col2 = CONVERT(NVARCHAR(255), sum(Total_Salary) as Salary) From employee_data.dbo.employee where [year] = @LtYear UNION ALL SELECT 'Overall Average Salary ' + @y, CONVERT(NVARCHAR(255), avg(Total_Salary)) From employee_data.dbo.employee where [year] = @LtYear UNION ALL SELECT 'Employee Name - 1', CONVERT(NVARCHAR(255), first_name+' '+last_name) From employee_data.dbo.employee where [year] = @LtYear and First_Name = 'first' and Last_Name = 'last' UNION ALL SELECT 'Individual Employee Experience ' + @y, CONVERT(NVARCHAR(255), work_Exp) From employee_data.dbo.employee where [year] = @LtYear and First_Name = 'first' and Last_Name = 'last'; </code></pre> <p>Notice a few changes:</p> <ul> <li>dropped all of the aliases - they're ignored after the first statement in the union anyway.</li> <li>used explicit converts against the second column to a hopefully compatible data type.</li> <li>properly enclosed all keywords (e.g. <code>year</code>) in square brackets. Removed those that aren't necessary - they only serve to make the query harder to read.</li> <li>created a string version of the year parameter so that it could be appended to the first column without having to perform any conversion.</li> </ul> <p>Now, you've dumbed down the question, and there are more queries that we can't see. But I still think a better approach would be to ditch the unions against the base table and perform it this way instead (especially if you have 10 or 15 more queries against the same table). This isn't necessarily going to perform any differently, but it will be a <em>lot</em> easier to maintain.</p> <pre><code>;WITH x AS ( SELECT y = CONVERT(CHAR(4), @LtYear), [ss] = CONVERT(NVARCHAR(255), s.s), [as] = CONVERT(NVARCHAR(255), s.a), [en] = CONVERT(NVARCHAR(255), e.first_name + ' ' + e.last_name), [we] = CONVERT(NVARCHAR(255), e.work_Exp) FROM employee_data.dbo.employee AS e CROSS JOIN ( SELECT SUM(Total_Salary), AVG(Total_Salary) FROM employee_data.dbo.employee WHERE [Year] = @LtYear ) AS s(s,a) WHERE e.[Year] = @LtYear AND e.First_Name = @first_name AND e.Last_Name = @last_name ) SELECT 'Sum of total salary for ' + y, [ss] FROM x UNION ALL SELECT 'Overall average salary for ' + y, [as] FROM x UNION ALL SELECT 'Employee Name - 1', [en] FROM x UNION ALL SELECT 'Individual Employee Experience ' + y, [we] FROM x; </code></pre> <p>Further to that, I think your approach is very troublesome in the first place. You're really performing data validation by cobbling these queries together and manually pasting the results into Excel? Surely there is a more automated way to do this, and instead of trying to tailor an unwieldy SQL query to conform to your process, you should improve the process...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload