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.
SELECT ArticleCode, Color, STUFF((SELECT'-'+ CAST(t.Qty AS NVARCHAR(MAX))FROM ArticleTable t
WHERE t.ArticleCode = a.ArticleCode AND t.Color = a.Color
FORXML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')FROM ArticleTable a
GROUPBY ArticleCode, Color
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.
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
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
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
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
Suwandi - Non Graduate Programmer
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
Thanking You
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.
Results to Text
With Regards,
Fei Han