We help IT Professionals succeed at work.

SQL Server Pivot Query using like rather than absolute value

gwarcher asked
I am running a simple sql server pivot query.  I tried to google this but because the word "like" is such a common word I can't get a good answer.  

Can I run a PIVOT query using the LIKE syntax ['%data%'] instead of using the absolute value in the field?  I have a large dataset where some of the values may be different but at least one word will be constant.  I am trying to avoid a long and arduous clean-up process.

Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

I'm tempted to just say no, but instead give us a data mockup of what you're trying to pull off here, with emphasis on what is being like'd.


Select PlaceName, ID, [SMITH, JOHN] as Smith, [DOE, JANE] as Doe
    (Select PlaceName, ID, Name, total from [table] where PlaceName = 'The Park') t
   (sum(total) FOR Name IN ([SMITH, JOHN], [DOE, JANE])) as pvt

The SMITH, JOHN and DOE, JANE is where I would like to have a LIKE check.

I have some fields that match this perfectly, and others that have noise but the SMITH will always be there.
IT Engineer
Distinguished Expert 2017
No, you can't because those values will be used as column names.


ok, thanks, I will just do clean up as needed then.