For example:
Need to sort the above as,
So there's nothing that you can do natively. If you want to sort the values just as a return value, i.e. not update the database itself, you can transform the results with either a stored procedure or perhaps a view.
So let's construct an answer.
Let's just assume you want to do it visually, for a single row. If you have SQL 2016 you can use STRING_SPLIT but SQL Fiddle doesn't, so I used a common UDF fnSplitString
http://sqlfiddle.com/#!6/7194d/2
SELECT value
FROM fnSplitString('Pillars 101 in an apartment', ' ')
WHERE RTRIM(value) <> '';
That gives me each word, split out. What about ordering it?
SELECT value
FROM fnSplitString('Pillars 101 in an apartment', ' ')
WHERE RTRIM(value) <> ''
ORDER BY value;
And if I want to do it for each row in the DB table I have? http://sqlfiddle.com/#!6/7194d/8
SELECT split.value
FROM [Data] d
CROSS APPLY dbo.fnSplitString(IsNull(d.Value,''), ' ') AS split
WHERE RTRIM(split.value) <> ''
ORDER BY value;
That's sort of helpful, except now all my words are jumbled. Let's go back to our original query and identify each row. Each row probably has an Identity column on it. If so, you've got your grouping there. If not, you can use ROW_NUMBER, such as:
SELECT
ROW_NUMBER() OVER(ORDER BY d.Value) AS [Identity] -- here, use identity instead of row_number
, d.Value
FROM [Data] d
If we then use this query as a subquery in our select, we get:
http://sqlfiddle.com/#!6/7194d/21
SELECT d.[Identity], split.value
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY d.Value) AS [Identity] -- here, use identity instead of row_number
, d.Value
FROM [Data] d
) d
CROSS APPLY dbo.fnSplitString(IsNull(d.Value,''), ' ') AS split
WHERE RTRIM(split.value) <> ''
ORDER BY d.[Identity], value;
This query now sorts all rows within each identity. But now you need to reconstruct those individual words back into a single string, right? For that, you can use STUFF. In my example I use a CTE because of SQL Fiddle limitations but you could use a temp table, too.
WITH tempData AS (
SELECT d.[Identity], split.value
FROM
(
SELECT
ROW_NUMBER() OVER(ORDER BY d.Value) AS [Identity] -- here, use identity instead of row_number
, d.Value
FROM [Data] d
) d
CROSS APPLY dbo.fnSplitString(IsNull(d.Value,''), ' ') AS split
WHERE RTRIM(split.value) <> ''
)
SELECT grp.[Identity]
, STUFF((SELECT N' ' + [Value] FROM tempData WHERE [Identity] = grp.[Identity] ORDER BY Value FOR XML PATH(N''))
, 1, 1, N'')
FROM (SELECT DISTINCT [Identity] FROM tempData) AS grp
Here's the end result fiddle: http://sqlfiddle.com/#!6/7194d/27
As expressed in comments already, this is not a common case for SQL. It's an unnecessary burden on the server. I would recommend pulling data out of SQL and sorting it through your programming language of choice; or making sure it's sorted as you insert it into the DB. I went through the exercise because I had a few minutes to kill :)