January 27, 2014 at 10:01 am
Hello Everyone
I hope that you all had a very nice weekend.
I am working with some Document data that is in Text and RTF format. This data is stored in multiple rows with PersonDataID to indicate who the data belongs, and a DocumentDataID to indicate the other in which the data is to be put together. There can be one row, or many rows that make up the DocumentData. One row is pretty simple for me to figure out. But I need help in getting the rows with multiple parts to be concatenated. There will not always be the same number of rows per DocumentDataID. Sometimes there is only one, and sometimes there are multiples. The DocumentDataID is a sequential float number, usually incremented by what looks to be 50.
This is what the data looks like in the table:
DECLARE @DocumentData TABLE
(
DocumentDataID float
,PersonDataID float
, DocumentData varchar(2500)
);
-- This is the way the data is currently in the table
INSERT INTO @DocumentData
SELECT 1.19384762430111E+15,1.66652806506057E+15, 'This is a letter home to mom to tell her that I do not like being away at summer camp. Please send money and then come and get me. Signed, Son' UNION ALL
SELECT 1.25526204540759E+15,1.25526077500759E+15, 'This is the First Part of the Letter to Santa. ' UNION ALL
SELECT 1.25526204545759E+15,1.25526077500759E+15, 'This is the Second part of the letter telling what a good boy I have been. You do believe me don''t you ' UNION ALL
SELECT 1.25526204550759E+15,1.25526077500759E+15, 'this is the list of items that I want. This list can be very long, so get ready' UNION ALL
SELECT 1.25526204555759E+15,1.25526077500759E+15, 'I want a shiny new red bicycle, a Daisy Air Rifle with genuine fake wood stock, a basball mit, walkie-talkies, the ones like GI Joe uses in his tv show, building blocks, the red plastic ones that click together, a telescope with wide angle view finder and mirror, ' UNION ALL
SELECT 1.25526204560759E+15,1.25526077500759E+15, 'I do not want any clothes, socks or underware, those things are not worth having, and especially from you, Santa. I save all the good stuff for this for this time of year.' UNION ALL
SELECT 1.25527705140696E+15,1.25527558800824E+15, 'This is a letter to the Secrectary of the Navy informing them that I cannot qualify to be a fighter pilot, but that I do have ' UNION ALL
SELECT 1.25527705145696E+15,1.25527558800824E+15, 'other jobs that I can do. Like serve coffee to the Admiral, drive the Admiral around to his meetings in that big fancy car with the flags on the fenders, and other things like that.' UNION ALL
SELECT 1.25527705150696E+15,1.25527558800824E+15, ', driving the big fancy car in parades and such, that is what I want to do if I cannot be a Fighter Pilot.'
SELECT * FROM @DocumentData
ORDER BY DocumentDataID ASC;
This is how I need the data needs to be, so that all the data parts are in one row, and in correct order.
DECLARE @DocumentDataPutTogether TABLE
(
DocumentDataID float
,PersonDataID float
, DocumentData varchar(max)
);
-- This is the way that I need the data concatenated
INSERT INTO @DocumentDataPutTogether
SELECT 1.19384762430111E+15,1.66652806506057E+15, 'This is a letter home to mom to tell her that I do not like being away at summer camp. Please send money and then come and get me. Signed, Son' UNION ALL
SELECT 1.25526204540759E+15,1.25526077500759E+15, 'This is the First Part of the Letter to Santa. This is the Second part of the letter telling what a good boy I have been. You do believe me don''t you this is the list of items that I want. This list can be very long' UNION ALL
SELECT 1.25527705140696E+15,1.25527558800824E+15, 'This is a letter to the Secrectary of the Navy informing them that I cannot qualify to be a fighter pilot, but that I do have other jobs that I can do. Like serve coffee to the Admiral, drive the Admiral around to his meetings in that big fancy car with the flags on the fenders, and other things like that.'
SELECT * FROM
@DocumentDataPutTogether
ORDER BY DocumentDataID ASC;
How can I know how many rows make up one Document? How can I put the DocumentData back together in proper order?
I greatly appreciate all your help with this, and thank you in advance for your work, comments, suggestions and advice.
Andrew SQLDBA
January 27, 2014 at 5:32 pm
Take a look at this SQL Spackle article by Wayne Sheffield: Creating a comma-separated list[/url]
SELECT DocumentDataID=MIN(DocumentDataID), PersonDataID
,DocumentData=
(
SELECT '' + DocumentData
FROM @DocumentData b
WHERE a.PersonDataID = b.PersonDataID
ORDER BY DocumentDataID
FOR XML PATH('')
)
FROM @DocumentData a
GROUP BY PersonDataID
ORDER BY DocumentDataID;
I have used a FOR XML PATH('') that may return invalid results if your document text contains special characters. In his article, Wayne shows a way around that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 27, 2014 at 11:34 pm
Thanks Dwain.C
That did the trick for that. What are you talking about, "special characters"? You did not include the article. There are some rows that use RTF data, would that be an issue?
Andrew SQLDBA
January 27, 2014 at 11:48 pm
AndrewSQLDBA (1/27/2014)
Thanks Dwain.CThat did the trick for that. What are you talking about, "special characters"? You did not include the article. There are some rows that use RTF data, would that be an issue?
Andrew SQLDBA
Sorry that I don't have handy that list of special characters (used to have a link somewhere) but I think that < and > are included in them. I think there's about half a dozen of them.
Did you click the link to get to the referenced article? I don't think Wayne discussed them in there either though. I just know that if you can use the stripped down version I provided, it's a bit faster than with the special character handling.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply