Post reply

How to find groups of records that are identical

  • June 17, 2014 at 1:32 am

    #309159

    Hello,

    I would invite you all to find a solution for the following problem. I have a column A (colA) that for each value has several detail records (colB). I want to find the unique combinations of ColB that exists in the context of ColA.

    Example:

    ColA - ColB

    a 1

    a 5

    a 3

    b 1

    b 2

    c 5

    c 1

    c 3

    d 2

    The combination of ColB values 1, 3 and 5 appear twice, one for ColA value a and one time for value c. I only need those once.

    The combination of ColB values 1 and 2 appear once, for ColA value b.

    ColB value 2 appears once, for ColA value d.

    The result I want are the "tuples" (1, 3, 5), (1, 2) and (2) as records with same fake id:

    ID - ColB

    id1 1

    id1 3

    id1 5

    id2 1

    id2 2

    id3 2

    How can I do this?

    Thanks!

  • June 17, 2014 at 1:36 am

    #1722154

    One way you could tackle this is to concatenate all the columnB values into one string per colA:

    a 1,3,5

    b 1,2

    c 1,3,5

    d 2

    Then you take a DISTINCT over colB and split it out again.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 17, 2014 at 1:49 am

    #1722166

    Thanks,

    My problem is a bit more complex, I thought about this concatenation-solution but actually this colB is not one column but several columns. I could still concatenate the values into one big string, group them and then split them out again, but I was hoping for another solution that would contain only "sql" commands like group, join, apply, cte's etc.

  • June 17, 2014 at 1:51 am

    #1722171

    Peter.Frissen (6/17/2014)

    Thanks,

    My problem is a bit more complex, I thought about this concatenation-solution but actually this colB is not one column but several columns. I could still concatenate the values into one big string, group them and then split them out again, but I was hoping for another solution that would contain only "sql" commands like group, join, apply, cte's etc.

    Tsk tsk, FOR XML is part of the TSQL language 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • June 17, 2014 at 2:28 am

    #1722181

    Peter.Frissen (6/17/2014)

    Thanks,

    My problem is a bit more complex, I thought about this concatenation-solution but actually this colB is not one column but several columns. I could still concatenate the values into one big string, group them and then split them out again, but I was hoping for another solution that would contain only "sql" commands like group, join, apply, cte's etc.

    If you only provide us with half of the details, you're unlikely to get more than half of the solution 😛

    Have a quick read of the article linked in my sig block "Please read this" (which you should know already, having visited ssc over two hundred times). Post up ddl and dml. DwainC and I have done some work in this area and may be able to help. This might help too.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply