April 12, 2016 at 10:18 pm
I am trying to create an employee table that lists every employee in a column in a stored procedure. The issue is that the number of employees could change from run to run, so I am trying to figure out how I can do this.
Here is some sample data:
IF OBJECT_ID('EmpCodes' ,'U') IS NOT NULL BEGIN DROP TABLE EmpCodes END
CREATE TABLE EmpCodes (EmpCode VARCHAR(10))
INSERT INTO EmpCodes SELECT 'D100'
INSERT INTO EmpCodes SELECT 'D101'
INSERT INTO EmpCodes SELECT 'D102'
INSERT INTO EmpCodes SELECT 'E100'
INSERT INTO EmpCodes SELECT 'E101'
INSERT INTO EmpCodes SELECT 'E102'
SELECT * FROM EmpCodes
In this example, the code to create the table would be this:
CREATE TABLE Employees (D100 NVARCHAR(10), D101 NVARCHAR(10), D102 NVARCHAR(10), E100 NVARCHAR(10), E101 NVARCHAR(10), E102 NVARCHAR(10))
However the next time the employee would look like this:
IF OBJECT_ID('EmpCodes' ,'U') IS NOT NULL BEGIN DROP TABLE EmpCodes END
CREATE TABLE EmpCodes (EmpCode VARCHAR(10))
INSERT INTO EmpCodes SELECT 'D100'
INSERT INTO EmpCodes SELECT 'D101'
INSERT INTO EmpCodes SELECT 'D102'
INSERT INTO EmpCodes SELECT 'E100'
INSERT INTO EmpCodes SELECT 'E101'
INSERT INTO EmpCodes SELECT 'E102'
INSERT INTO EmpCodes SELECT 'F100'
INSERT INTO EmpCodes SELECT 'F101'
INSERT INTO EmpCodes SELECT 'F102'
SELECT * FROM EmpCodes
Which would create this table:
CREATE TABLE Employees (D100 NVARCHAR(10), D101 NVARCHAR(10), D102 NVARCHAR(10), E100 NVARCHAR(10), E101 NVARCHAR(10), E102 NVARCHAR(10), F100 NVARCHAR(10), F101 NVARCHAR(10), F102 NVARCHAR(10))
Does anyone have any ideas as to the best way this could be done?
Thanks.
April 13, 2016 at 1:48 am
sdownen05 (4/12/2016)
Does anyone have any ideas as to the best way this could be done?
The best way is not to do this at all. Relational databases are designed to work for a fixed schema. They have no tools to handle changing schemas very well. If you struggle to create the table, how do you think you will query it?
My advise is to step back and reconsider what it is you actually need. A table is never a business requirement, it is a tool. You apparently think that this table will help you solve a business requirement. Perhaps you can share that requirement - there might be much better ways to handle it. For instance, if you need this for a report that has all the employees at the top, then look into pivoting the data in the front end or use a reporting tool that can do crosstabs on the spot. If you don't have a front end and cannot use a reporting tool, then google "dynamic crosstab".
April 13, 2016 at 7:39 am
If this is for reporting purposes, your best bet is some sort of PIVOT results instead of the dynamic table approach.
In SSRS, the matrix report handles this sort of thing quite well. If you're trying to do it via SQL, you'll need to PIVOT the results probably.
April 13, 2016 at 8:54 am
Manic Star (4/13/2016)
If this is for reporting purposes, your best bet is some sort of PIVOT results instead of the dynamic table approach.In SSRS, the matrix report handles this sort of thing quite well. If you're trying to do it via SQL, you'll need to PIVOT the results probably.
Agreed. And a CROSS TAB would be easier to make dynamic and would likely provide better performance, as well.
Can you tell us more about what you'll end up doing once the table you want is formed? Perhaps provide and example output?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply