SQL Server: Dynamic columns returned from query

Question

I've got a fairly complex SQL query running against data.stackexchange.com. The query can be found at this link (and pasted below).

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 > @RepLimit
AND Users.Location = @Location

In the above query, a few things happen:

1. On data.stackexchange.com, they generate a form field for me to enter the data that I have DECLAREd at the top (in this case, Location and RepLimit).
2. 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.

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:

1. 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.
2. I'd like the list of tags to be provided as either a comma-separated list in a new form element (being DECLAREd at the top, like Location and RepLimit), or as 5 separate form fields (limiting the query to up to 5 different tags)

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.

Answer 1

As for your first question here is a way to optimize the query:

DECLARE @Location varchar(128) = ##Location:string##
DECLARE @RepLimit int = ##RepLimit:int##

SELECT Users.DisplayName,
       Users.Id,
       Users.WebsiteUrl,
       Users.Reputation,
       sum(case when Tags.Id = 3 then 1 else 0 end) as JavascriptCount,
       sum(case when Tags.Id = 5 then 1 else 0 end) as PhpCount,
       sum(case when Tags.Id = 820 then 1 else 0 end) as jQueryCount,
       sum(case when Tags.Id = 21 then 1 else 0 end) as MySqlCount,
       sum(case when Tags.Id = 1386 then 1 else 0 end) as AndroidCount,
       sum(case when Tags.Id IN (58338, 81106, 92809, 7003) then 1 else 0 end) as IosCount

FROM Users
    JOIN Posts ON Posts.OwnerUserId = Users.Id
        JOIN PostTags ON Posts.ParentId = PostTags.PostId
           JOIN Tags ON PostTags.TagId = Tags.Id

WHERE Users.Reputation > @RepLimit AND Posts.PostTypeId = 2
AND Users.Location = @Location

GROUP BY
       Users.DisplayName,
       Users.Id,
       Users.WebsiteUrl,
       Users.Reputation

As for your second question I - as knowing nothing about those stackexchange forms - am afraid I cannot help. But since you are mentioning comma separated list, you could utilize SQL's CHARINDEX() function in order to lookup Tags.Id in the parameter. Sth like this:

case when CHARINDEX(','+convert(varchar,Tags.Id)+',',','+@List1+',') <> 0 then 1 else 0 end