Solved

Need to create new output column

Posted on 2014-03-26
3
161 Views
Last Modified: 2014-03-26
I need to create a new output column in a query that will be used as a index.  I    can not use the existing field for security reasons (Col A)  So I must generate a new column (Col B)
Lets say that Col A is a Social Security Number and a call center person can not see it so we will create a new field Col B.  Later internally we will re associate the records.

Col A exists in the table Col B does not.  I can not modify the table

How can I create a query to do this.  A stored procedure will be ok as long as it produces a result set that I can see and export

Example

TableX

Col A           Col B
163777439     1
163777439     1
163777439     1
163777439     1
163777439     1
163777439     1
718108418     2
718108418     2
718108418     2
918233645     3
918233645     3
418323022     4
418323022     4
418323022     4
418323022     4
418434193     5
418434193     5
418434193     5
418434193     5
0
Comment
Question by:charlesbaldo
  • 2
3 Comments
 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
Comment Utility
So ... is the point of the 1, 2, 3, 4, 5 numbers in Column B just to have a 'placeholder' to Column A?

If so, and you want it in a query, meaning the 1, 2, 3, 4, 5 won't be stored in a table, you can just use a RANK statement, sorted by whatever floats your boat...

SELECT ColA, RANK() OVER (ORDER BY ColA) as ColB
FROM YourTable
0
 

Author Closing Comment

by:charlesbaldo
Comment Utility
Thank You
0
 
LVL 65

Expert Comment

by:Jim Horn
Comment Utility
Thanks for the grade.  Good luck with your project.  -Jim
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now