Joining and Grouping 2 column set

[Beg1nner]

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?

[NorbertKrupa]

• PIVOT Query Example
• Pivot Rows into Columns (a Cross Tab)
• Crosstab or Pivot in Native Vertica SQL

[Beg1nner]

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.

[Beg1nner]

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).

[id10t]

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)

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)

2. count occurrence for first column

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)

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.

[Beg1nner]

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

[Beg1nner]

Stored result set in a temp table and then used it in formatting. It's all good now. thanks!