one column with break to next line[Answered] RSS

• • Reply
  Gopi.MCA

  Member

  293 Points

  676 Posts

  one column with break to next line

  Sep 10, 2019 10:56 AM|Gopi.MCA|LINK

  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

  Can we have output like below in stuff query

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

• • Reply
  Fei Han - MS...

  All-Star

  40565 Points

  6233 Posts

  Microsoft

  Re: one column with break to next line

  Sep 11, 2019 02:19 AM|Fei Han - MSFT|LINK

  Hi Gopi.MCA,

  If you'd like to print query result on separate line, you can try to use CHAR(10) or CHAR(13) to make the string more readable.

  https://docs.microsoft.com/en-us/sql/t-sql/functions/char-transact-sql?view=sql-server-2017#remarks

  Example

  

  But if you'd like to get above displaying effect on web page, you may need to dynamically insert <br/> tag between Qty(s).

  With Regards,

  Fei Han

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
• • Reply
  oned_gk

  All-Star

  52673 Points

  15719 Posts

  Re: one column with break to next line

  Sep 11, 2019 07:25 AM|oned_gk|LINK

  I guess, it's similart with

  https://stackoverflow.com/questions/47689303/how-can-i-sum-strings-in-one-column-into-one-row-in-microsoft-sql-group-by-does

  Use char(13) for enter key or char(10) new line to replace coma

  https://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

  Mostly, for displaying data like your requirement i did it in the page instead of sql

  Programming to simplify, don't look for difficult way
  Suwandi - Non Graduate Programmer
• • Reply
  Gopi.MCA

  Member

  293 Points

  676 Posts

  Re: one column with break to next line

  Sep 11, 2019 10:19 AM|Gopi.MCA|LINK

  Hello

  How To Apply For This Below Code

  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

  Thanking You

• • Reply
  Fei Han - MS...

  All-Star

  40565 Points

  6233 Posts

  Microsoft

  Re: one column with break to next line

  Sep 12, 2019 06:11 AM|Fei Han - MSFT|LINK

  Hi Gopi.MCA,

  You can refer to the following query.

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

  Results to Text

  

  With Regards,

  Fei Han

  .NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.