locked
comparing columns in two rows RRS feed

  • Question

  • I want to compare column values of top 2 records and capture the column values that existed in both records.

    Example:

    DDL of the table is below.

    CREATE TABLE [dbo].[Test](

    [c1] [int] NOT NULL,
    [c2] [int] NOT NULL,
    [c3] [int] NOT NULL,
    [c4] [int] NOT NULL,
    [c5] [int] NOT NULL
    ) ON [PRIMARY]

    GO

    Sample Output:

    C1 C2 C3 C4 C5

    15 24  39 48 51

    0  15  31 24  7

     since 15, 24 exist in both records I want to capture and put them in a single column seperated by comma's in a new table. Is this possible?

    Thanks

    • Moved by Maggie Luo Wednesday, November 21, 2012 1:44 AM (From:SQL Server Database Engine)
    Tuesday, November 20, 2012 11:54 PM

Answers

  • I am not sure I understand you. If you need 2 consecutive rows, you may want to instead of count(*) use a self-join based on Rn = Rn - 1 and ColumnValue = ColumnValue. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by oleolehoohoo Monday, November 26, 2012 5:03 PM
    Wednesday, November 21, 2012 1:08 PM
  • Sure it's possible although not related to your original question:

    select [Date], stuff((select ',' + convert(varchar(10),RtHs)
    from dbo.[2012_RtHs] R1 where R1.[Date] = R.[Date]
    FOR XML PATH('')),1,1,'') as RtHs
    from dbo.[2012_RtHs] R 
    GROUP BY [Date]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 26, 2012 5:20 PM

All replies

  • Try

    ;with cte as (select ID, ColumnName, ColumnValue, COUNT(*) over (partition by 
    ColumnValue) as cntValue from dbo.Test 
    unpivot (ColumnValue for ColumnName IN ([C1],[C2], [C3], [C4], [C5])) unpvt)
    
    select STUFF((select ', ' + cast(ColumnValue as varchar(10)) from cte 
    where cntValue = 2 GROUP BY ColumnValue for XML PATH('')),1,2,'') as SameValues 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, November 21, 2012 2:26 AM
  • Try

    ;with cte as (select ID, ColumnName, ColumnValue, COUNT(*) over (partition by 
    ColumnValue) as cntValue from dbo.Test 
    unpivot (ColumnValue for ColumnName IN ([C1],[C2], [C3], [C4], [C5])) unpvt)
    
    select STUFF((select ', ' + cast(ColumnValue as varchar(10)) from cte 
    where cntValue = 2 GROUP BY ColumnValue for XML PATH('')),1,2,'') as SameValues 


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Thanks for the reply Naomi. But I am not able to get around replacing the required values in your query.

     I am trying to identify the common column values in two consecutive records and move them to different table that has a single column. 

    Wednesday, November 21, 2012 3:50 AM
  • I am not sure I understand you. If you need 2 consecutive rows, you may want to instead of count(*) use a self-join based on Rn = Rn - 1 and ColumnValue = ColumnValue. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    • Marked as answer by oleolehoohoo Monday, November 26, 2012 5:03 PM
    Wednesday, November 21, 2012 1:08 PM
  • I am not sure I understand you. If you need 2 consecutive rows, you may want to instead of count(*) use a self-join based on Rn = Rn - 1 and ColumnValue = ColumnValue. 

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Sorry for the confusion. In this example I specified only two records. But there are almost 400 rows in the actual table. I have to compare records based on the following strategy:

    compare first record with second record and identify the common column values, compare the second record with third record and identify the common column values and so on till the end.

    Thanks much for your time and help.

    Wednesday, November 21, 2012 3:24 PM
  • In this case use a self-join based on the Id column. You still will need to unpivot first and then simply use a JOIN as I said in my previous message.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, November 21, 2012 4:26 PM
  • In this case use a self-join based on the Id column. You still will need to unpivot first and then simply use a JOIN as I said in my previous message.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    The query I wrote which is as shown below returns me the results but although it identifies multiple common column values it returns me 1.

    select A.c1 from dbo.[TEST] A inner join 
    dbo.[TEST] B on A.ID=B.ID-1 where A.c1=B.c1 or
    A.c1=B.c2 or
    A.c1=B.c3 or
    A.c1=B.c4 or
    A.c1=B.c5

    Does unpivoting help me fix this? 

    Wednesday, November 21, 2012 5:29 PM
  • Yes, it will. You will get several rows: 1 row for each matched value.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, November 21, 2012 5:51 PM
  • This will give you repeated values in continuous rows with row id

    CREATE TABLE #Test(

    [c1] [int] NOT NULL,
    [c2] [int] NOT NULL,
    [c3] [int] NOT NULL,
    [c4] [int] NOT NULL,
    [c5] [int] NOT NULL
    )

    alter table #test add id int identity(1,1) insert into #Test values(15, 24, 39, 48, 51) insert into #Test values(0 , 15 , 31 ,24, 7) insert into #Test values(44 , 18 , 31 ,24, 80) insert into #Test values(15, 24, 39, 48, 51) go with cte as (SELECT val,ID FROM (SELECT * FROM #Test ) src CROSS apply ( VALUES ([c1],'C1'),([c2],'C2'),([c3],'C3'),([c4],'Cl4'),([c5],'C5')) d(val, col)) select cte.val,cte.id from #test t1 inner join cte on (t1.id= cte.id-1 and cte.val in (t1.c1,t1.c2 ,t1.c3,t1.c4,t1.c5))



    Friday, November 23, 2012 4:17 AM
  • Thanks for all your inputs. I have output the query results to a table and I am trying to change it to a different format.

    I have the table structure as`

    CREATE TABLE [dbo].[2012_RtHs]( [Date] [datetime] NOT NULL, [RtHs] [int] NOT NULL ) ON [PRIMARY]

    GO

    The sample output is listed below:
    Date             RtHs
    2012-11-23   28
    2012-11-22   4
    2012-11-22   28
    2012-11-18   15
    2012-11-18   35
    2012-11-17   36
    2012-11-17   26

    I need the query that returns the out in the below format:

    Date            RtHs
    2012-11-23   28
    2012-11-22   4,28
    2012-11-18   15,35
    2012-11-17   36,26

    Is this possible?

    Thanks a bunch for everyone

    Monday, November 26, 2012 5:07 PM
  • Sure it's possible although not related to your original question:

    select [Date], stuff((select ',' + convert(varchar(10),RtHs)
    from dbo.[2012_RtHs] R1 where R1.[Date] = R.[Date]
    FOR XML PATH('')),1,1,'') as RtHs
    from dbo.[2012_RtHs] R 
    GROUP BY [Date]


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Monday, November 26, 2012 5:20 PM
  • Thanks Naomi
    Monday, November 26, 2012 7:11 PM