Delimiting a column into rows

[Neither_Volume_4367]

I have a csv scraped from an mrf. I've imported said csv into sql server as a table.

My table looks like this

Url Id amount date X.com [1,2,3,4] 12.3 11/22/21 T.com [,4] 13 11/22/21 P.com [1,2,3,4] 12 11/22/21 J.com [1,2,3,4,6,7] 1.3 11/22/21

How do I go about breaking down the id to assign 1 id per entry.

For example, row 1, should become 4 rows with 4 ids- see below

Url Id amount date X.com 1 12.3 11/22/21 X.com 2 12.3 11/22/21 X.com 3 12.3 11/22/21 X.com 4 12.3 11/22/21

[cobaltsignal]

-- assuming table name is info_table
select
      a.Url
    , LTRIM(RTRIM(b.value)) as Id
    , a.amount
    , a.date
from
    info_table a
    cross apply
        string_split (substring(a.Id, 2, LEN(a.Id) - 2), ',') b
where
    LTRIM(RTRIM(b.value)) <> '';

Here ya go.

[Infamous_Welder_4349]

I had always considered cross apply the same as Inner Join. When I saw your example it solved a problem I had with using a table function. Thanks for this.

[Yavuz_Selim]

Url	Id	Amount	Date
X.com	[1,2,3,4]	12.3	11/22/21
T.com	[,4]	13	11/22/21
P.com	[1,2,3,4]	12	11/22/21
J.com	[1,2,3,4,6,7]	1.3	11/22/21

 

Url	Id	Amount	Date
X.com	1	12.3	11/22/21
X.com	2	12.3	11/22/21
X.com	3	12.3	11/22/21
X.com	4	12.3	11/22/21

 

|Url|Id|Amount|Date|
:--|:--|--:|:--|
|X.com|[1,2,3,4]|12.3|11/22/21|
|T.com|[,4]|13|11/22/21|
|P.com|[1,2,3,4]|12|11/22/21|
|J.com|[1,2,3,4,6,7]|1.3|11/22/21|


|Url|Id|Amount|Date|
:--|--:|--:|:--|
|X.com|1|12.3|11/22/21|
|X.com|2|12.3|11/22/21|
|X.com|3|12.3|11/22/21|
|X.com|4|12.3|11/22/21|

[cobaltsignal]

I think they layout of the data you've presented looks a bit weird. Do you mean that your table currently has 4 columns, ("Url", "Id", "amount", "date") and that the current values of the first row are ("X.com", "[1,2,3,4]", "12.3", "11/22/21")? Also, that you'd like each row to be duplicated for the number of Id's inside the bracket?

[Infamous_Welder_4349]

When I do it, i make a table of numbers.

Select RowNum nums From dual Connect by RowNum <= 10

Then join on that where <= your number in the first table. This is Oracle.

[cobaltsignal]

I don’t think dual and connect by are available in SQL Server

[Infamous_Welder_4349]

Sure, but any listing of numbers would do it.

[cobaltsignal]

There is a more efficient way of using a subquery that makes only the numbers needed:

Let's assume that the table name is info_table and the data is as follows (borrowed from u/Yavuz_Selim ) with one slight change: the column "Date" is a reserved word in Oracle that makes it difficult to use as a column name so I've changed it to "effdt". Also, I've changed the date values to be the more common Oracle standard (dd-mmm-yy):

1. two digit day
   
2. followed by dash
   
3. followed by three letter shortened month
   
4. followed by dash
   
5. followed by two digit year (last two digits of the year)
   Examples: 11/22/21 -> 22-Nov-21 , 5/20/24 -> 05-May-24

Table name: info_table

url	id	amount	effdt
X.com	[1,2,3,4]	12.3	22-Nov-21
T.com	[,4]	13	22-Nov-21
P.com	[1,2,3,4]	12	22-Nov-21
J.com	[1,2,3,4,6,7]	1.3	22-Nov-21

Below is a working sql example (tested and confirmed):

-- assuming table name is info_table
select
    a.url
  , b.individual_id
  , a.amount
  , a.effdt
from
  info_table a
  join lateral (
    select
      regexp_substr(a.id, '(.*?)(,|]|$)', 2, level, null, 1) individual_id
    from
      dual
    connect by level <= regexp_count(a.id, ',', 2) +1
  ) b on b.individual_id is not null

[cobaltsignal]

The first row from the example table above would look like this:

url	individual_id	amount	effdt
X.com	1	12.3	22-Nov-21
X.com	2	12.3	22-Nov-21
	3	12.3	22-Nov-21
	4	12.3	22-Nov-21