SQL Same Unit Between Two Tables Needs Order Numbers in 1 Cell

SQL same unit between two tables needs order numbers in 1 cell

select 
    UnitId, 
    stuff((select ', ' + convert(varchar, WorkOrderNumber) 
           from tblWorkOrders t2 where t1.UnitId = t2.UnitId 
           for xml path('')),
          1,2,'') WorkOrderNumbers
from tblWorkOrders t1
group by UnitId

How to sort the words of a single cell in an SQL table?

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 :)

SQL and Coldfusion left join tables getting duplicate results as a list in one column

Look up FOR XML - that will let you pivot the order numbers.

Check this out

With Person AS
(
    Select 1 PersonId, 'John' PersonName
    Union Select 2, 'Jane'
),
Orders As
(
    Select 1 OrderId, 1 PersonId, Convert (DateTime, '1/1/2011') OrderDate
    Union Select 2, 1 , Convert (DateTime, '1/2/2011')
    Union Select 3, 1 , Convert (DateTime, '1/5/2011')
    Union Select 4, 1 , Convert (DateTime, '1/7/2011')
    Union Select 5, 1 , Convert (DateTime, '1/9/2011')
    Union Select 6, 2 , Convert (DateTime, '1/2/2011')
    Union Select 7, 2 , Convert (DateTime, '1/5/2011')
    Union Select 8, 2 , Convert (DateTime, '1/7/2011')
)
Select PersonId, 
(
    Select STUFF((SELECT  ', ' + cast(O.OrderId as nvarchar)
    FROM Orders O
    Where 1=1
        And O.PersonId = Person.PersonId
    FOR XML PATH('')), 1, 1, '') 
) OrderList
From Person

The output is

PersonId    OrderList
----------- -----------------------
1            1, 2, 3, 4, 5
2            6, 7, 8

(2 row(s) affected)

SQL Query to get aggregated result in comma separators along with group by column in SQL Server

You want to use FOR XML PATH construct:

select 
    ID, 
    stuff((select ', ' + Value 
           from YourTable t2 where t1.ID = t2.ID 
           for xml path('')),
          1,2,'') [Values]
from YourTable t1
group by ID

The STUFF function is to get rid of the leading ', '.

You can also see another examples here:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL and Coldfusion left join tables getting duplicate results as a list in one column

Unioning two tables with different number of columns

Add extra columns as null for the table having less columns like

Select Col1, Col2, Col3, Col4, Col5 from Table1
Union
Select Col1, Col2, Col3, Null as Col4, Null as Col5 from Table2

sql ORDER BY multiple values in specific order?

...
WHERE
   x_field IN ('f', 'p', 'i', 'a') ...
ORDER BY
   CASE x_field
      WHEN 'f' THEN 1
      WHEN 'p' THEN 2
      WHEN 'i' THEN 3
      WHEN 'a' THEN 4
      ELSE 5 --needed only is no IN clause above. eg when = 'b'
   END, id

SQL: How can I pick a cell value from one table as a condition to select another table

You can use subqueries with IN clause

Here is too a Version with two diemsnions, maybe this will help also

CREATE TABLE table1 ([id] varchar(2),[date] int)
GO

✓

    SELECT id, date FROM table1

    where date >= 2 and date <= 6

    and id IN (

    SELECT TOP 2 id FROM table1

    WHERE date >= 2 and date <= 6

    ORDER BY RAND(CHECKSUM(*) * RAND())
)

    ORDER BY date ASC

GO

id | date
:- | ---:

SELECT id, date FROM table1
WHERE EXISTS (SELECT 1
              FROM (

    SELECT TOP 2 id,[date] FROM table1

    WHERE date >= 2 and date <= 6

    ORDER BY RAND(CHECKSUM(*) * RAND())) AS table2
              WHERE  table1.[id] = table2.[id]
                        AND table1.[date] = table2.[date])
GO

id | date
:- | ---:

db<>fiddle here

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
    SELECT ARIDNR
    FROM YourTable
    GROUP BY ARIDNR
    HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

---

Related Topics