locked
Is there anyway to do this? One table two columns, pkeys - pic attached RRS feed

  • Question

  • Hi,

    I have a table  that has two fields (OrgCode and Earncode).

    Both of these fields are pkeys (not designed by me).

    I am working in .NET and trying to display a grid that shows only the unique Orgcodes with thier corresponding Earncodes.

    For example this is how the table is:

    I'd like for it to only show ESLLAN once and then the corresponding Earncodes for each Orgcode.

    How can I do that if both fields are pkeys?


    qeqw

    Wednesday, November 14, 2012 5:38 PM

Answers

  • create a CTE that uses these columns plus a ROW_NUMBER() colum partitioned by org2Code and ordered by earnCode and then conditionally select the orgCode for the output select; for example:

    declare @test table(org2Code varchar(9), earnCode varchar(4));
    insert into @test
    select 'ESLBUC', '0001' union all
    select 'ESLBUC', '0030' union all
    select 'ESLBUC', '0096' union all
    select 'ESLBUC', '0121' union all
    select 'ESLBUC', '0123' union all
    select 'ESLBUC', '0151' union all
    select 'ESLLAN', '0001' union all
    select 'ESLLAN', '0002' union all
    select 'ESLLAN', '0003' union all
    select 'ESLLAN', '0004'
    ;
    ;with cte as
    ( select
        org2Code,
    	earnCode,
    	row_Number() over
    	( partition by org2Code
    	  order by earnCode
    	) as Rn
      from @test
    )
    select
      case when Rn=1 then org2Code else ''
      end as org2Code,
      earnCode
    from cte;
    /* -------- Output: --------
    --------- --------
    ESLBUC    0001
              0030
              0096
              0121
              0123
              0151
    ESLLAN    0001
              0002
              0003
              0004
    */

    Also, please note that it might be better to do this kind of work on the front end of the application rather than in the back end.

    • Edited by Kent Waldrop Wednesday, November 14, 2012 5:59 PM
    • Proposed as answer by Naomi N Thursday, November 15, 2012 4:42 AM
    • Marked as answer by Naomi N Sunday, November 18, 2012 2:53 AM
    Wednesday, November 14, 2012 5:52 PM
  • Yes, don't do it at all.  This was just meant as an example of how to do it not as a final product.  Replace table name @test with your actual table name and you should be just fine.

    :)


    • Edited by Kent Waldrop Wednesday, November 14, 2012 6:11 PM
    • Marked as answer by Kent Waldrop Monday, December 3, 2012 3:17 PM
    Wednesday, November 14, 2012 6:10 PM

All replies

  • create a CTE that uses these columns plus a ROW_NUMBER() colum partitioned by org2Code and ordered by earnCode and then conditionally select the orgCode for the output select; for example:

    declare @test table(org2Code varchar(9), earnCode varchar(4));
    insert into @test
    select 'ESLBUC', '0001' union all
    select 'ESLBUC', '0030' union all
    select 'ESLBUC', '0096' union all
    select 'ESLBUC', '0121' union all
    select 'ESLBUC', '0123' union all
    select 'ESLBUC', '0151' union all
    select 'ESLLAN', '0001' union all
    select 'ESLLAN', '0002' union all
    select 'ESLLAN', '0003' union all
    select 'ESLLAN', '0004'
    ;
    ;with cte as
    ( select
        org2Code,
    	earnCode,
    	row_Number() over
    	( partition by org2Code
    	  order by earnCode
    	) as Rn
      from @test
    )
    select
      case when Rn=1 then org2Code else ''
      end as org2Code,
      earnCode
    from cte;
    /* -------- Output: --------
    --------- --------
    ESLBUC    0001
              0030
              0096
              0121
              0123
              0151
    ESLLAN    0001
              0002
              0003
              0004
    */

    Also, please note that it might be better to do this kind of work on the front end of the application rather than in the back end.

    • Edited by Kent Waldrop Wednesday, November 14, 2012 5:59 PM
    • Proposed as answer by Naomi N Thursday, November 15, 2012 4:42 AM
    • Marked as answer by Naomi N Sunday, November 18, 2012 2:53 AM
    Wednesday, November 14, 2012 5:52 PM
  • Thanks Kent.

    Is there a way that I won't have to pre=populate the @test table:

    insert into @test
    select 'ESLBUC', '0001' union all
    select 'ESLBUC', '0030' union all
    select 'ESLBUC', '0096' union all
    select 'ESLBUC', '0121' union all
    select 'ESLBUC', '0123' union all
    select 'ESLBUC', '0151' union all
    select 'ESLLAN', '0001' union all
    select 'ESLLAN', '0002' union all
    select 'ESLLAN', '0003' union all
    select 'ESLLAN', '0004'
    ;
    There are six thousand items in this table, and users adding more..

    qeqw

    Wednesday, November 14, 2012 6:05 PM
  • Yes, don't do it at all.  This was just meant as an example of how to do it not as a final product.  Replace table name @test with your actual table name and you should be just fine.

    :)


    • Edited by Kent Waldrop Wednesday, November 14, 2012 6:11 PM
    • Marked as answer by Kent Waldrop Monday, December 3, 2012 3:17 PM
    Wednesday, November 14, 2012 6:10 PM
  • Awesome..thanks Kent!

    qeqw

    Thursday, November 15, 2012 2:22 PM