How to fetch list items as column names and get cumulative count for every column

[amritanshu1912]

My neo4j relationships look something like....

(city:City {name:'city_name'})
-[:Has_Club]->
(c:Club {name:"ABC"})
-[:Has_Member]->
(p:Person{
    name:"XYZ",
    joinDate:'date_ in_datetime_format'
})

So for one particular city, I want to produce a record of member_count(cumulative) for every club related to city. I am trying to produce a record something like...

/    day     clubA   clubB   clubC   
1.   date1     2       3       3     
2.   date2     3       5       3     
3.   date3     4       7       6     

I googled something and implemented this query mentioned below

    match  
      (city:City{name:"city_name"})
        -[:Has_Club]->
        (club:Club)
        -[:Has_Member]->
        (p:Person)
        
        with p.joinDate as jd, club
        return date(jd) as day, count(*), club.name  
        order by day

But with this query, I am getting record which has column name as 'day', 'count' and 'clubName'.

/       day     count    clubName 
1      date1     12       clubA
2      date2     31       clubB
3      date3      9       clubA

What do I need to change to see 'day' and club_Names as columns and make member_count add up in successive rows for that particular club.

[glilienfield]

Not sure what you are asking for about the columns.  Are the three columns, 'day', 'count', and 'clubName' ok?  Are you looking for a running total per club over time, such as

/       day     total    clubName 
1      date1     12       clubA
2      date2     31       clubB
3      date3     21       clubA

[amritanshu1912]

no the columns i want are 'day',  'clubA' , 'clubB' , clubC and more club columns if they exist

/    day     clubA   clubB   clubC   
1.   date1     2       3       3       
2.   date2     3       5       3        
3.   date3     4       7       6        

 So if you look at clubA column, 2 members have joinDate as date1 so total members which belong to club A at date1 are 2, then on date2 one more member got added to clubA , so on date2 total number of members  that belong to clubA are 3 (i.e. 2+1), same goes for date3 and clubB. But if you look at clubC on date1 only 3 members got added but on date2 there was  no addition in clubC so on date2 the members' count doesn't increase and remains 3 on date2. On date3  3 members were added to clubC so total members belonging to clubC by the  end of date3 was 6(i.e. 3+3).

[amritanshu1912]

@glilienfield Any help would be highly appreciated

[glilienfield]

Try this. It works for a fixed know set of club names. You need to substitute with your specific names, where I have 'A', 'B', and 'C'.

match (club:Club)-[:Has_Member]->(p:Person)
with p.joinDate as jd, club.name as club, count(p) as dailyClubCount
with collect(distinct jd) as dates, collect({date: jd, club: club, count: dailyClubCount}) as clubStats
order by dates
unwind dates as date
return date,
    reduce(s=0,i in [x in clubStats where x.club='A' and x.date<=date|x.count] | s + i) as `A`,
    reduce(s=0,i in [x in clubStats where x.club='B' and x.date<=date|x.count] | s + i) as `B`,
    reduce(s=0,i in [x in clubStats where x.club='C' and x.date<=date|x.count] | s + i) as `C`

[glilienfield]

Here is the output for my test data: