SQLTeam.com | Weblogs | Forums

Self join - previous value comparison for the ID and concatenation

#1

Dear expert,

Below is table and expected result. What is the query to achieve this result

Table : Test

ID LOT
7065161 4
7065212 1
7065212 4
7065203 1
7065203 2
7065203 3

Expected Result of query

ID LOT
7065161 4
7065212 1_4
7065203 1_2_3

#2

hi

hope this link helps

if not i can do it for you

#3

For future reference - to get tested solutions, please include a script to create a test table with sample data. Here is an example:

Declare @testTable Table (ID int, LOT int);

 Insert Into @testTable (ID, LOT)
 Values (7065161, 4)
      , (7065212, 1)
      , (7065212, 4)
      , (7065203, 1)
      , (7065203, 2)
      , (7065203, 3);

 Select *
   From @testTable tt;

 Select Distinct
        ID
      , LOTS = stuff((Select concat('_', tt2.LOT)
                        From @testTable tt2
                       Where tt2.ID = tt.ID
                         For xml Path('')), 1, 1, '')
   From @testTable tt
  Order By
        LOTS desc;

If you are on SQL Server 2017 or higher - the XML version can be changed to use STRING_AGG instead.

1 Like
#4

Hi Harish,

Thanks for your help on this. I would need the query if possible from your end. Concatenation i can do with this query. But what i am looking for is below output from Query.

Source Table : Test

ID LOT
7065161 4
7065212 1
7065212 4
7065203 1
7065203 2
7065203 3

ID LOT
7065161 4
7065212 1_4
7065203 1_2_3

#5

Hi Jeff,

Wonderful solution. Thanks a million for your help. I was looking for exactly same output.

Sure, i will take your suggestion going forward to provide scripts to create the table and sample data as well.

Have a wonderful day ahead.