June 24, 2015 at 12:15 pm
Hi
Probably not going to explain this correctly, but here it goes....
MyTable has the following data (Just a sample)
parent | NAme | Checked | contactmethod|Check2 | Other
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face | true | skills
974224 | Other | true | Contact | true | skills
I'd like to pivot on "parent"
In a perfect world I'd like to see output like
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face, Collateral Contact | true | skills
If there are more than one name or contactmethod for the same parent then they would be strung along with commas
Thanks in Advance
Joe
June 24, 2015 at 1:38 pm
Unfortunately, MS SQL's PIVOT only operates on one field, and what you're looking for isn't really a PIVOT operation. What you need is a concatenation function that can operate with GROUP BY, and to my knowledge, that requires a CLR assembly, which is an advanced method. I remember reading about such a function in one of Itzhik Ben-Gan 's "T-SQL Programming" books, and it was either for SQL 2005 or SQL 2008. If memory serves me correctly, he published the source in the book. I know that SQL 2008 doesn't have a concatenate function that could operate with GROUP BY to do this, however, there is a way to get this via a number of correlated subqueries that would need to appear in your SELECT statement. I don't have the time to demonstrate the how, but here's a link to a good explanation:
http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server
However, don't count on good performance...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply