If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
) [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
), 2, 1000) [Students]
FROM dbo.Students ST2
You can create a new writeable instance of HttpValueCollection
by calling System.Web.HttpUtility.ParseQueryString(string.Empty)
, and then use it as any NameValueCollection
. Once you have added the values you want, you can call ToString
on the collection to get a query string, as follows:
NameValueCollection queryString = System.Web.HttpUtility.ParseQueryString(string.Empty);
queryString.Add("key1", "value1");
queryString.Add("key2", "value2");
return queryString.ToString(); // Returns "key1=value1&key2=value2", all URL-encoded
The HttpValueCollection
is internal and so you cannot directly construct an instance. However, once you obtain an instance you can use it like any other NameValueCollection
. Since the actual object you are working with is an HttpValueCollection
, calling ToString method will call the overridden method on HttpValueCollection
, which formats the collection as a URL-encoded query string.
After searching SO and the web for an answer to a similar issue, this is the most simple solution I could find.
.NET Core
If you're working in .NET Core, you can use the Microsoft.AspNetCore.WebUtilities.QueryHelpers
class, which simplifies this greatly.
https://docs.microsoft.com/en-us/dotnet/api/microsoft.aspnetcore.webutilities.queryhelpers
Sample Code:
const string url = "https://customer-information.azure-api.net/customers/search/taxnbr";
var param = new Dictionary<string, string>() { { "CIKey", "123456789" } };
var newUrl = new Uri(QueryHelpers.AddQueryString(url, param));
Best Answer
Although John Sansom's solution works, there's another way to do this, without having to use a potentially inefficient scalar valued UDF. In the SSRS report, on the parameters tab of the query definition, set the parameter value to
In your query, you can then reference the value like so: