Post reply

Recode existing data to hide information but provide the same patern

  • 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

  • February 5, 2012 at 11:08 pm

    #1442897

    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

  • February 5, 2012 at 11:17 pm

    #1442898

    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.. 🙂

  • February 6, 2012 at 1:11 am

    #1442923

    Hi,

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

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

    declare @sample as table(

    InvoiceID int,

    ClientID varchar(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

  • February 6, 2012 at 3:22 pm

    #1443485

    Good work that's precisely what I needed.

    Thanks a lot.

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

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