Just wondering if they need the actual quote information or just that they had one in the past 7 days?
If not, maybe you can use
REVERSE ( STUFF ( REVERSE ( REPLACE ( REPLACE
I would go into more detail, but I actually don’t understand it. I think I lifted it from @josecgomez but don’t remember the topic I got it from.
Found the thread
Yes, the thread is a nice history of getting rows into cells. And it’s a LONG thread, so read all the way to the bottom! Prior to SQL Serv2017, we had to use the FOR XML hack to accomplish this. If you’re on SQL 2017+, Microsoft created a T-SQL function to combine rows to columns with STRING_AGG. You must set the database compatibility level to 2017 or higher (140-150 if I recall).
@Mark_Wonsil we have 2016 installed and are using compatibility of 2014 as we had some major issues with clocking in and out when we tried to convert to 2016.
Yes, they are looking for the actual data.
I did use that format of the
REVERSE ( STUFF ( REVERSE ( REPLACE ( REPLACE
in another export that I am using, but it was very simple. This is combining multiple fields as well as multiple rows for the quote lines and part num with description, then combining that into another combination for each of the CustCnt.EMailAddresses across the past 7 days.
Yes, this thread is great and was helpful in my other BAQ as indicated in my other response, but I ended up getting to where I am currently using the following article at the end of the post you referenced.
KB0039364 - Turn Multiple Row Values from a Query into a Comma Separated List
That KB article does NOT use either method (FOR XML or the T-SQL), but uses CTE, UnionAll, and some calculated fields to pull the values together. That’s why I was hoping that someone would be able to help me get to the next step of combining the current data across the CustCnt.EMailAddress field. I’m still trying some things related to the FOR XML and some other possible items, but nothing is working so far.
If anyone has any other suggestions, I’m still all ears. Thanks to everyone that has posted all of the helpful information already!
If you can get a CTE to output each quote as
Email | Quote Info
You can then do a subquery and stuff it all into one line by email.
I will give that a try, but I’m not totally sure what I’m doing with it to get there. I’ll post back once I have a chance to try it out.