Pivot return output

[chap400001]

Hi. I’m very new to NEO4J and Graph Database. Very Strong Relational and SQL experience. Sorry if this is a simple question.

Node label description
DateRun
• Date Value of an Event
TimeRun
• Time of Day Event happen on a Day (Also has property to make it unique from other TimeRun)

Number
• Numbers (1 – 80) that are related to a TimeRun. 1-->n numbers are related to aTimeRun

The Path
(num:Number ) -[:number_on]- (tr:TimeRun) - [:time_on] - (dr:DateRun )

I issue the following statement and runs as expected.

with ['04/07/2020','04/08/2020'] as fd
match (num:Number ) -[:number_on]- (tr:TimeRun) - [:time_on] - (dr:DateRun )
where dr.value in fd
with dr as DATE_RUN, num.value as NUMBER, collect(num.value) as nc
return DATE_RUN.value, NUMBER, size(nc) as COUNT
order by DATE_RUN, NUMBER, size(nc)

The statement takes a DateRun and counts of the number of times a number is used on that date. (it does not factor the time on the date. Just the Date)

A sample of the out is:

╒════════════════╤════════╤═══════╕
│"DATE_RUN.value" │"NUMBER"│"COUNT" │
╞════════════════╪════════╪═══════╡
│"04/08/2020" │"01" │39 │
├────────────────┼────────┼───────┤
│"04/08/2020" │"02" │42 │
├────────────────┼────────┼───────┤
│"04/08/2020" │"03" │37 │
├────────────────┼────────┼───────┤
│"04/08/2020" │"04" │55 │
├────────────────┼────────┼───────┤
│"04/08/2020" │"05" │33 │
├────────────────┼────────┼───────┤
│"04/08/2020" │"06" │45 │
├────────────────┼────────┼───────┤
│"04/08/2020" │"07" │50 │
├────────────────┼────────┼───────┤
│"04/07/2020" │"01" │41 │
├────────────────┼────────┼───────┤
│"04/07/2020" │"02" │44 │
├────────────────┼────────┼───────┤
│"04/07/2020" │"03" │46 │
├────────────────┼────────┼───────┤
│"04/07/2020" │"04" │45 │
├────────────────┼────────┼───────┤
│"04/07/2020" │"05" │46 │
└────────────────┴────────┴───────┘

Is it possible to Pivot the output above to look like the sample output below?

Number 4/8/2020 4/7/2020
01 39 41
02 42 44
03 37 45
04 55 45
05 33 46
06 45 41
07 50 39

The DateRun Node value is now a header and the Count for each number is a value under each DateRun. (Number would go to 80 and stop. There are no numbers higher than 80)

Thank you for any help you might be able to provide!

[ameyasoft]

The only way is to collect the data separately for each date and then join/rearrange the data to get the desired result.

[chap400001]

Thanks for taking the time for reading and replying. It it much appreciated.

[ameyasoft]

No problem! Let me know if you need help getting your results.

[intouch_vivek]

chap400001:

with ['04/07/2020','04/08/2020'] as fd
match (num:Number ) -[:number_on]- (tr:TimeRun) - [:time_on] - (dr:DateRun )
where dr.value in fd
with dr as DATE_RUN, num.value as NUMBER, collect(num.value) as nc

Hi @chap400001,
I had implemented pivot table in my project long back. I do not have sample data that you are using, so unable to test it. I am not sure if the code given below will work in your scenario too. However I am sure it will give you some ray for solution.

with ['04/07/2020','04/08/2020'] as fd
match (num:Number ) -[:number_on]- (tr:TimeRun) - [:time_on] - (dr:DateRun )
where dr.value in fd
with num.value as Number, dr.value as val, count(dr.value) as countVal
with Number, collect([val,countVal]) as keypairlist
with Number, apoc.map.fromPairs(keypairlist) as mapVal
Return Number,mapVal.04/07/2020 as '04/07/2020',mapVal.04/08/2020 as '04/08/2020'

[chap400001]

Perfect. I needed to make a few changes, but what you provided was all I needed, Below is the final Command.
Thank you all for taking the time to read and help out this beginner!

with ['04/07/2020','04/08/2020'] as fd
match (num:Number ) -[:number_on]- (tr:TimeRun) - [:time_on] - (dr:DateRun )
where dr.value in fd
with num.value as Number, dr.value as val, count(dr.value) as countVal
with Number, collect([val,countVal]) as keypairlist
with Number, apoc.map.fromPairs(keypairlist) as mapVal
Return Number,mapVal['04/07/2020'] as 04/07/2020 ,mapVal['04/08/2020'] as 04/08/2020
order by Number

BTW, the TICKS as part of the date in the RETURN line are not showing up in this post.

Thanks again!

[intouch_vivek]

Good to hear that !!
If Query below if working fine then, Please mark as Solution so that others can get benefited.
with ['04/07/2020','04/08/2020'] as fd
match (num:Number ) -[:number_on]- (tr:TimeRun) - [:time_on] - (dr:DateRun )
where dr.value in fd
with num.value as Number, dr.value as val, count(dr.value) as countVal
with Number, collect([val,countVal]) as keypairlist
with Number, apoc.map.fromPairs(keypairlist) as mapVal
Return Number,mapVal['04/07/2020'] as 04/07/2020 ,mapVal['04/08/2020'] as 04/08/2020
order by Number