Add multiplicity as rows

I use Sparx EA to build a relationship database (MS SQL Server).

The tool has a function to create charts.

I use the query:

SELECT system.Name AS Series, systemElement.Name AS GroupName
FROM t_object systemElement
INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'

WHERE system.Name = 'Name of my main system object'

to get

which is great, just for the fact that the chart doesn’t account for the multiplicity of the ActualPosts.

I can access the multiplicity with

SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'ActualPost', post.Multiplicity AS 'Multiplicity'
FROM t_object systemElement
INNER JOIN t_object system ON system.Object_ID = systemElement.ParentID
INNER JOIN t_object organization ON organization.ParentID = systemElement.Object_ID
INNER JOIN t_object post ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'

WHERE system.Name = 'Name of my main system object'

which results in a table like:

Series | GroupName | ActualPost | Multiplicity
Name of my main system element | OperationalPerformer A | Post AA | 1
Name of my main system element | OperationalPerformer A | Post AB | 1
Name of my main system element | OperationalPerformer A | Post AC | 2
Name of my main system element | OperationalPerformer B | Post BA | 1
Name of my main system element | OperationalPerformer B | Post BB | 1
Name of my main system element | OperationalPerformer C | Post CA | 3
Name of my main system element | OperationalPerformer C | Post CB | 2
Name of my main system element | OperationalPerformer C | Post CC | 5

What I’m thinking is to add the multiplicity as rows, so turn a table like this:

Series | GroupName | ActualPost| Multiplicity
Name of my main system element | OperationalPerformer A | Post AA | 1
Name of my main system element | OperationalPerformer A | Post AB | 1
Name of my main system element | OperationalPerformer A | Post AC | 2

into

Series | GroupName
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A
Name of my main system element | OperationalPerformer A

Although I don’t know how to do that.

Any thoughts?

Thanks!

UPDATE:

Seems like Sparx EA has some limitations when it comes to SQL-queries… The query HAS TO start with select, see WITH clause within EA Sparx query

Is there a workaround for provided answers?

Answer

This will work if the Multiplicity <= 2047

WITH myTable as (
    SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'ActualPost', post.Multiplicity AS 'Multiplicity'
    FROM t_object systemElement
    JOIN t_object system 
      ON system.Object_ID = systemElement.ParentID
    JOIN t_object organization 
      ON organization.ParentID = systemElement.Object_ID
    JOIN t_object post 
      ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'
    WHERE system.Name = 'Name of my main system object'
)
SELECT
    Series, GroupName
FROM
    myTable m
    JOIN master.dbo.spt_values v on m.Multiplicity > v.number
WHERE
    Type = 'P'

Modified CTE to Derived Table check this out

SELECT
    Series, GroupName
FROM (
    SELECT system.Name AS Series, systemElement.Name AS GroupName, post.Name AS 'ActualPost', post.Multiplicity AS 'Multiplicity'
    FROM t_object systemElement
    JOIN t_object system 
      ON system.Object_ID = systemElement.ParentID
    JOIN t_object organization 
      ON organization.ParentID = systemElement.Object_ID
    JOIN t_object post 
      ON post.ParentID = organization.Object_ID AND post.Stereotype = 'ActualPost'
    WHERE system.Name = 'Name of my main system object'
) DT
    JOIN MASTER.dbo.spt_values v on DT.Multiplicity > v.number
WHERE
    Type = 'P'