SELECT * FROM (SELECT CAST(mlblmsg AS varchar(max)) AS mlblmsg,clblcode FROM warninglabels) t PIVOT (MAX(mlblmsg) FOR clblcode IN ([0001],[0002],[003],[0004],[0005],[0006]))p
To get the table where this column use you can use this query. SELECT * FROM sys.tables WHERE tables.object_id IN (SELECT OBJECT_ID FROM sys.columns WHERE columns.name LIKE '%CDRUGNAME%') -- use your column name