Shell Script to Normalize the data

Using Miller (mlr) to "explode" the comma-delimited subfields of the 3rd field in the pipe-delimited input:

$ mlr --nidx --fs pipe nest --evar , -f 3 file
key1|desc field|item1|extra field
key1|desc field|item2|extra field
key1|desc field|item3|extra field
key1|desc field|item4|extra field
key2|desc field|item1|extra field
key2|desc field|item2|extra field
key2|desc field|item3|extra field

using awk:

awk -F'|' '
{
  split($3, items, ","); 
  for (i=1; i in items; i++) 
    print $1 "|" $2 "|" items[i] "|" $4
}'

This will split the 3rd column into an array named items delimited by ,. For each item in items it will print a new line using the value of each other column from the original line.

Here's a Perl approach which assumes that you never have | inside any of the items:

$ perl -F'\|' -ane '@items = split(/,/,$F[2]); print join("|",@F[0,1],$_,@F[3..$#F]) for @items' file
key1|desc field|item1|extra field
key1|desc field|item2|extra field
key1|desc field|item3|extra field
key1|desc field|item4|extra field
key2|desc field|item1|extra field
key2|desc field|item2|extra field
key2|desc field|item3|extra field

The -n flag tells perl to read its input line by line and apply the script given by -e to each line. The -a flag tells perl to behave like awk and split its input on the character given by -F and save the result into the array @F.

Then, in the script, we split the third field (numbering starts at 0, so $F[2] is the third field) on commas and save the result into the array @items. Then, for each element of the @items array, we print the result of joining the first two fields with a | (which gives us back the beginning of the original input line), then the current item (the value of the special variable $_), and then the rest of the array. All joined with |.

Using any awk:

$ cat tst.awk
BEGIN { FS=OFS="|" }
{
    n = split($3, f, ",")
    for ( i=1; i<=n; i++ ) {
        $3 = f[i]
        print
    }
}

$ awk -f tst.awk file
key1|desc field|item1|extra field
key1|desc field|item2|extra field
key1|desc field|item3|extra field
key1|desc field|item4|extra field
key2|desc field|item1|extra field
key2|desc field|item2|extra field
key2|desc field|item3|extra field

Passing the field position 3 as an argument to a shell script calling awk as the OP requested in a comment:

$ cat tst.sh
#!/usr/bin/env bash

pos=$1
shift

awk -v pos="$pos" '
    BEGIN { FS=OFS="|" }
    {
        n = split($pos, f, ",")
        for ( i=1; i<=n; i++ ) {
            $pos = f[i]
            print
        }
    }
' "${@:--}"

$ ./tst.sh 3 file
key1|desc field|item1|extra field
key1|desc field|item2|extra field
key1|desc field|item3|extra field
key1|desc field|item4|extra field
key2|desc field|item1|extra field
key2|desc field|item2|extra field
key2|desc field|item3|extra field

here's a very short python3 solution; put it in a file (say normalize.py), make it executable (chmod 755 normalize.py) and run it on your input (./normalize.py < your_input_data.file)

#!/usr/bin/env python3
from sys import stdin

for line in stdin:
  key, desc, items, extra = line.split("|")
  for item in items.split(","):
    if item:
      print(f"{key}|{desc}|{item}|{extra.strip()}")

Assumptions: no | anywhere except as separators of columns, exactly 4 columns per input line, and within the items list, no commas except for these that separate the items.

Note that your question basically screams that you would probably want a better relational data model than this; in a classical relational database design, you'd make two tables out of this:

1. table "itemtypes" with columns 'key' (a unique, primary key), 'description' and 'extra'
2. table "items" with column 'item' and column 'type_key', which references 'itemtypes.key'.

Using sed, assuming the exact input with no surprises:

$ sed -E 's/\|([^|]*,[^|]*)\|/|"\{\1\}"|/;s/^|$/"/g;s/(.*)/printf "%s\n" \1/e' input.txt
key1|desc field|item1|extra field
key1|desc field|item2|extra field
key1|desc field|item3|extra field
key1|desc field|item4|extra field
key2|desc field|item1|extra field
key2|desc field|item2|extra field
key2|desc field|item3|extra field

This is a silly answer and will fail spectacularly if the comma-separated items contain spaces, or if there are any quoted commas, or if you try to separate more than one field with commas. (And probably many other special cases I haven't thought of.)

Explanation:

• s/\|([^|]*,[^|]*)\|/|"\{\1\}"|/g: Replace any field x,y,z that contains a comma with "{x,y,z}".
• s/^|$/"/g: Put " at the beginning and end of each line. Now the rest of the line is quoted.
• s/(.*)/printf "%s\n" \1/e: Open another bash (or zsh, or sh) shell and call printf "%s\n" on the resulting line. The shell performs brace expansion on each line to turn it into multiple strings, one with each option. Then printf prints each string on its own line.

Using Raku (formerly known as Perl_6)

~$ raku -ne 'my @a = .split("|").map: *.split(",");  my @b;
             given @a.pairs { for .values {
                 when .key == 0  { @b = $_.value }  #(A);
                 when .value.elems > 1  { @b = [X] @b, .value }  #(B);
                 default { @b .= map( -> $b { $b, .value }) }
             }}; put $_ for @b;  #say "";'  file.txt

Above is an answer written in Raku, a member of the Perl-family of programming languages. Raku features case statements, shown here in the given/when format. These statements are used to create a recipient @b array, which is then output.

In the first statement the input line is split on the pipe-character, then each resulting element is mapped into and further split on commas. Saved into the @a array, this object is used in pair format, which provides .key (indexing) and .value functionality.

Each element is iterated through using a for loop, testing via the following when statements:

• #(A): Automatically add the first row to the recipient @b array. FYI, this means the simple code above won't expand multiple elements within the first column (so think of the first column as containing indivisible keys). Add a line-number to your datafile as column 1 to circumvent this limitation,
• #(B): When a column contains more than one element, take the @b array and [X] (meta) cross-product expand^[1]. Replace @b with this expansion.
• default: String-concatenate each cell's value onto the end of every element of the @b array.

Sample Input (OP's file with no-spaces and extra test line):

key1|desc_field|item1,item2,item3,item4|extra_field
key2|desc_field|item1,item2,item3|extra_field
key3|desc_field|item1,item2|itemA,itemB|extra_field

Sample Output (joining columns on spaces, uncomment say "" to add a blank line between input rows):

key1 desc_field item1 extra_field
key1 desc_field item2 extra_field
key1 desc_field item3 extra_field
key1 desc_field item4 extra_field

key2 desc_field item1 extra_field
key2 desc_field item2 extra_field
key2 desc_field item3 extra_field

key3 desc_field item1 itemA extra_field
key3 desc_field item1 itemB extra_field
key3 desc_field item2 itemA extra_field
key3 desc_field item2 itemB extra_field

To join columns on something else (e.g. | space-pipe-space), a fun and powerful way is to declare a new J infix operator, using it alone or in conjunction with the X cross-product operator:

~$ raku -ne 'BEGIN sub infix:<J> ($x, $y) { ($x,$y).join(" | ") }; 
             my @a = .split("|").map: *.split(",");  my @b;
             given @a.pairs { for .values {
                 when .key == 0 { @b = $_.value }  #(A);
                 when .value.elems > 1 { @b = @b  XJ  .value.cache }  #(B);
                 default { @b .= map( -> $b { $b   J  .value.cache }) }
             }}; put $_ for @b; say "";'  file.txt
key1 | desc_field | item1 | extra_field
key1 | desc_field | item2 | extra_field
key1 | desc_field | item3 | extra_field
key1 | desc_field | item4 | extra_field

key2 | desc_field | item1 | extra_field
key2 | desc_field | item2 | extra_field
key2 | desc_field | item3 | extra_field

key3 | desc_field | item1 | itemA | extra_field
key3 | desc_field | item1 | itemB | extra_field
key3 | desc_field | item2 | itemA | extra_field
key3 | desc_field | item2 | itemB | extra_field

Remove whitespace and say "" call to get back to a pipe-separated output file sans blank lines.

^[1] _{raku -e 'say <A B C> X 1..3' returns ((A 1) (A 2) (A 3) (B 1) (B 2) (B 3) (C 1) (C 2) (C 3)).}

https://docs.raku.org/language/control#index-entry-case_statements_(given)
https://docs.raku.org/language/operators#index-entry-X
https://docs.raku.org/language/functions#Defining_operators
https://raku.org