Help with code to Grouping the text in multiple lines

Question

Help with code to Grouping the text in multiple lines

v.razaghzadeh

SSChasing Mays

Points: 656
More actions
November 26, 2017 at 7:24 am

#326513

Hello everyone
I am a newcomer to the world of TSQL and was wondering if someone can help me with the following:
I have a simple code as below
select * from line_Text where Line_id = '541105516'
this will return the following result
Line_id    Sequence_Number    Text Batch_Id_INSERT    Batch_Id_UPDATE
541105516    1 50 PERCENT 112586 NULL
541105516    3 112586 NULL
541105516    5 Discount 112586 NULL
541105516    7 Given 112586 NULL
541105516    9 SERIAL:01 112586 NULL
In the result above the column Text is split into different lines because the user presses enter key so the same line ID is repeated for the texts.
What I want to do is somehow for each line_id join all the texts together and show it as one line so when I run the query i should get
541105516    1 50 PERCENT Dicount Given Serial:01 112586 NULL
The reason is in my actual code i am joining this table to another table which has the price of item and because its repeated I get the same for the same item value for each line and my sum is wrong.
Hope this makes sense what I am trying to achieve.
Any help is appreciate it.
Thanks in advance

Viewing 8 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

v.razaghzadeh - Sunday, November 26, 2017 7:24 AM
Hello everyone
I am a newcomer to the world of TSQL and was wondering if someone can help me with the following:
I have a simple code as below
select * from line_Text where Line_id = '541105516'
this will return the following result
Line_id    Sequence_Number    Text Batch_Id_INSERT    Batch_Id_UPDATE
541105516    1 50 PERCENT 112586 NULL
541105516    3 112586 NULL
541105516    5 Discount 112586 NULL
541105516    7 Given 112586 NULL
541105516    9 SERIAL:01 112586 NULL
In the result above the column Text is split into different lines because the user presses enter key so the same line ID is repeated for the texts.
What I want to do is somehow for each line_id join all the texts together and show it as one line so when I run the query i should get
541105516    1 50 PERCENT Dicount Given Serial:01 112586 NULL
The reason is in my actual code i am joining this table to another table which has the price of item and because its repeated I get the same for the same item value for each line and my sum is wrong.
Hope this makes sense what I am trying to achieve.
Any help is appreciate it.
Thanks in advance

You've been here long enough to know this: please provide your sample data in a consumable format.

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

v.razaghzadeh - Sunday, November 26, 2017 7:24 AM
Hello everyone
I am a newcomer to the world of TSQL and was wondering if someone can help me with the following:
I have a simple code as below
select * from line_Text where Line_id = '541105516'
this will return the following result
Line_id    Sequence_Number    Text Batch_Id_INSERT    Batch_Id_UPDATE
541105516    1 50 PERCENT 112586 NULL
541105516    3 112586 NULL
541105516    5 Discount 112586 NULL
541105516    7 Given 112586 NULL
541105516    9 SERIAL:01 112586 NULL
In the result above the column Text is split into different lines because the user presses enter key so the same line ID is repeated for the texts.
What I want to do is somehow for each line_id join all the texts together and show it as one line so when I run the query i should get
541105516    1 50 PERCENT Dicount Given Serial:01 112586 NULL
The reason is in my actual code i am joining this table to another table which has the price of item and because its repeated I get the same for the same item value for each line and my sum is wrong.
Hope this makes sense what I am trying to achieve.
Any help is appreciate it.
Thanks in advance

Please see the following article for how to do such a thing. Use spaces instead of commas.

http://www.sqlservercentral.com/articles/comma+separated+list/71700/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 3

Dear Phil

Thanks for yur reply, I have put the result in an excel spreadsheet so hopefully it be more clearer. Sorry about not doing it properly.

Hello everyone

I am a newcomer to the world of TSQL and was wondering if someone can help me with the following:

I have a simple code as below

select * from line_Text where Line_id = '541105516'
this will return the following result which I have attached as a excel ile

In the result Text is split into different lines because the user presses enter key so the same line ID is repeated for the texts.

What I want to do is somehow for each line_id join all the texts together and show it as one line so when I run the query i should get

541105516 1 50 PERCENT Discount Given Serial:01 112586 NULL

The reason is in my actual code i am joining this table to another table which has the price of item and because its repeated I get the same for the same item value for each line and my sum is wrong.

Basically I want all the texts to be put in one row for the line_id

Hope this makes sense what I am trying to achieve.

Any help is appreciate it.

Thanks in advance

Thom A SSC Guru Points: 99143 More actions · Answer 4

You'll find it unlikely that people will open and/or use your Excel document. You need to supply your data in a consumable format; Phil, Jeff and I all have a link in a signature explaining how to do that.

On the subject of achieving your goal, what was wrong with the article Jeff linked to?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 5

Dear Jeff

Thank you for your reply. I will look at this link as it does seem to be what I need.

Thanks again for your help

v.razaghzadeh SSChasing Mays Points: 656 More actions · Answer 6

Thom A - Monday, November 27, 2017 2:36 AM
You'll find it unlikely that people will open and/or use your Excel document. You need to supply your data in a consumable format; Phil, Jeff and I all have a link in a signature explaining how to do that.
On the subject of achieving your goal, what was wrong with the article Jeff linked to?

Dear Phil

Thanks for your advise.

I have now hopefully followed the instruction on the link and below is the sample code generator
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL DROP TABLE #mytable

--===== Create the test table with CREATE TABLE #mytable ( Line_id int , Sequence_Number INT IDENTITY(1,1) , --Is an IDENTITY column on real table, Text varchar(Max) )

--===== All Inserts into the IDENTITY column SET IDENTITY_INSERT #mytable ON

--===== Insert the test data into the test table INSERT INTO #mytable (Line_id,Sequence_Number, Text)

SELECT '182613325','1','Text Line 1' UNION ALL SELECT '182613325','2','Text Line 2' UNION ALL SELECT '182613325','3','Text Line 3' UNION ALL SELECT '182613325','4','Text Line 4' UNION ALL SELECT '116235666','1','Text Line 11' UNION ALL SELECT '116235666','2','Text Line 21' UNION ALL SELECT '116235666','3','Text Line 31' UNION ALL SELECT '116235666','4','Text Line 41'

--===== Set the identity insert back to normal SET IDENTITY_INSERT #mytable OFF

select * from #mytable

What I want to achieve is line_id 182613325 with text Line1 text Line 2 Text Line 3 text line 4 in one row
and
line id 116235666 text Line 11 Text Line 21 Text Line 31 text Line 41 in second row.

i.e. all the texts for the specific line Id to together in one line.

Hopefully I have followed the right protocol now.

hanks again for your advise

Phil Parkin SSC Guru Points: 247180 More actions · Answer 7

As you've been so helpful in posting all the DDL, sample data and desired results, I've written the solution out for you below.
Note that it is almost a direct copy of the code which appears in the link posted by Jeff ... you'll find that you get better responses in future if you demonstrate that you have attempted to follow their advice.
--===== If the test table already exists, drop it IF OBJECT_ID('TempDB..#mytable', 'U') IS NOT NULL DROP TABLE #mytable;

--===== Create the test table with CREATE TABLE #mytable ( Line_id INT , Sequence_Number INT , Txt VARCHAR(MAX) );

--===== Insert the test data into the test table INSERT #mytable (Line_id,Sequence_Number, Txt) VALUES ('182613325','1','Text Line 1') , ('182613325','2','Text Line 2') , ('182613325','3','Text Line 3') , ('182613325','4','Text Line 4') , ('116235666','1','Text Line 11') , ('116235666','2','Text Line 21') , ('116235666','3','Text Line 31') , ('116235666','4','Text Line 41');

WITH CTE AS ( SELECT DISTINCT Line_id FROM #mytable ) SELECT CTE.Line_id , CommaList = STUFF(( SELECT ' ' + m.Txt FROM #mytable m WHERE m.Line_id = CTE.Line_id ORDER BY Sequence_Number FOR XML PATH(''), TYPE ).value('.', 'varchar(max)') ,1 ,1 ,'' ) FROM CTE ORDER BY CTE.Line_id;