Hello guys,
Does Vertica support Pivot command?
I was looking to do the following
first Place Second
A A
B B
A B
C A
D C
Transformed to
Places First Second
A 2 2
B 1 1
C 1 0
D 1 0
Any suggestions?
Joining and Grouping 2 column set
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Joining and Grouping 2 column set
Checkout vertica.tips for more Vertica resources.
Re: Joining and Grouping 2 column set
The challenge in my situation is that the First place Values and Second place values can change depending on the where clause. So I cannot hard code them using Decode.
Re: Joining and Grouping 2 column set
Need to distinct from both columns and for my first result column and then do a count for respective rows (number of times they are in first place or second).
Re: Joining and Grouping 2 column set
Hi!
Where is your solution? No challenges here, you always can solve it with "brute force".
1. select all distinct values (you can do it)
2. count occurrence for first column
2. count occurrence for second column
3. Now, can you perform LEFT OUTER JOIN on these results to get desired output? I think you can. So where is a challenge?
PS
Of cause exists a better solution, but theoretically I solved your challenge and there are no other solutions so theoretically Im a winner in this challenge Show me your solution (that differs from my), show me that your solution is better and I will take your challenge and will try to improve my results, so far I don't see any reason to take a challenge.
Where is your solution? No challenges here, you always can solve it with "brute force".
1. select all distinct values (you can do it)
Code: Select all
daniel=> select distinct first as place from t
daniel-> union
daniel-> select distinct second as place from t;
place
-------
A
B
C
D
(4 rows)
Code: Select all
daniel=> select first, count(first) from t group by first;
first | count
-------+-------
A | 2
B | 1
C | 1
D | 1
(4 rows)
2. count occurrence for second column
Code: Select all
daniel=> select second, count(second) from t group by second;
second | count
--------+-------
C | 1
A | 2
B | 2
(3 rows)
PS
Of cause exists a better solution, but theoretically I solved your challenge and there are no other solutions so theoretically Im a winner in this challenge Show me your solution (that differs from my), show me that your solution is better and I will take your challenge and will try to improve my results, so far I don't see any reason to take a challenge.
Re: Joining and Grouping 2 column set
I never challenged you for a solution. You took it wrong.
challenge was noun there not verb.. Anyways leaving grammar away..
the reason I can't do that brute force is that the result set comes after 2-3 joins on 2 tables so I can't afford to run that same thing 5 more times
I don't have a solution to it as of now but trying
challenge was noun there not verb.. Anyways leaving grammar away..
the reason I can't do that brute force is that the result set comes after 2-3 joins on 2 tables so I can't afford to run that same thing 5 more times
I don't have a solution to it as of now but trying
Re: Joining and Grouping 2 column set
Stored result set in a temp table and then used it in formatting. It's all good now. thanks!