Creating a custom column

Question

Post reply

Creating a custom column

npatel 17252

Mr or Mrs. 500

Points: 525
More actions
May 14, 2014 at 4:13 pm

#307947

Hello I have question regarding creating a custom column that will put a list of 5 values for each unique value(or in this case Employee).
So I have a Employee table were I pull a list of all active employees -
Example -
024Swanson, Ronrswanson@tv.com
026Donaughy, Jackjdonaughy@tv.com
028Scott, Michaelsmichael@tv.com
What I want to do is add a column that has 5 values and create a row for each value
I want it too look like this -
024Swanson, Ronrswanson@tv.com a
024Swanson, Ronrswanson@tv.com b
024Swanson, Ronrswanson@tv.com c
024Swanson, Ronrswanson@tv.com d
024Swanson, Ronrswanson@tv.com e
026Donaughy, Jackjdonaughy@tv.com a
026Donaughy, Jackjdonaughy@tv.com b
026Donaughy, Jackjdonaughy@tv.com c
026Donaughy, Jackjdonaughy@tv.com d
026Donaughy, Jackjdonaughy@tv.com e
028Scott, Michaelsmichael@tv.com a
028Scott, Michaelsmichael@tv.com b
028Scott, Michaelsmichael@tv.com c
028Scott, Michaelsmichael@tv.com d
028Scott, Michaelsmichael@tv.com e
Currently all my query looks like is this -
SELECT EmpID, LastFirst, Email
FROM dbo.EmpList
WHERE (Active = 1)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

npatel 17252 (5/14/2014)
What I want to do is add a column that has 5 values and create a row for each value

Why would you do such a thing?

This would denormalize the table and that will only give headaches. Instead of repeating rows just to add a single column, you should create a new table to store those values related by the PK of your original table.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

npatel 17252 Mr or Mrs. 500 Points: 525 More actions · Answer 2

Its because I need to create a report that sees what employees have put in time or not for the last week. And unfortunately we have 2 different systems for Time Entry so what I want to do is to create a table that has the dates of last week(M-F) and there name and email. And then insert the values of the 2 different databases into the table.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

That's the beauty of relational databases, you don't need to store the repeated values. You just need to use the joins wisely.

CREATE TABLE #Employees(

emp__id char(3),

emp_name varchar(100),

emp_email varchar(100))

INSERT #Employees

SELECT '024', 'Swanson, Ron', 'rswanson@tv.com' UNION ALL

SELECT '026', 'Donaughy, Jack', 'jdonaughy@tv.com' UNION ALL

SELECT '028', 'Scott, Michael', 'smichael@tv.com'

CREATE TABLE #Days(

day_no int)

INSERT #Days

SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5

SELECT *

FROM #Employees

CROSS JOIN #Days

DROP TABLE #Employees

DROP TABLE #Days

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

npatel 17252 Mr or Mrs. 500 Points: 525 More actions · Answer 4

Luis Cazares (5/14/2014)
That's the beauty of relational databases, you don't need to store the repeated values. You just need to use the joins wisely.
CREATE TABLE #Employees(
emp__id char(3),
emp_name varchar(100),
emp_email varchar(100))
INSERT #Employees
SELECT '024', 'Swanson, Ron', 'rswanson@tv.com' UNION ALL
SELECT '026', 'Donaughy, Jack', 'jdonaughy@tv.com' UNION ALL
SELECT '028', 'Scott, Michael', 'smichael@tv.com'
CREATE TABLE #Days(
day_no int)
INSERT #Days
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
SELECT *
FROM #Employees
CROSS JOIN #Days
DROP TABLE #Employees
DROP TABLE #Days

Okay So I ended up putting that in a Stored Procedure, what I need to do next is my other query is a view that pulls from the same DB and I need it to insert the Time into that temptable were the EmployeeName and Date match from both.