December 3, 2015 at 5:58 am
I am working with a table that is kind of 'funky'. It has text one more than one line for the same record. An example is like this:
ordernumber textline text
1 1 This text is part of
1 2 the first order.
2 1 This is the text
2 2 for the second
2 3 order.
How could I do a select statement that would combine the text for each order into a single record, such as this:
Results:
ordernumber text
1 This text is part of the first order.
2 This is the text for the second order.
Thanks!
December 3, 2015 at 6:16 am
Use FOR XML PATH('') aggregation https://msdn.microsoft.com/en-us/library/ms189885(v=sql.120).aspx
Please provide consumable DDL and test data DML if you need more assitance.
December 9, 2015 at 10:36 am
I agree with Sergiy. XML makes this easy.
As a hint, you can use the idea of joining a column to itself.
select '' + mycol
from mytable
If you have the tsqlt framework, here's a test to combine the data. Insert your query into the MyCTE to test.
CREATE PROCEDURE [tsqlt].[test combine order line items correctly]
-- ALTER PROCEDURE [tsqlt].[test combine order line items correctly]
AS
begin
EXEC tsqlt.FakeTable
@TableName = N'Orders2'
INSERT Orders2
( orderid, lineid, mytext )
VALUES
( 1, 1, 'This text is part of' )
, ( 1, 2, ' the first order.' )
, ( 2, 1, 'This text is ' )
, ( 2, 2, 'part of ' )
, ( 2, 3, 'the second order.' )
;
CREATE TABLE #expected (orderid INT, ordertext VARCHAR(5000));
INSERT #expected
( orderid, ordertext )
VALUES
( 1, 'This text is part of the first order.')
, ( 2, 'This text is part of the second order.');
WITH myCTE(orderid, ordertext)
AS
(
SELECT *
FROM orders2 o
)
SELECT *
INTO #actual
FROM myCTE
EXEC tsqlt.AssertEqualsTable
@Expected = N'#expected'
, -- nvarchar(max)
@Actual = N'#actual'
, -- nvarchar(max)
@FailMsg = N'Incorrect query' -- nvarchar(max)
END
GO
December 9, 2015 at 12:49 pm
This is actually a nice little exercise for practice. Although you'd hope that you wouldn't have to normally deal with tables like this. 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply