SQL displaying duplicate values in tables

Question

I need to get a query that will display details of persons that have the same banking details, that have been captured in error.

I need to then display both the correct details as well as the incorrect details of these persons. For instance:

1stName   1stAccNo   1stItemSold    2ndName   2ndAccNo  2ndItemSold
Albert    2231432     Item1         John      2231432   Item3
Robert    6321009     Item11        Peter     6321009   Item3

Name and AccNo are on one table, and ItemSold are on a different table.

I have the following query:

select p1.Name As 1stName, p1.AccNo AS 1stAccNo, I.ItemSold AS 1stItemSold, p2.Name AS 2ndName, p2.AccNo AS 2ndAccNo, I.ItemSold As 2ndItemSold
FROM Persons p1 JOIN Persons p2 ON p1.AccNo = p2.AccNo 
INNER JOIN Items I on p.ItemID = I.ItemID

The problem is that 2nd ItemSold is not correct, because it is the same as the 1stItemSold. What do I do to change my query so that I may achieve the correct results?

Answer 1

You should join Items as a second table. Also If you need the different people you have to include where p1.id<>p2.id where ID is a primary key in the PERSONS table

select 
p1.Name As 1stName, p1.AccNo AS 1stAccNo, I1.ItemSold AS 1stItemSold, 
p2.Name AS 2ndName, p2.AccNo AS 2ndAccNo, I2.ItemSold As 2ndItemSold
FROM Persons p1 JOIN Persons p2 ON p1.AccNo = p2.AccNo 
INNER JOIN Items I1 on p1.ItemID = I1.ItemID
INNER JOIN Items I2 on p2.ItemID = I2.ItemID
where p1.id<>p2.id

PS: based on comment. Return the last ItemSold to avoid duplicates:

select 
p1.Name As 1stName, p1.AccNo AS 1stAccNo, I1.ItemSold AS 1stItemSold, 
p2.Name AS 2ndName, p2.AccNo AS 2ndAccNo, I2.ItemSold As 2ndItemSold
FROM Persons p1 JOIN Persons p2 ON p1.AccNo = p2.AccNo 
left JOIN 
   (select ItemID,ItemSold, 
           row_number() over (PARTITION BY ItemID order by DATE_SOLD DESC) as rn 
    from Items) I1 
         on (p1.ItemID = I1.ItemID) and (rn=1)
left JOIN 
   (select ItemID,ItemSold, 
           row_number() over (PARTITION BY ItemID order by DATE_SOLD DESC) as rn 
    from Items) I2 
         on (p2.ItemID = I2.ItemID) and (rn=1)

where p1.id<>p2.id

Answer 2

I might be misinterpreting your requirements but it sounds like you can use the UNPIVOT/PIVOT function to get the results you need.

If you know the number of columns to transform, then you can hard-code a static version of this.

select *
from
(
  select AccNo,
    val,
    col + cast(rn as varchar(10)) Col_Name
  from
  (
    select p1.accno,
      p1.name,
      p1.itemid,
      i1.id,
      i1.i_name,
      row_number() over(partition by accno order by name) rn
    from persons p1
    left join items i1
      on p1.itemid = i1.id
  ) x
  unpivot
  (
    val
    for col in (name, i_name)
  ) u
) x1
pivot
(
  max(val)
  for col_name in ([name1], [i_name1], [name2], [i_name2])
) p

see SQL Fiddle with Demo

But it sounds like you will have an unknown number of items to transform to columns, so you can use dynamic SQL and create a dynamic version of this:

DECLARE @colsUnPivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @colsUnPivot = stuff((select ','+C.name
         from sys.columns as C
         where (C.object_id = object_id('persons') 
                  or C.object_id = object_id('items'))
            and C.name like '%name%'
         for xml path('')), 1, 1, '')

select @colsPivot = STUFF((SELECT  ',' 
                      + quotename(c.name 
                         + cast(p.rn as varchar(10)))
                    from
                    (
                      select row_number() over(partition by accno order by name) rn
                      from persons
                    ) p
                     cross apply 
                      sys.columns as C
                   where (C.object_id = object_id('persons') 
                            or C.object_id = object_id('items'))
                      and C.name like '%name%'
                   group by c.name, p.rn
                   order by p.rn, c.name desc
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query 
  = 'select *
      from
      (
          select AccNo,
            val,
            col + cast(rn as varchar(10)) Col_Name
        from 
        (
          select p1.accno,
            p1.name,
            p1.itemid,
            i1.id,
            i1.i_name,
            row_number() over(partition by accno order by name) rn
          from persons p1
          left join items i1
            on p1.itemid = i1.id
        ) x
        unpivot
        (
          val
          for col in ('+ @colsunpivot +')
        ) u
      ) x1
      pivot
      (
        max(val)
        for col_name in ('+ @colsPivot + ')
      )p'

exec(@query)

see SQL Fiddle with Demo