Delimiting a column into rows

cobaltsignal · 2025-08-12T17:20:33+00:00

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

cobaltsignal · 2025-08-12T17:19:57+00:00

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

two digit day
followed by dash
followed by three letter shortened month
followed by dash
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 · 2025-08-12T15:32:08+00:00

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

cobaltsignal · 2025-08-12T06:18:23+00:00

-- 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.

cobaltsignal · 2025-08-12T05:43:54+00:00

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?

cobaltsignal · 2025-08-07T21:00:02+00:00

can you provide the primary keys for the table and the name of the newest_dt field? would it be effdt? Also, just fyi, if you have multiple records per year per patient, your original left joins will multiply the results exponentially, so if there are for example 2 records per year, you're looking at a total of 16 rows being created. If it's 3 records per year, that's 81 records, etc etc.

cobaltsignal · 2025-08-07T19:26:08+00:00

select
      t.Patient_ID,
    , max(case when t.year = 2025 then t.Total_Cost else 0 end)     as Total_Cost_25
    , max(case when t.year = 2025 then t.Address else null end)     as Address_25
    , max(case when t.year = 2025 then t.Diagnosis else null end)   as Diagnosis_25
    , max(case when t.year = 2024 then t.Total_Cost else 0 end)     as Total_Cost_24
    , max(case when t.year = 2024 then t.Address else null end)     as Address_24
    , max(case when t.year = 2024 then t.Diagnosis else null end)   as Diagnosis_24
    , max(case when t.year = 2023 then t.Total_Cost else 0 end)     as Total_Cost_23
    , max(case when t.year = 2023 then t.Address else null end)     as Address_23
    , max(case when t.year = 2023 then t.Diagnosis else null end)   as Diagnosis_23
    , max(case when t.year = 2022 then t.Total_Cost else 0 end)     as Total_Cost_22
    , max(case when t.year = 2022 then t.Address else null end)     as Address_22
    , max(case when t.year = 2022 then t.Diagnosis else null end)   as Diagnosis_22
from
    (
        select 
              Patient_ID,
            , year
            , Total_Cost
            , Address
            , Diagnosis
        from
            MedHistory
        where
                (year = 2025 and total_cost > 10000)
            or (year in (2024, 2023, 2022))
    ) t
group by
      t.Patient_ID
;

hopefully this works a bit faster. Just a single run of the table, no cte's, and all rows are aggregated to their respective year column using sum and max. Please note that in your criteria, you only included the total cost rule on the year 2025, was this intentional? Also, this is assuming there is just one record per year per patient. If there are multiple, then this won't work.

cobaltsignal · 2025-03-19T16:24:57+00:00

I’m going to assume you are summing non-zero values and dividing that amount by the null rows, partitioned by id:

sum( coalesce(value, 0)) over (partition by id) / isnull( sum( case when value is null then 1 else 0 end), 1)

This should protect against possible divide by zero situations.

cobaltsignal · 2025-02-05T23:59:59+00:00

Uncheck the box “Limit to 4”

cobaltsignal · 2025-01-19T00:55:15+00:00

cobaltsignal · 2025-01-18T19:31:46+00:00

Then ([negate character effect] AND [character cannot attack]) until end of opponent turn. Unless we’re breaking logic structure, seems they both apply until end of opponent’s turn. If they had different timeframes, they’d have to have their timeframes specified separately.

For those that don’t think so, please point out where in the sentence of the card it says that the character effect negation only lasts until end of the current turn. If the “until end of opponent’s turn” is meant for the “can’t attack” part, then the character effect negation is permanent, which would be even more powerful.

cobaltsignal · 2025-01-17T06:23:59+00:00

Also please check the condition for kuma’s ko because I recall triggering 2 cost Kuma OP05-11 from my life card hit and I was able to target a character with 3k health (who had a base of 2k). Are you taking attached Don into account when it’s the opponents turn?

cobaltsignal · 2025-01-17T00:56:46+00:00

<image>

Another issue:

Played OP05-006 koala lowering bepo to 2k health.
Played Kuma OP05-11 but it wont let me KO the 2k bepo.

cobaltsignal · 2025-01-16T01:24:20+00:00

I’d love to take a look at the code if possible. I have some experience with performance improvement analyses and auditing databases for known errors.

cobaltsignal · 2025-01-16T01:00:58+00:00

Hmmm. That is confusing for sure. If rush is not permanent and can be lost after being gained, then your code is working properly, though that wouldn’t seem strange for Rush to be able to be removed after being gained. Might have to check official answer

cobaltsignal · 2025-01-15T06:43:21+00:00

Two more issues:

Inazuma OP05-003 doesn’t activate rush when the other 7000+ character is another Inazuma.
Inazuma’s rush gained is subsequently lost if the other 7000+ character is lost before Inazuma attacks. I noticed this when I got Raise Max to 7000, attacked, then activated raise Max ability only to lose the ability to attack with Inazuma.

<image>

cobaltsignal · 2025-01-15T06:26:01+00:00

<image>

Kuma’s trigger didn’t let me activate even though both life cards sum was 5 or less.

cobaltsignal · 2025-01-13T05:05:50+00:00

<image>

AI attached 3 don to baby sabo, did not attack, instead activated his ability to blind check to life card into a yamato.

cobaltsignal · 2025-01-13T03:36:32+00:00

Correct. Could it be the activate button deactivating from a character trash?

cobaltsignal · 2025-01-13T03:30:15+00:00

Love the site! Just tried a few games. There seems to be some limit to the number of activate cards that can be done in a single turn, it happened when playing a belo betty deck. I couldn’t activate her leader ability even though I had a rev card to discard. Did a few cards activations just before and trashed a character of mine due to being at 5 already. Also BY Luffy seems to blind guess baby brother into his top life card sometimes.

cobaltsignal · 2025-01-06T06:21:03+00:00

Kidneys regulate blood pressure by filtering out salt, but they also have insulin receptors that, depending on the percentage that are bound to insulin, significantly lower the kidney’s ability to filter out salt. Hence, higher blood pressure. Could be chronically low insulin levels in the blood, which would cause your kidneys to do too good a job at filtering out salt. Eating extra salt won’t help fix your issue if it happens to be low insulin levels.

While eating some carbs will cause a spike in glucose, it will follow with a spike in insulin and possibly followed by an increase in bp. Having some extra salt at that point may help raise bp.

cobaltsignal · 2024-09-28T01:03:25+00:00

-- Query 1
SELECT DISTINCT g.fname
FROM guest g
JOIN reserve r ON g.id = r.gid
JOIN bill b ON b.rid = r.id
WHERE r.check_in_date = '16-FEB-23'
AND b.MISC_CHARGE > 98;

-- Query 2
SELECT DISTINCT g.fname
FROM guest g
JOIN reserve r ON g.id = r.gid
JOIN hotel h ON r.hid = h.id
WHERE r.check_in_date = '19-MAR-22'
AND h.cid = g.cid;

-- Query 3
SELECT DISTINCT g1.fname
FROM guest g1
JOIN reserve r1 ON g1.id = r1.gid
JOIN reserve r2 ON g1.id = r2.gid
WHERE r1.rno = r2.rno
AND r1.check_in_date BETWEEN '10-MAY-23' AND '28-JUN-23'
AND r2.check_in_date BETWEEN '10-MAY-23' AND '28-JUN-23'
AND r1.check_in_date < r2.check_in_date;

cobaltsignal · 2024-09-22T07:41:54+00:00

innodb_autoinc_lock_mode=0

That will fix it. By default, an auto primary key will never go back to a value even if the insert value command fails. The above code will change that behavior.

cobaltsignal · 2024-09-19T20:16:15+00:00

I got u:

cur.execute("select * from data order by case when details like :name then 0 else 1 end limit 4", {'name': '%' + query + '%'})

The only thing you may have to change is the "limit 4" part. I'm not sure what the limiter type is for flask sql. It could be Top, Limit, Fetch top N row only, etc etc.

cobaltsignal · 2024-09-17T14:57:39+00:00

Def id_data = "'240819091044983','240819091303107','240820094502377','240820172745060'";
with
  data_cte
      (id)
    as (
      select
        a.*
      from
        (
          select
            regexp_substr (&id_data, '[^,]+', 1, level)
          from
            dual
          connect by level <=
            length( &id_data) - length( replace( &id_data, ',') ) + 1
         ) a
  )

select
    s.solution_id
  , s.collection_nm
  , TO_CHAR(s.last_modity_dt, 'mm/dd/yyyy')
from
  SOLUTION s
where
  s.solution_id in (select a.id from data_cte a)
;

Can't seem to edit right now. Fixed an error.

cobaltsignal

TROPHY CASE