Recode existing data to hide information but provide the same patern

Question

Post reply

Recode existing data to hide information but provide the same patern

adrian.buzgau

Old Hand

Points: 355
More actions
February 5, 2012 at 8:56 pm

#256191

Hi,
I have a table called tblInvoices where any the letters represent the same value, for example letter a is the same value everywhere, letter b is the same value everywhere etc. Therefore I have this table below:
InvoiceID, ClientID, ProviderID
100 a e
200 a f
300 b e
400 b f
500 c g
I would like to provide the same data but hide the actual numbers within one SQL statement. This is the output I need:
InvoiceID, ClientID, ProviderID
1 1 1
2 1 2
3 2 1
4 2 2
5 3 3
I know the first part where InvoiceID is simply the rownumber of the dataset but I cannot work out the ClientID and the ProviderID.
SELECT ROW_NUMBER() OVER(order by InvoiceID) AS 'InvoiceID',
SELECT ROW_NUMBER() OVER(order by ClientID) AS 'ClientID',
SELECT ROW_NUMBER() OVER(order by ProviderID) AS 'ProviderID',
FROM tblInvoices
This sql statement gives me incorrect numbers for ClientID and ProviderID. Any ideas what I need to get the desired output?
Regards
AB

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

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

Manjuke SSChasing Mays Points: 629 More actions · Answer 1

Hi,

Please provide the following information in order to provide a better solution.

1. Does the value which represent the letter is same for the column only ?

2. According to the Sample you have provided 'a' and 'e' has the same value. Is it like that ?

And if you can provide with some real sample data it would be really nice.. 🙂

--------
Manjuke
http://www.manjuke.com

adrian.buzgau Old Hand Points: 355 More actions · Answer 2

manjuke (2/5/2012)
Hi,
Please provide the following information in order to provide a better solution.
1. Does the value which represent the letter is same for the column only ?
Yes values are unique for the same column. so letter a is the same for the one column.
2. According to the Sample you have provided 'a' and 'e' has the same value. Is it like that ?
yes, within the same column the number can start from 1 and keep going
but when you look at the next column the numbers can start from 1 and keep going also provided that the same number is coded the same within the same column.
And if you can provide with some real sample data it would be really nice.. 🙂

Manjuke SSChasing Mays Points: 629 More actions · Answer 3

Hi,

You can use the dense_rank to get the desired results. Please check the following sample..

--===== Create Sample Data ======

declare @sample as table(

InvoiceIDint,

ClientIDvarchar(1),

ProviderID varchar(1)

)

insert into @sample (InvoiceID, ClientID, ProviderID)

select 100,'a','e' union

select 200,'a','f' union

select 300,'b','e' union

select 400,'b','f' union

select 500,'c','g'

--===== Logic =====

;with cte_client as (

select InvoiceID,dense_rank() over (order by ClientID) as ClientID from @sample

)

, cte_provider as (

select InvoiceID,dense_rank() over (order by ProviderID) as ProviderID from @sample

)

select data.InvoiceID, cte_client.ClientID, cte_provider.ProviderID from @sample as data

join cte_client on data.InvoiceID = cte_client.InvoiceID

join cte_provider on data.InvoiceID = cte_provider.InvoiceID

Hope it'll solve your issue.. 🙂

--------
Manjuke
http://www.manjuke.com

adrian.buzgau Old Hand Points: 355 More actions · Answer 4

Good work that's precisely what I needed.

Thanks a lot.