Split string value to multiple columns

Question

Data from 2 tables is joined and displayed, while displaying I want to split the value of column 'spec 219' to 219 columns

SELECT * FROM

(SELECT * 
FROM [CTMS].[dbo].[NUTQCDATA]
WHERE reg_tm >= '20200320000000' and reg_tm <= '20200320235959') a

LEFT JOIN

(SELECT *
FROM [CTMS].[dbo].[MES_IF_ORDER_TRK]) b

ON a.body_no = b.body_no;

Result

.-----------.------------.---------.--------.---------.----------------.----------------.----------.-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------.
| device_id |  body_no   | snetchk | stn_cd | stn_seq |     stn_dt     |    reg_date    | use_flag |                                                                                      spec219                                                                                      |
:-----------+------------+---------+--------+---------+----------------+----------------+----------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------:
| DIAP133   | B4B 078913 |       1 |   3510 |      58 | 20200320020026 | 20200320020300 |        1 | L4RAG 5UJ423A8 P EE13 442BACN21 5116CNT8T3C 1 E212 1 1 32 CBA R 1R1 FA 121 4 5UR2 25NC 2S 12 1S HMC2 3 3 L11 2411 8 T 11 P P B1 GG F C2 BO CG 13 3 ON 1F 18 G R 3 N 4B1D L A C 11 |
'-----------'------------'---------'--------'---------'----------------'----------------'----------'-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'

Note, that there are 219 chars including blank space. Blank space also to be assigned in column.

Requirements:

Result should be all columns in Table 1 + all columns in table 2 ('spec 219' to be split to 219 columns)
Assign name for each newly created columns of 'spec 219', Eg: Column 1 = MODEL YEAR, Column 2 = MODEL,...Column 219 = 'SPECIAL DISTRIBUT

Does spec219 column always contain 219 characters, icluding spaces (the data in the question has 178 characters)? — Zhorov
– Zhorov, Commented May 20, 2020 at 10:14
Side note: Bad habits to kick : using table aliases like (a, b, c) or (t1, t2, t3) — Thom A
– Thom A ♦, Commented May 20, 2020 at 10:20

Zhorov · Accepted Answer · 2020-05-21 05:46:53Z

1

You may try with the following statement, uisng OUTER APPLY and VALUES table value constructor to get the expected results:

SELECT * 
FROM [CTMS].[dbo].[NUTQCDATA] a
LEFT JOIN [CTMS].[dbo].[MES_IF_ORDER_TRK] b ON a.body_no = b.body_no
OUTER APPLY (VALUES (
    SUBSTRING(b.[spec 219], 1, 1),
    SUBSTRING(b.[spec 219], 2, 1),
    ...,
    SUBSTRING(b.[spec 219], 219, 1)
)) v ([MODEL YEAR], [MODEL], ..., [SPECIAL DISTRIBUT])
WHERE a.reg_tm >= '2020-03-20T00:00:00' AND a.reg_tm <= '2020-03-20T23:59:59'

edited May 21, 2020 at 5:46

answered May 20, 2020 at 14:39

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user27 Over a year ago

Thanks! I get below error. I use SQL Server 2012. Msg 195, Level 15, State 10, Line 5 'SUSBSTRING' is not a recognized built-in function name.

Zhorov Over a year ago

Also it's always good to use unambiguous date format - yyyymmdd or yyyy-mm-ddThh:mm:ss. Thanks.

user27 Over a year ago

nice! This also works. Which is the best method the one posted by @Máté Farkas or your answer. how would we measure.. by time taken to execute. could you guide.

Zhorov Over a year ago

@user27 Weel, it's your choise, but you should know, that PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output and PIVOT runs aggregations (from documentation). VALUES table value constructor constructs a table. My personal choise for this specific case is the VALUES approach.

Máté Farkas · Accepted Answer · 2020-05-20 12:00:34Z

Here is your query for that. It can have errors because I don't know which column belong to which table (a or b). I simplified your query and give the logic of splitting that fields to columns:

SELECT device_id, body_no, snetchk, stn_cd, stn_seq, stn_dt, reg_date, use_flag,
       [1], [2], [3], [4], [5], [6], [7], [8], [9], [10] --, etc
FROM (
    SELECT a.*, spec219.[id], spec219.[char]
    FROM [CTMS].[dbo].[NUTQCDATA] a
    LEFT JOIN [CTMS].[dbo].[MES_IF_ORDER_TRK] b ON a.body_no = b.body_no
    OUTER APPLY (
        select nums.id, substring(b.spec219, nums.id, 1) [char]
        from (
            select top (219) row_number() over(order by (select null)) id
            from sys.objects o1, sys.objects o2
            order by 1
        ) nums
    ) spec219
    WHERE a.reg_tm >= '20200320000000' and a.reg_tm <= '20200320235959'
) t
pivot(max([char]) for [id] in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10])) p

You need to finish this query to add the remaining columns and specify alias names after the select.

Collectives™ on Stack Overflow

Split string value to multiple columns

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related