locked
Merge Column To One RRS feed

  • Question

  • User-807418713 posted

    Hello

    This is my table data

    ArticleCode Color Qty
    A1 Black 2
    A1 Black 4
    A2 Red 5
    A2 Red 10
    A2 Red 15
    C1 Green 6

    I want resultset like this

    ArticleCode Color Qty
    A1 Black 2 - 4
    A2 Red 5 - 10 - 15
    C1 Green 6

    Thanking You

    Tuesday, September 10, 2019 6:27 AM

Answers

  • User61956409 posted

    Hi Gopi.MCA,

    To achieve the requirement, please try the following query.

    SELECT ArticleCode, Color, STUFF((
              SELECT '-' + CAST(t.Qty AS NVARCHAR(MAX))
              FROM ArticleTable t
              WHERE t.ArticleCode = a.ArticleCode AND  t.Color = a.Color
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM ArticleTable a
    GROUP BY ArticleCode, Color

    Query Result

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 10, 2019 7:12 AM

All replies

  • User61956409 posted

    Hi Gopi.MCA,

    To achieve the requirement, please try the following query.

    SELECT ArticleCode, Color, STUFF((
              SELECT '-' + CAST(t.Qty AS NVARCHAR(MAX))
              FROM ArticleTable t
              WHERE t.ArticleCode = a.ArticleCode AND  t.Color = a.Color
              FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
    FROM ArticleTable a
    GROUP BY ArticleCode, Color

    Query Result

    With Regards,

    Fei Han

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, September 10, 2019 7:12 AM
  • User-807418713 posted

    Hello

    Thank You For Your Reply

    Can we have output like below in stuff query

    ArticleCode

    Color

    Qty

    A1

    Black

    2

    4

    A2

    Red

    5

    10

    15

    C1

    Green

    6

    Tuesday, September 10, 2019 7:51 AM