Single query to split out data of one column, into two columns, from the same table based on different criteria [SQL]

I have the following data in a table, this is a single column shown from a table that has multiple columns, but only data from this column needs to be pulled into two column output using a query:

+----------------+--+
| DataText       |  |
| 1 DEC20 DDD    |  |
| 1 JUL20 DDD    |  |
| 1 JAN21 DDD    |  |
| 1 JUN20 DDD500 |  |
| 1 JUN20 DDD500 |  |
| 1 JUN20DDDD500 |  |
| 1 JUN20DDDD500 |  |
| 1 JUL20 DDD800 |  |
| 1 JUL20 DDD800 |  |
| 1 JUL20DDDD800 |  |
| 1 JUL20DDDD400 |  |
| 1 JUL20DDDD400 |  |
+----------------+--+

Required result: distinct values based on the first 13 characters of the data, split into two columns based on “long data”, and “short data”, BUT only giving the first 13 characters in output for both columns:

+-------------+-------------+
| ShortData   | LongData    |
| 1 DEC20 DDD | 1 JUN20 DDD |
| 1 JUL20 DDD | 1 JUN20DDDD |
| 1 JAN21 DDD | 1 JUL20 DDD |
|             | 1 JUL20DDDD |
+-------------+-------------+

Something like:

Select
(Select DISTINCT LEFT(DataText,13)
From myTable)
Where LEN(DataText)=13) As ShortData
 ,
(Select DISTINCT LEFT(DataText,13)
From myTable)
Where LEN(DataText)>13) As LongData

I would also like to query/”scan” the table only once if possible. I can’t get any of the SO examples modified to make such a query work.

Answer

This is quite ugly, but doable. As a starter, you need a column that defines the order of the rows – I assumed that you have such a column, and that is called id.

Then you can select the distinct texts, put them in separate groups depending on their length, and finally pivot:

select
    max(case when grp = 0 then dataText end) shortData,
    max(case when grp = 1 then dataText end) longData
from (
    select 
        dataText, 
        grp,
        row_number() over(partition by grp order by id) rn
    from (
        select
            id,
            case when len(dataText) <= 13 then 0 else 1 end grp,
            substring(dataText, 1, 13) dataText 
        from (select min(id) id, dataText from mytable group by dataText) t
    ) t
) t
group by rn

If you are content with ordering the records by the string column itself, it is a bit simpler (and, for your sample data, it produces the same results):

select
    max(case when grp = 0 then dataText end) shortData,
    max(case when grp = 1 then dataText end) longData
from (
    select 
        dataText, 
        grp,
        row_number() over(partition by grp order by dataText) rn
    from (
        select distinct
            case when len(dataText) <= 13 then 0 else 1 end grp,
            substring(dataText, 1, 13) dataText 
        from mytable
    ) t
) t
group by rn

Demo on DB Fiddle:

shortData   | longData     
:---------- | :------------
1 DEC20 DDD | 1 JUL20 DDD80
1 JAN21 DDD | 1 JUL20DDDD40
1 JUL20 DDD | 1 JUL20DDDD80
null        | 1 JUN20 DDD50
null        | 1 JUN20DDDD50