How to convert comma separated values to row values

[#1]

I have data in this format

Project ID	Firm	Year	KPI	Inventors
6417100	AMD	2002	2	05736423-1;04294002-2;04954459-1
6415975	AMD	2002	1	06193134-1;06028350-1
6400160	AMD	2002	8	06400160-1

I want in this format

Project ID	Firm	Year	KPI	Inventors
6417100	AMD	2002	2	05736423-1
6417100	AMD	2002	2	04294002-2
6417100	AMD	2002	2	04954459-1
6415975	AMD	2002	1	06193134-1
6415975	AMD	2002	1	06028350-1
6400160	AMD	2002	8	06400160-1

Also I want count of inventors like this

Project ID	Firm	Year	KPI	Inventors	Count of Inventors
6417100	AMD	2002	2	05736423-1;04294002-2;04954459-1	3
6415975	AMD	2002	1	06193134-1;06028350-1	2
6400160	AMD	2002	8	06400160-1	1

Please provide the code as I am using personal edition. Thanks in advance

[sunny_talwar · Accepted solution]

Try this:

Table:

LOAD [Project ID],

          Firm,

          Year,

          KPI,

          SubField(Inventors, ';') as Inventors,

          (SubStringCount(Inventors, ';') + 1) as [Count of Inventors]

FROM Source;

[#3]

Can I get distinct count instead of count?

[sunny_talwar]

Try this:

Table:

LOAD RowNo() as RowNo,

  [Project ID],

     Firm,

     Year,

     KPI,

     SubField(Inventors, ';') as Inventors;

LOAD * Inline [

Project ID, Firm, Year, KPI, Inventors

6417100, AMD, 2002, 2, 05736423-1;04294002-2;04954459-1;04954459-1

6415975, AMD, 2002, 1, 06193134-1;06028350-1

6400160, AMD, 2002, 8, 06400160-1;06400160-1

];

Left Join (Table)

LOAD [Project ID],

  Count(DISTINCT Inventors) as [Count of Inventors]

Resident Table

Group By [Project ID];

[#5]

So you mean there is no direct way of doing that like substringcount

[sunny_talwar]

I don't think SubStringCount will work for Distinct Count