23 reputation
4
bio website
location
age
visits member for 3 years, 7 months
seen Oct 21 '11 at 18:20

Feb
27
awarded  Popular Question
Apr
21
comment Query to normalize table/combine row text
Yea, looks like that's what Thx did in his/her version (as noted, using MSSQL2005). For whatever reason they seemed to work quite a bit faster than manual cursor-ing. Someday I'll probably go back and see if it's actually making less sub queries or what.
Apr
19
comment Query to normalize table/combine row text
I should add though, there's a typo in the answer you gave: It needs a semicolon before the "WITH combinedresults" line. So: ;WITH combinedresults(ctid, id, rank, linenumber, combinedtext)
Apr
19
awarded  Scholar
Apr
19
comment Query to normalize table/combine row text
Legendary! That seems to work work well, takes 17 seconds after the obvious optimizations, which is a nice improvement. I'd upvote if I have the rep (takes 15?), but suffice to say Thank You Very Much!
Apr
19
accepted Query to normalize table/combine row text
Apr
18
revised Query to normalize table/combine row text
Added example of known working (but too slow) query.
Apr
18
awarded  Editor
Apr
18
revised Query to normalize table/combine row text
added 557 characters in body
Apr
18
comment Query to normalize table/combine row text
"input's do not have a id 2 rank 14"- See consideration 1 on the question. This is often the case, the query should pick the rank 13 value if 14 does not exist. I'm going to wait on optimizations until I can verify the query works. I'll update the post with more elaborate example data.
Apr
18
awarded  Student
Apr
18
comment Query to normalize table/combine row text
Didn't seem to work for me...After taking 2minutes 30 seconds (longer than the current while-loop method unfortunately), most of the CombinedText columns didn't get updated, and the ones that did were inconsistent (the same ID+Rank could have different values). When ran against the example case, the 3rd row (ID 2, Rank 14) has a blank CombinedText.
Apr
15
comment Query to normalize table/combine row text
Are you sure it requires row by row? I came across this page: projectdmx.com/tsql/rowconcatenate.aspx where there are solutions for a simpler cases (where the primary key is not composite). Why is it required in this case?
Apr
14
asked Query to normalize table/combine row text