June 26, 2014 at 7:21 am
Hi
I have a table(Comments)
two columns clientno and comment
one clientno can have many comments
I want to merge all the comments for one clientno in a new field
example
clientno comment
001 comments1
001 comments2
002 comments3
so in the output I want
001 comments1 comments2
002 comments3
Thanks in Advance
Joe
June 26, 2014 at 7:24 am
jbalbo (6/26/2014)
HiI have a table(Comments)
two columns clientno and comment
one clientno can have many comments
I want to merge all the comments for one clientno in a new field
example
clientno comment
001 comments1
001 comments2
002 comments3
so in the output I want
001 comments1 comments2
002 comments3
Thanks in Advance
Joe
Any chance you can post some actual details? Do you want to merge all the values into a single column or are you looking to get a dynamic number of columns? Post ddl and sample data. You have been around here long enough this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 7:29 am
I'm sorry, and I'm not trying to be a smartA$$...
But could you give me an axample of wht you would like to see?
Thanks
June 26, 2014 at 7:37 am
jbalbo (6/26/2014)
I'm sorry, and I'm not trying to be a smartA$$...But could you give me an axample of wht you would like to see?
Thanks
If you want this in a single column all you need to do is look at this article. A slight modification to the technique here will produce your output. http://www.sqlservercentral.com/articles/comma+separated+list/71700/[/url]
If you want a dynamic number of columns you would need a cross tab. You can find articles about cross tabs in my signature.
You might want to take a few minutes and read the article found at the first link in my signature for best practices when posting questions. The short answer is that if you had posted ddl (create table statements) and sample data (insert statements) you would already have working code. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 8:05 am
So somehow you figured out what i need via my cryptic request...
and I came up with this
WITH CTE AS
(
SELECT DISTINCT
CLINO
FROM Hill_Comments
WHERE COMMENT IS NOT NULL
)
SELECT CLINO,
CommaList = STUFF((
SELECT ',' + COMMENT
FROM Hill_Comments
WHERE CLINO = CTE.CLINO
ORDER BY COMMENT
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY CLINO;
I am getting this error :
FOR XML could not serialize the data for node 'NoName' because it contains a character (0x000E) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
Here is some COMMENT Sample data, COMMENT Field is NVARCHAR(MAX)
04/10/14 Bo23rgie presenting clinical for a jjjsp w/ +SI, no plan, feeling stressed and weak. "I just don't feel right, I don't fit in." jjA/V=. Good sleep and appetite. In treatment, happy with his meds, denies any substance use. +THC but states he doesn't use and it had to be second hand. Agitated, Irritable, guarded, you people ask too many questions. Clt refused an outpatient list - "I don't want therapy, it doesn't work for me." Refused to answer most questions and mood & affect appear ioluiol. States that he can stay safe and wants to return home. Clt cannot describe any precipitants. Clt will be d/c'd home with a provider list for a therapist. MkkkkCakkkkkey, MEd., MA 04/09/14 lmyyh edu called at 9:59p to request an eval for this patient who is reporting depression and si. pos for thc. jjj. cleared by jjan. kkk, LMFT
June 26, 2014 at 8:18 am
That is char(14). Not sure how you managed to get that character in your data but you can just replace it with an empty string.
WITH CTE AS
(
SELECT DISTINCT CLINO
FROM Hill_Comments
WHERE COMMENT IS NOT NULL
)
SELECT CLINO,
CommaList = STUFF((
SELECT ',' + replace(COMMENT, CHAR(14), '')
FROM Hill_Comments
WHERE CLINO = CTE.CLINO
ORDER BY COMMENT
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,'')
FROM CTE
ORDER BY CLINO;
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 8:36 am
Thanks for that..
Now I get ...because it contains a character (0x0003)
I read that only &, < and > (as well as " or ' in attributes) are illegal in XML ?
Can I include them all with the replace statement?
Also is there anywhere I can look up these codes?
Thanks
June 26, 2014 at 8:52 am
jbalbo (6/26/2014)
Thanks for that..Now I get ...because it contains a character (0x0003)
I read that only &, < and > (as well as " or ' in attributes) are illegal in XML ?
Can I include them all with the replace statement?
Also is there anywhere I can look up these codes?
Thanks
http://www.redgrittybrick.org/ascii.html
Since 3 is end of text I have a feeling that is probably all you would need. Just add a nested replace to remove char(3).
I am guessing this data comes from a mainframe somewhere? Those are not codes that are usually generated by humans but by some program.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 26, 2014 at 10:23 am
Thanks for the Help...
Here is my final list(If interested..lol)
Yes the data came from an old foxpro db
Sorry, one more question , OK two....
one...
can I add a carriage return in for each record it picks up ?
so if cltno 101 has four records when it displays it will be on four lines ?
two...
if I added a field to this table is it possible to write the combned data to that field?
Thanks again....
Joe
June 26, 2014 at 12:41 pm
jbalbo (6/26/2014)
Thanks for the Help...Here is my final list(If interested..lol)
Yes the data came from an old foxpro db
Sorry, one more question , OK two....
one...
can I add a carriage return in for each record it picks up ?
so if cltno 101 has four records when it displays it will be on four lines ?
Yes. Look at the ASCII chart, it will show what character you can add.
two...
if I added a field to this table is it possible to write the combned data to that field?
Thanks again....
Joe
Yes. But why do you want denormalized data in a column? Since you effectively merging rows, which row would you put this new denormalized data on? How would you know if the value in a given row is current? I would recommend not doing this, but it is certainly possible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 27, 2014 at 8:53 am
Hi..
or "Hi Sean" !!!
So I have the data merging, and read the article and assumed to replace SELECT ', ' +
with SELECT CHR(13) + ,for a Carriage Return?
It doesn't seem to seperate the comments being merged.
Not sure if it matters that this data will be inserted into an MSACCESS table ?
Thanks...
WITH CTE AS
(
SELECT DISTINCT CLINO
FROM Hill_Comments
WHERE COMMENT IS NOT NULL
)
SELECT CLINO,
CommaList = STUFF((
SELECT CHR(13) +
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(replace(replace(replace(replace(replace(
COMMENT, CHAR(14), ''), CHAR(3), ''), CHAR(16), ''), CHAR(15), ''), CHAR(21), '')
, CHAR(8), ''), CHAR(7), ''), CHAR(20), ''), CHAR(19), ''), CHAR(2), ''), CHAR(5), '')
FROM Hill_Comments
WHERE CLINO = CTE.CLINO
ORDER BY a_date
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,' ')
FROM CTE
ORDER BY CLINO;
June 27, 2014 at 9:04 am
Switch you char(13) to char(10). 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2014 at 12:22 pm
Sean helped me with this awhile back..
I need to add a carriage return between each record and I seem stuck !!
Once I get the carriage return it getting exported to excel
Thanks
Joe
WITH CTE AS
(
SELECT DISTINCT CLINO
FROM Hill_Comments
WHERE COMMENT IS NOT NULL
)
SELECT CLINO,
CommaList = STUFF((
SELECT CHAR(10) +
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(replace(replace(replace(replace(replace(
COMMENT, CHAR(14), ''), CHAR(3), ''), CHAR(16), ''), CHAR(15), ''), CHAR(21), '')
, CHAR(8), ''), CHAR(7), ''), CHAR(20), ''), CHAR(19), ''), CHAR(2), ''), CHAR(5), '')
FROM Hill_Comments
WHERE CLINO = CTE.CLINO
ORDER BY a_date
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,' ')
FROM CTE
ORDER BY CLINO;
July 23, 2014 at 12:54 pm
jbalbo (7/23/2014)
Sean helped me with this awhile back..I need to add a carriage return between each record and I seem stuck !!
Once I get the carriage return it getting exported to excel
Thanks
Joe
WITH CTE AS
(
SELECT DISTINCT CLINO
FROM Hill_Comments
WHERE COMMENT IS NOT NULL
)
SELECT CLINO,
CommaList = STUFF((
SELECT CHAR(10) +
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(replace(replace(replace(replace(replace(replace(
COMMENT, CHAR(14), ''), CHAR(3), ''), CHAR(16), ''), CHAR(15), ''), CHAR(21), '')
, CHAR(8), ''), CHAR(7), ''), CHAR(20), ''), CHAR(19), ''), CHAR(2), ''), CHAR(5), '')
FROM Hill_Comments
WHERE CLINO = CTE.CLINO
ORDER BY a_date
FOR XML PATH(''),
TYPE).value('.','varchar(max)'),1,1,' ')
FROM CTE
ORDER BY CLINO;
Awesome!!! Glad you got this working and thanks for letting me know.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 23, 2014 at 1:30 pm
Hi Sean
Thanks for the help with this..
As usual I can't explain myself.
Everything work except the carraage return.
I tried CHR13, but when I copy and paste into say .. notepad from the results window I get the data all stringed together, do I have the CHR13 in the wrong spot ?
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply