This is the syntax that I have and It presents me with 'No column name was specified for column 1 of 'A'

; With A As
(
  Select outcome, Count(UserID), location, '' As [One], SUM(value1+value2+value3) As [Total Value]
  From database1
  Where salemadeby ('George', 'Jan', 'Steve')
  And (Coalesce(approved, shipped, paid) Is not null)
  Group by outcome, location
)

Select outcome,[George],[Jan],[Steve] From A As ready
Pivot (Count(UserID) For outcome IN ([George],[Jan],[Steve])) As Final

And my desired output (or what I am trying to see is outcome on the left side, location on the top and Count ofuserid filling in the grid.

• Edited by IndigoMontoya 11 hours 58 minutes ago

you need alias for the column count(userID) in the CTE..

; With A As
(
  Select outcome, Count(UserID) as [UserID], location, '' As [One], SUM(value1+value2+value3) As [Total Value]
  From database1
  Where salemadeby ('George', 'Jan', 'Steve')
  And (Coalesce(approved, shipped, paid) Is not null)
  Group by outcome, location
)

Select outcome,[George],[Jan],[Steve] From A As ready
Pivot (Count(UserID) For outcome IN ([George],[Jan],[Steve])) As Final

Are you sure it doesn't say no column name was specified for column 2 of A?  Because column 2 is Count(UserID) so you need to give it a name.  There are other problems.  You use UserID in the final query, but UserID is not a column in the cte A, I'm not sure what you mean by the WHERE clause, do you mean samemadeby IN ...? and you are both pivoting by outcome and have outcome in the Select, which is not allowed.

Best would be if you gave a sample table and data (in the form of a CREATE TABLE and INSERT statements) and then showed us the result you want from that sample data.  That makes it much more likely we will be able to help you.

Tom