1

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?

3
  • 2
    What kind of SQL? Do you only ever sell 2 items to people? Commented Sep 20, 2012 at 9:59
  • SQL Server 2008. No, the problem is that sometimes, the are more than 2 people with the same account number. One instance I found was 8 people with the same account number. Commented Sep 20, 2012 at 10:17
  • @user1668123 can you provide some sample data for both tables? Or even better create a SQL Fiddle with a working data model Commented Sep 20, 2012 at 10:37

2 Answers 2

1

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

Sign up to request clarification or add additional context in comments.

3 Comments

I tried replicating what you did in the query above, but I am very lost when it comes to pivot/unpivot. Is there any easier way to do this?
@user1668123 I cannot see of another way to do this if you have an unknown number of columns. If you have a specific question about how the pivot/unpivot works, please ask and I will clarify it.
@user1668123 I do not, however you can always use SQL Fiddle to create your data model.
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

4 Comments

I tried that, but I get unnecessary duplicate results from that query.
It is because you have more than one item for one person. So you should decide what to do in this case.
If there is more than one item for one person, then I need to return the first item that was purchased, based on purchase date.
Do you have an email address that I could use to send the actual query to? Its a lot more complex than the example I gave here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.