October 29, 2014 at 3:09 pm
Context:
> SQL Amateur
> SQL Server is 2012
> I am selecting data per Order# from an Order Header table and the corresponding Order Line table. I am trying to consolidate multiple rows for a column from the Order Line table into a single row per Order#.
Problem:
> How to consolidate, with hard/carriage returns, where an unknown number of lines exist in the Order Line table for each order in the Order Header table.
I figured out how to pivot each line number from the Order Line table into individual columns for each line, but I really need a single column having hard returns between each line.
Including a snapshot which I'm hoping may draw a better picture than my description!
Please let me know if I need to provide more info.
October 29, 2014 at 3:31 pm
This article explains what you need to do.
http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Be sure to use it only for display and don't store the concatenation in the database.
Notes:
You can use CHAR(10) + CHAR(13) for the carriage return.
SSMS won't display the carriage returns in grid mode.
October 29, 2014 at 3:38 pm
Thanks for the link to article, Luis. I will give it a read and try it out.
October 29, 2014 at 8:07 pm
Hi,
I tried running the CTE found in the link you provided. I see how this applies, but I haven't been successful toying around with substituting a carriage return for the comma.
For example I tried the following--what am I doing wrong? I realize I'm not properly understanding the commalist = subquery, but intuit that char(13) may be in conflict or I'm not using it correctly.
Thx
WITH CTE AS
(
SELECT DISTINCT
AccountNumber
FROM #TestData
)
SELECT AccountNumber,
CommaList = STUFF((
--SELECT ',' + Value
SELECT CHAR(13) + Value
FROM #TestData
WHERE AccountNumber = CTE.AccountNumber
ORDER BY Value
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY AccountNumber;
October 30, 2014 at 8:10 am
I'm getting closer, I think I may have it. I've wrestled this down to this result with query results to text:
ORDER_NUMBER DESCRIPTION
--------------------- ------------------------------
451093.00
Line 1 Description
Line 2 Description
[font="Courier New"]
WITH CTE AS
(
SELECT DISTINCT
ORDER_NUMBER
FROM Order_Lines
WHERE ORDER_NUMBER = '451093.00'
)
SELECT ORDER_NUMBER,
[DESCRIPTION] = STUFF((
SELECT (CHAR(13) + CHAR(10)) + [DESCRIPTION]
FROM Order_Lines
WHERE ORDER_NUMBER = CTE.ORDER_NUMBER
ORDER BY LINE_NUMBER
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY ORDER_NUMBER;
[/font]
October 30, 2014 at 8:55 am
Total noob with all of the replies, apologies.
Final obstacle, perhaps: I am trying to figure out how to strip/replace the extra (CHAR(10) + CHAR(13)) which precedes each list of values?
In the example within the link you provided, a comma was placed before each value, then removed with the STUFF function. Handy, but I'm guessing this won't work with the CHAR(10) etc.
October 30, 2014 at 9:38 am
It works perfectly fine, the problem is that you're using 2 characters and stripping just one. You need to change the third parameter from the STUFF function.
To get better and faster answers, be sure to post sample data in a consumable format as the example that I'm posting.
CREATE TABLE Order_Lines( Line_Number int,
Order_Number decimal( 10, 2 ),
Description varchar( 150 ));
--------------------- ------------------------------
INSERT INTO Order_Lines
VALUES
( 1, 451093.00, 'Line 1 Description' ),
( 2, 451093.00, 'Line 2 Description' );
WITH Cte AS (
SELECT DISTINCT Order_Number
FROM Order_Lines
WHERE Order_Number = '451093.00'
)
SELECT Order_Number,
Description = STUFF((
SELECT CHAR( 13 ) + CHAR( 10 ) + [DESCRIPTION]
FROM Order_Lines
WHERE Order_Number = Cte.Order_Number
ORDER BY Line_Number
FOR XML PATH( '' ), TYPE).value( '.', 'varchar(max)' ), 1, 2, '' )
FROM Cte
ORDER BY Order_Number;
GO
DROP TABLE Order_Lines;
October 30, 2014 at 10:32 am
Got it. I think. Indicating as answered.
Found out how to REPLACE the ',' within the STUFF with carriage return here: http://stackoverflow.com/questions/18635697/carriage-return-in-sql-server-2012.
October 30, 2014 at 11:12 am
Posted last reply before refreshing/seeing/reading your response.
I am running tests on my personal, non-networked laptop first. Staying away from servers. Trying to understand what I am exploring, learning as I go.
Back to understanding, I studied and attempted the following TSQL using REPLACE and STUFF to try to understand how it is being framed and what it is doing:
[DESCRIPTION] = REPLACE(
STUFF((
SELECT ',' + [DESCRIPTION] --+ (CHAR(13) + CHAR(10))
FROM Order_Lines
WHERE ORDER_NUMBER = CTE.ORDER_NUMBER --'451093.00'
ORDER BY LINE_NUMBER
FOR XML PATH(''), TYPE).value('.','varchar(max)'), 1, 1,'')
, ',', CHAR(13) + CHAR(10))
I understand the above replaces the commas which remain--after STUFF removes the first comma--with CHAR(13) + CHAR(10).
But, I could not tell you if it is better or worse practice than what you suggested (much appreciated, btw)--save that it seems to me SQL has less work to do in your example w/o REPLACE:
Description = STUFF((
SELECT CHAR( 13 ) + CHAR( 10 ) + [DESCRIPTION]
FROM Order_Lines
WHERE Order_Number = Cte.Order_Number
ORDER BY Line_Number
FOR XML PATH( '' ), TYPE).value( '.', 'varchar(max)' ), 1, 2, '' )
The results "look" the same. I will try my hand at examining the execution plan to challenge myself.
And again, thanks for your response and caveats.
October 30, 2014 at 11:42 am
You're right, the REPLACE will add a small amount of work to the server which might be unnoticeable in most cases. However, you shouldn't want to add unnecessary work to the server and if you ever have a comma in your values, you might end up with an additional carriage return.
I am running tests on my personal, non-networked laptop first. Staying away from servers. Trying to understand what I am exploring, learning as I go.
That's a great habit.
I have to say that I'm impressed on how you managed to adapt the code and understand it. Most newbies aren't capable (or willing) to do it.
October 30, 2014 at 1:47 pm
Well, I'm not so smart when it comes to adapting this snippet into a join with the parent table, Orders.
As soon as I join to Orders on ORDER_NUMBER, I start getting two identical rows (two lines in Order_Lines). No need to respond, separate issue, as in how to join parent and child and still return only one row when child has two or more lines associated to parent. Frustrating--this feels like a duh. More to learn!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply