Concat the Second Column Value If the First Column Value Is Same

Concat the second column value if the first column value is same

Use Listagg() in 11g or WM_Concat() in 10g:

   SELECT LISTAGG(TITLE_OF_DOC_SEC, ',') WITHIN GROUP (ORDER BY TRACKING_NUM) AS TITLE_OF_DOC_SEC 
     FROM your table
    WHERE....

   SELECT WM_CONCAT(TITLE_OF_DOC_SEC) AS TITLE_OF_DOC_SEC
     FROM your table
    WHERE....

How to combine column values of one column into another column in MySQL?

Use union:

select birth_date
from players
union    -- on purpose to remove duplicates
select payment_date
from penalties;

Faster way to concatenate a column value to all values in another column pandas, do for all values in the first column

Use itertools.product here:

import itertools
pd.DataFrame([' '.join(i) for i in itertools.product(df.col_1,df.col_2)],columns=['col1'])

---

  col1
0  a d
1  a e
2  a f
3  b d
4  b e
5  b f
6  c d
7  c e
8  c f

Excel VBA Concatenate values in another column if the value in first column is the same

Here you go. The XlFindAll function isn't custom-written for this purpose, just customised. Therefore it contains some superfluous code.

Sub TestFindAll()
    ' 23 Dec 2017

    Dim Ws As Worksheet
    Dim Rng As Range                            ' range to search in
    Dim Matches As String
    Dim R As Long, Rl As Long

    Set Ws = ActiveSheet
    Application.ScreenUpdating = False
    With Ws
        Rl = .Cells(.Rows.Count, "B").End(xlUp).Row
        ' search items are in column B, starting in row 2
        Set Rng = Range(.Cells(2, "B"), .Cells(Rl, "B"))
        ' matches will be returned form the adjacent column
        ' however this can be adjusted in the XlFindAll function
        For R = 2 To Rl
            Matches = XlFindAll(Rng, .Cells(R, "B").Value)
            If Len(Matches) Then
                ' output to column D
                .Cells(R, "D").Value = .Cells(R, "B").Value & " (" & Matches & ")"
            End If
        Next R
    End With
    Application.ScreenUpdating = True
End Sub

Function XlFindAll(Where As Range, _
                   ByVal What As Variant, _
                   Optional ByVal LookIn As Variant = xlValues, _
                   Optional ByVal LookAt As Long = xlWhole, _
                   Optional ByVal SearchBy As Long = xlByColumns, _
                   Optional ByVal StartAfter As Long, _
                   Optional ByVal Direction As Long = xlNext, _
                   Optional ByVal MatchCase As Boolean = False, _
                   Optional ByVal MatchByte As Boolean = False, _
                   Optional ByVal After As Range, _
                   Optional ByVal FindFormat As Boolean = False) As String
    ' 23 Dec 2017
    ' Settings LookIn, LookAt, SearchOrder, and MatchByte
    ' are saved each time the Find method is used

    Dim Fun() As String
    Dim Search As Range
    Dim Fnd As Range
    Dim FirstFnd As String
    Dim i As Long

    Set Search = Where
    With Search
        If After Is Nothing Then
            If StartAfter Then
                StartAfter = WorksheetFunction.Min(StartAfter, .Cells.Count)
            Else
                StartAfter = .Cells.Count
            End If
            Set After = .Cells(StartAfter)
        End If

        Set Fnd = .Find(What:=What, After:=After, _
                        LookIn:=LookIn, LookAt:=LookAt, _
                        SearchOrder:=SearchBy, SearchDirection:=Direction, _
                        MatchCase:=MatchCase, MatchByte:=MatchByte, _
                        SearchFormat:=FindFormat)
        If Not Fnd Is Nothing Then
            FirstFnd = Fnd.Address
            ReDim Fun(100)
            Do
                ' select the value in the adjacent cell on the same row
                Fun(i) = Fnd.Offset(0, 1).Value
                i = i + 1
                Set Fnd = .FindNext(Fnd)
            Loop While Not (Fnd Is Nothing) And (Fnd.Address <> FirstFnd)
        End If
    End With

    If i Then ReDim Preserve Fun(i - 1)
    XlFindAll = Join(Fun, "-")
End Function

How to concat column values in SQL based on another column value

This is aggregation -- because you want one row per group in the result set. You also need a way to aggregate strings, which MySQL provides in the form of group_concat():

SELECT wp.id, wp.post_title, tt.taxonomy,
       GROUP_CONCAT(t.name)
FROM wp_posts wp JOIn
     wp_term_relationships r
     ON wp.ID = r.object_id JOIn
     wp_term_taxonomy tt
     ON r.term_taxonomy_id = tt.term_taxonomy_id  JOin
     wp_terms t
     ON t.term_id = tt.term_id 
WHERE tt.taxonomy like 'pa_%'
GROUP BY wp.id, wp.post_title, tt.taxonomy;

Note that the unaggregated columns are in the GROUP BY.

Multiple COALESCE in GROUP_CONCAT - is showing value if only first column isn't null

This does not do what you seem to want it to do:

COALESCE(sales.date, ',', purchase.date)

COALESCE() returns the first of the three values that is not NULL, so you will never get purchase.date: ',' is never NULL.

You basically want:

GROUP_CONCAT(purchase.date, ',', sales.date) AS date

However, you have learned that CONCAT() -- and even this implicit concatenate -- return NULL if any of the values are NULL. One solution is individual COALESCE() -- as in your answer. I cannot reproduce your problem. Here is an example of the code working.

Or, you can use CONCAT_WS() (although you miss the separator):

GROUP_CONCAT(CONCAT_WS(',', sales.date, purchase.date))

how to concatenate values of a column if the other columns have the same value

Use the GROUP_CONCAT() aggregate function

SELECT
    cc.contact_id,
    cc.name,
    GROUP_CONCAT(ct.name SEPARATOR ', ') AS contact_type
FROM
    contacts AS c
    LEFT JOIN contact_companies AS cc ON c.contact_id = cc.contact_id
    LEFT JOIN contacts_to_types AS ctt ON cc.contact_id = ctt.contact_id
    LEFT JOIN contact_types AS ct ON ctt.contact_type_id = ct.contact_type_id
WHERE
     cc.name LIKE '%p%'
GROUP BY cc.contact_id
ORDER BY name, contact_id

---

Related Topics