Search code examples
sql-server-2012inner-join

How to combine multiple rows of varying amounts of data into one row in SQL Server 2012

I've seen the multiple entries similar to this one but can't find one that fits my specific needs so sorry if this is considered a repost.

Note; Using Sql Server 2012 and am just past beginner in level of knowledge of programming, so might still be missing something obvious.

I have a table called Results that outputs the following (With example data)

------------------------------------------------------------
UID | LotNum | SerNum | ResultValue| ResultCode | TestType |
------------------------------------------------------------
1   | Lot1   | 1234   | 10         |    1       | 1        |
------------------------------------------------------------
2   | Lot1  | 1234   | 5          |    1       | 1        |
------------------------------------------------------------
3   | Lot1  | 1234   | 2          |    2       | 1        |
------------------------------------------------------------
4   | Lot1  | 2345   | 5          |    1       | 1        |
------------------------------------------------------------
5   | Lot1  | 2345   | 2          |    2       | 1        |
------------------------------------------------------------
6   | Lot1  | 2345   | 2          |    2       | 1        |

The UID is autogenerated, the rest is entered and the maximum amount of tests per serial number is 3.

I'm trying to pull some of this data into a single row based off of the Serial Number so I can easily combine it with a separate table using a stored procedure. This new table looks like this

-----------------------------------------------------------
SerNum | UID1 | Result1 | UID2 | Result2 | UID3 | Result 3
-----------------------------------------------------------

Where the UIDs and Results can be null, and SerNum is not necessarily sequential (as shown in the example data).

I've tried several things but the closest I have gotten is

Alter Procedure [dbo].[spSerialNum](
                @LotNum varchar(50)
      )
AS

Truncate Table dbo.tbl_Serial_Num

Begin

  Insert into dbo.tbl_Serial_Num (Serial_Num, UID1, Result1, UID2, Result2, UID3, Result 3)
  Select A.SerNum, A.UID, A.ResultCode, B.UID, B.ResultCode, C.UID, C.ResultCode
  From dbo.tbl_Results A
  Left Join dbo.tbl_Results B ON A.LotNum = B.LotNum and A.SerNum = B.SerNum and A.UID < B.UID
  Left Join dbo.tbl_Results C ON A.LotNum = C.LotNum and A.SerNum = C.SerNum and B.UID < C.UID
  Where A.LotNum = @LotNum

This however returns a table that looks like this enter image description here

How do I get rid of the duplicate rows (If I am understanding it correctly its executing for every row of original data); is the Left Self Join not the right option for this or am I just missing a predicate?

Solution

  • Here is one way that doesn't use pivot since you stated they can only have 3 instances... 

    create table #tempTable([UID] int,LotNum varchar(16),SerNum int, ResultValue int, ResultCode int , TestType int)
insert into #tempTable
values
(1,'Lot1',1234,10,1,1),
(2,'Lot1',1234,5,1,1),
(3,'Lot1',1234,2,2,1),
(4,'Lot1',2345,5,1,1),
(5,'Lot1',2345,2,2,1),
(6,'Lot1',2345,2,2,1)

;with cte as(
select
    row_number() over (partition by SerNum order by [UID]) as RN
    ,[UID]
    --,LotNum
    ,SerNum
    --,ResultValue
    ,ResultCode
    --,TestType
from 
    #tempTable)

select
    s1.SerNum
    ,s1.UID as UID1
    ,s1.ResultCode as Result1
    ,s2.UID as UID2
    ,s2.ResultCode as Result2
    ,s3.UID as UID3
    ,s3.ResultCode as Result3
from
    cte s1
    left join
        cte s2 on s2.SerNum = s1.SerNum and s2.RN = s1.RN + 1
    left join
        cte s3 on s3.SerNum = s1.SerNum and s3.RN = s1.RN + 2
where
    s1.RN = 1


drop table #tempTable

    RETURNS

    +--------+------+---------+------+---------+------+---------+
| SerNum | UID1 | Result1 | UID2 | Result2 | UID3 | Result3 |
+--------+------+---------+------+---------+------+---------+
|   1234 |    1 |       1 |    2 |       1 |    3 |       2 |
|   2345 |    4 |       1 |    5 |       2 |    6 |       2 |
+--------+------+---------+------+---------+------+---------+