Creating a custom column

Question

Post reply

Creating a custom column

npatel 17252

SSC-Addicted

Points: 475
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 -

024 Swanson, Ron rswanson@tv.com

026 Donaughy, Jack jdonaughy@tv.com

028 Scott, Michael smichael@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 -

024 Swanson, Ron rswanson@tv.com a

024 Swanson, Ron rswanson@tv.com b

024 Swanson, Ron rswanson@tv.com c

024 Swanson, Ron rswanson@tv.com d

024 Swanson, Ron rswanson@tv.com e

026 Donaughy, Jack jdonaughy@tv.com a

026 Donaughy, Jack jdonaughy@tv.com b

026 Donaughy, Jack jdonaughy@tv.com c

026 Donaughy, Jack jdonaughy@tv.com d

026 Donaughy, Jack jdonaughy@tv.com e

028 Scott, Michael smichael@tv.com a

028 Scott, Michael smichael@tv.com b

028 Scott, Michael smichael@tv.com c

028 Scott, Michael smichael@tv.com d

028 Scott, Michael smichael@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: 183686 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 SSC-Addicted Points: 475 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: 183686 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 SSC-Addicted Points: 475 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.