SQL Server Create a table with the values of 3 strings [duplicate]

e4yzc0pl 于 12个月前发布在其他

关注(0)|答案(2)|浏览(157)

This question already has answers here:

Result order of string_split? (3 answers)
Closed last month.

In SQL (T-SQL) I have 3 lists of numbers, in the form of strings (I take them from an Excel file):

DECLARE @article_list VARCHAR(MAX) = 1060226, 20653, 39986, 1041443, 1060303, 1057353, 1050423, 1057354, 1059541, 1050570';
DECLARE @store_list VARCHAR(MAX) = '1046, 1046, 1046, 1046, 1107, 1107, 1107, 1107, 1107, 1107';
DECLARE @order_list VARCHAR(MAX) = '1012406, 2008322, 2008322, 2008322, 2011092, 1020199, 1020199, 1020199, 1020176, 2010731';

And I want to create a table like this:

DECLARE @rows_to_delete TABLE (rn INT IDENTITY(1,1), art INT, ord INT, stor INT);

Now I need to load the values of the lists, in the same order, into the table @rows_to_delete, like {1060226, 1046, 1012406}, {20653, 1046, 2008322}
(it's not JSON, I just want to describe the table rows..)

Any ideas?

sql-server

来源：https://stackoverflow.com/questions/77567028/create-a-table-with-the-values-of-3-strings

2条答案

按热度按时间

yhived7q1#

Here is a JSON based solution, using set based operations.

It will work starting from SQl Server 2016 onwards.

We are converting input strings into JSON arrays with guaranteed sequential order. OPENJSON() is converting JSON arrays into rectangular data sets with [Key] and [Value] columns.

SQL

-- DDL and sample data population, start
DECLARE @rows_to_delete TABLE (rn INT IDENTITY PRIMARY KEY, art INT, ord INT, stor INT);

DECLARE @article_list VARCHAR(MAX) = '1060226, 20653, 39986, 1041443, 1060303, 1057353, 1050423, 1057354, 1059541, 1050570'
    , @store_list VARCHAR(MAX) = '1046, 1046, 1046, 1046, 1107, 1107, 1107, 1107, 1107, 1107'
    , @order_list VARCHAR(MAX) = '1012406, 2008322, 2008322, 2008322, 2011092, 1020199, 1020199, 1020199, 1020176, 2010731';
-- DDL and sample data population, end

WITH art AS
(
    SELECT *
    FROM OPENJSON('[' + @article_list + ']') 
), ord AS
(
    SELECT *
    FROM OPENJSON('[' + @order_list + ']')  
), stor AS
(
    SELECT *
    FROM OPENJSON('[' + @store_list + ']')  
)
INSERT @rows_to_delete (art, ord, stor)
SELECT art.Value, ord.Value, stor.Value
FROM art 
    INNER JOIN ord ON ord.[Key] = art.[Key]
    INNER JOIN stor ON stor.[Key] = art.[Key]
ORDER BY art.[Key];

-- test
SELECT * FROM @rows_to_delete ORDER BY rn;

Output

rn	art	ord	stor
1	1060226	1012406	1046
2	20653	2008322	1046
3	39986	2008322	1046
4	1041443	2008322	1046
5	1060303	2011092	1107
6	1057353	1020199	1107
7	1050423	1020199	1107
8	1057354	1020199	1107
9	1059541	1020176	1107
10	1050570	2010731	1107

赞(0）回复(0）举报 12个月前

6jjcrrmo2#

If looking for a little more brevity.

DECLARE @article_list VARCHAR(MAX) = '1060226, 20653, 39986, 1041443, 1060303, 1057353, 1050423, 1057354, 1059541, 1050570'
      , @store_list   VARCHAR(MAX) = '1046, 1046, 1046, 1046, 1107, 1107, 1107, 1107, 1107, 1107'
      , @order_list   VARCHAR(MAX) = '1012406, 2008322, 2008322, 2008322, 2011092, 1020199, 1020199, 1020199, 1020176, 2010731';

 Insert Into @rows_to_delete
 Select art  = A.Value
       ,ord  = O.Value
       ,stor = S.Value
  From OPENJSON('[' + @article_list + ']')  A
  Join OPENJSON('[' + @store_list + ']')    S on A.[Key]=S.[key]
  Join OPENJSON('[' + @order_list + ']')    O on A.[Key]=O.[key]

赞(0）回复(0）举报 12个月前

我来回答

SQL Server Create a table with the values of 3 strings [duplicate]

2条答案

相关问题

热门标签

最新问答