If the output of my query is England, America, India.
Is it possible to display the query result like England, America and India
Here is my query:
Select stuff( ( Select Distinct ',' + Country as[text()]
from tbl_Country
where OrderNo in (Select Max(OrderNo) from Tbl_Literature where ID='AB123'
Group By OrderNo) for XML path('') ),1,1,'')as Result
from dbo.Tbl_Master as TP
where
TP.OrderNo IN (select MAX(order No) from dbo.Tbl_Master where TP.ID = 'AB123')
Assuming you are referring to the string aggregation hack done to compensate for SqlServer's lack of GROUP_CONCAT, you can use this ingenious hack here:
WITH myStuffedCte AS
(SELECT
STUFF((
SELECT ',' + Name
FROM Country
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SomeCol
)
SELECT ISNULL(reverse(replace(STUFF(reverse(SomeCol),
charindex(',',reverse(SomeCol)),0,'#'),'#,',' dna ')),
SomeCol)
FROM myStuffedCte;
Updated example of Stuff with a GROUPING criteria
In order to use STUFF in a GROUP BY fashion, you can manually filter the inner STUFF subquery based on an outer query's rows with a WHERE clause.
WITH myStuffedCte AS
(
SELECT p.FirstName, p.LastName,
STUFF((
SELECT ',' + c.Name
FROM Country c
INNER JOIN PersonCountryVisits pcv
ON pcv.CountryId = c.CountryId
WHERE pcv.PersonId = p.PersonID -- Manual Filter step
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS SomeCol
FROM Person p
)
SELECT FirstName, LastName,
ISNULL(reverse(replace(STUFF(reverse(SomeCol),
charindex(',',reverse(SomeCol)),0,'#'),'#,',' dna ')),
SomeCol) AS CountriesVisited
FROM myStuffedCte;
Updated Sql Fiddle example here
SqlClr Alternative to Stuff, IFF you can use SqlClr on your SqlServer:
If you have access to a Sql-Clr enabled SqlServer, you can ditch both of these hacks and instead, replace STUFF / FOR XML with a GROUP_CONCAT simulation of SqlUserDefinedAggregate like this one, and at the same time do the last comma replacement with a SqlFunction UDF, like this:
[SqlFunction]
public static SqlString ReplaceLast(SqlString source, SqlString find, SqlString replace,
int countFromRight)
{
var splits = source.Value
.Split(new[] {find.Value}, StringSplitOptions.None);
var replacePoint = splits.Length - countFromRight;
return (replacePoint > 0)
? new SqlString(
string.Join(replace.Value,
new[] { string.Join(find.Value, splits.Take(replacePoint)) }
.Union(splits.Skip(replacePoint))))
: source;
}
In which case the sample query becomes the entirely more palatable:
SELECT FirstName, LastName,
dbo.ReplaceLast(dbo.GROUP_CONCAT(c.Name, ','), ',', ' and ', 1)
FROM Person p
INNER JOIN PersonCountryVisits pcv
ON pcv.PersonId = p.PersonID
INNER JOIN Country c
ON pcv.CountryId = c.CountryId
GROUP BY FirstName, LastName;
I've put up a Gist on GitHub which provides all the salient bits to create the GROUP_CONCAT and ReplaceLast SqlClr functions. (SqlFiddle not available - No SQLClr ...)
As you have mentioned the stuff command, I assume you are using FOR XML PATH('') to concatenate your items, and STUFF to remove the first delimiter, e.g.
SELECT STUFF(( SELECT ', ' + Country
FROM InnerTable AS i
WHERE i.ID = o.ID
ORDER BY Country
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM OuterTable AS o;
If this is the case then you can use ROW_NUMBER() to identify the last row (by using the inverse ordering to the subquery), and put AND in there:
SELECT STUFF(( SELECT ', ' + CASE WHEN ROW_NUMBER() OVER(ORDER BY Country DESC) = 1
THEN 'and '
ELSE ''
END + Country
FROM InnerTable AS i
WHERE i.ID = o.ID
ORDER BY Country ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '')
FROM OuterTable AS o;
n.b. The above will result in a string serial comma (e.g. "England, America, and India"), but it won't require much manipulation to change this.
SIMPLE EXAMPLE
SELECT STUFF(( SELECT ', ' + CASE WHEN ROW_NUMBER() OVER(ORDER BY Country DESC) = 1
THEN 'and '
ELSE ''
END + Country
FROM (VALUES ('America'), ('England'), ('India')) AS i (Country)
ORDER BY Country ASC
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, '');
Try this:
Declare @String varchar(100)
set @String = 'England, America, India'
SELECT reverse(replace(STUFF(reverse(@String),
charindex(',',reverse(@String)),0,'#'),'#,',' dna '))
Use SUBSTRING(). Refer this for more String functions
DECLARE @s AS VARCHAR(100)
SET @s = 'England, America, India'
SELECT SUBSTRING(@s, 0, LEN(@s) - CHARINDEX(',', REVERSE(@s)) + 1 )
+ ' and'
+ SUBSTRING(@s, LEN(@s) - CHARINDEX(',', REVERSE(@s)) + 2, len(@s))
