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 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy