We help IT Professionals succeed at work.

SQL Server Pivot Query using like rather than absolute value

gwarcher
gwarcher asked
on
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.

Thanks,
Comment
Watch Question

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

Commented:
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.

Author

Commented:
Select PlaceName, ID, [SMITH, JOHN] as Smith, [DOE, JANE] as Doe
FROM
    (Select PlaceName, ID, Name, total from [table] where PlaceName = 'The Park') t
PIVOT
   (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
Commented:
No, you can't because those values will be used as column names.

Author

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