title: SQL Server: Dynamic columns returned from query
body: <p>I've got a fairly complex SQL query running against data.stackexchange.com.  The query can be found at <a href="https://data.stackexchange.com/stackoverflow/query/154580/find-devs-by-location-and-reputation" rel="nofollow noreferrer">this link</a> (and pasted below).</p>
  <pre><code>DECLARE @Location varchar(128) = ##Location:string##
  DECLARE @RepLimit int = ##RepLimit:int##
  
  SELECT Users.DisplayName,
         Users.Id,
         Users.WebsiteUrl,
         Users.Reputation,
         (
             SELECT COUNT(*)
             FROM Posts
             JOIN PostTags ON Posts.ParentId = PostTags.PostId
             JOIN Tags ON PostTags.TagId = Tags.Id
             WHERE Posts.PostTypeId = 2
             AND Tags.Id = 3 -- Javascript
             AND Posts.OwnerUserId = Users.Id
         ) AS JavascriptCount,
         
         (
             SELECT COUNT(*)
             FROM Posts
             JOIN PostTags ON Posts.ParentId = PostTags.PostId
             JOIN Tags ON PostTags.TagId = Tags.Id
             WHERE Posts.PostTypeId = 2
             AND Tags.Id = 5 -- PHP
             AND Posts.OwnerUserId = Users.Id
         ) AS PhpCount,
         
         (
             SELECT COUNT(*)
             FROM Posts
             JOIN PostTags ON Posts.ParentId = PostTags.PostId
             JOIN Tags ON PostTags.TagId = Tags.Id
             WHERE Posts.PostTypeId = 2
             AND Tags.Id = 820 -- jQuery
             AND Posts.OwnerUserId = Users.Id
         ) AS jQueryCount,
         
         (
             SELECT COUNT(*)
             FROM Posts
             JOIN PostTags ON Posts.ParentId = PostTags.PostId
             JOIN Tags ON PostTags.TagId = Tags.Id
             WHERE Posts.PostTypeId = 2
             AND Tags.Id = 21 -- MySQL
             AND Posts.OwnerUserId = Users.Id
         ) AS MySqlCount,
         
         (
             SELECT COUNT(*)
             FROM Posts
             JOIN PostTags ON Posts.ParentId = PostTags.PostId
             JOIN Tags ON PostTags.TagId = Tags.Id
             WHERE Posts.PostTypeId = 2
             AND Tags.Id = 1386 -- Android
             AND Posts.OwnerUserId = Users.Id
         ) AS AndroidCount,
         
         (
             SELECT COUNT(*)
             FROM Posts
             JOIN PostTags ON Posts.ParentId = PostTags.PostId
             JOIN Tags ON PostTags.TagId = Tags.Id
             WHERE Posts.PostTypeId = 2
             AND Tags.Id IN (58338, 81106, 92809, 7003) -- IOS
             AND Posts.OwnerUserId = Users.Id
         ) AS IosCount
  
  FROM Users
  
  WHERE Users.Reputation &gt; @RepLimit
  AND Users.Location = @Location
  </code></pre>
  <p>In the above query, a few things happen:</p>
  <ol>
  <li>On data.stackexchange.com, they generate a form field for me to enter the data that I have <code>DECLARE</code>d at the top (in this case, Location and RepLimit).</li>
  <li>The tags I'm searching for (Javascript, iOS, Android, PHP, etc.) are hardcoded, each using a sub-select, which isn't as efficient as I think it could be.</li>
  </ol>
  <p>I want to change two things, but am not familiar enough with SQL Server to know if they're both doable (and also don't know what exactly to query to find the results I'd need).  The two changes are:</p>
  <ol>
  <li>I'd like to optimize the query.  Right now, I feel like making six almost identical sub-selects is not the ideal way to accomplish the end result.</li>
  <li>I'd like the list of tags to be provided as either a comma-separated list in a new form element (being <code>DECLARE</code>d at the top, like Location and RepLimit), or as 5 separate form fields (limiting the query to up to 5 different tags)</li>
  </ol>
  <p>Has anyone had any experience with a similar query (or a similar problem, of having the actual columns return by a query be dynamic?).  Any help is appreciated.</p>