cancel
Showing results for 
Search instead for 
Did you mean: 

Pivot return output

chap400001
Node

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!

6 REPLIES 6

ameyasoft
Graph Maven

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

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

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

intouch_vivek
Graph Steward

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'

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!

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