Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
duplicate records based on two columns in a table

Answered duplicate records based on two columns in a table

  • Friday, March 01, 2013 5:05 PM
     
     
    Sign In to Vote
    0

    hi all,

    i have table Temp with values as follows and i have written a row_number() to get the row count for both colums

    Row_number() over (partition by col1 order by col1 ) as col1count  from Temp

    Row_number() over (partition by col1 order by col1 ) as col2count  from Temp

    col1 col1count col2 col2count    Type

    aa 1 ee 1 A

    bb 1 aa 1 A

    bb 2 ee 2 A

    cc 1 ff 1 A

    dd 1 dd 1 A

    i want all the distinct values for Type A( if i group by Type) the results should be as follows

    Type  Distinct

    A aa

    A bb

    A cc

    A dd

    A ee

    A ff

    please let me now how to write a sql query to achieve this and i Appreciate your help

    thanks


     

All Replies

  • Friday, March 01, 2013 5:12 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0
     
    Declare @myTable Table(c1 varchar(10), c1Count int, c2 varchar(10), c2Count int,  type varchar(10))
 Insert into @myTable values ('aa', 1,  'ee', 1, 'A'),
 ('bb', 1,  'aa', 1, 'A'),
 ('bb', 2,  'ee', 2, 'A'),
 ('cc', 1,  'ff', 1, 'A'),
('dd', 1,  'dd', 1, 'A')
 
SELECT c1,type from @myTable
UNION
SELECT c2,type from @myTable

     
  • Friday, March 01, 2013 6:07 PM
     
     
    Sign In to Vote
    0

    Thank you very much for that Jingyang Li. that kind of solves my problem but here is the complicated part

    the actual table is as follows

    col1 col1name col2  col2name  amount

    aa p122 ee p122 00

    bb p122 aa p122 150

    bb p122 ee p122 150

    cc p122 ff p122 00

    dd p122 dd p122 00

    i need the result to be like as follows

    aa col1name.p122 150

    bb col1name.p122 150

    cc col1name.p122 150

    dd col1name.p122 150

    ee col2name.p122 150

    ff col2name.p122 150

    could you please help me out with the query

    thanks a lot 

     
  • Friday, March 01, 2013 7:30 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0
     
    Declare @myTable Table(col1 varchar(10), col1name varchar(10),col2 varchar(10), col2name varchar(10),  amount int)
 Insert into @myTable values ('aa', 'p122',  'ee', 'p122', 0),
 ('bb', 'p122',  'aa', 'p122', 150),
 ('bb', 'p122',  'ee', 'p122', 150),
 ('cc', 'p122',  'ff', 'p122', 0),
('dd', 'p122',  'dd', 'p122', 0)
 
 ;WIth mycte as
 (
SELECT col,colNname+'.'+col1name as colNname,  row_number() over(partition by col order by colNname) rn , max(amount) Over() as amount

from @myTable
cross apply(values(col1,'col1name'),(col2,'col2name') ) d(col,colNname)
)
SELECT col,colNName,Amount from mycte WHERE rn=1

--Result
/*
col	colNName	Amount
aa	col1name.p122	150
bb	col1name.p122	150
cc	col1name.p122	150
dd	col1name.p122	150
ee	col2name.p122	150
ff	col2name.p122	150


*/

    • Marked As Answer by msbito Friday, March 01, 2013 8:50 PM
    •  
     
  • Friday, March 01, 2013 8:51 PM
     
     
    Sign In to Vote
    0

    thanks heaps for that mate. i reckon this is gonna solve my issue