Vertica SQL Build Issue

kushal_alester

Dear Friends,

Need help in resolving the below case-

I want how the output could be build using Vertica SQL:

I have a source table:

Emp_Id,Name_of_Day,Sales_Amount
--------------------------------------------------
1            Monday            5100
2            Wednesday     2000

Now the O/P should look like:
Target Table:

Emp_Id, Monday, Tues,Wed, Thus,Friday
1             5100      0        0       0       0
2             0            0        2000 0       0

How the target table could be loaded in Vertica?

# I could probably think of using CASE Statement

Thanks and Regards,
Kushal Sen





 

eli_revach

Hi,
I think "CASE" is good approch for this case , see below :

select Emp_Id ,case when Name_of_Day='Mon' then Sales_Amount else 0 end as 'Mon',           case when Name_of_Day='Tue' then Sales_Amount else 0 end as 'Tue',
           case when Name_of_Day='Wed' then Sales_Amount else 0 end as 'Wed',
           case when Name_of_Day='Thu' then Sales_Amount else 0 end as 'Thu',
           case when Name_of_Day='Fri' then Sales_Amount else 0 end as 'Fri',
           case when Name_of_Day='Sat' then Sales_Amount else 0 end as 'Sat',
           case when Name_of_Day='Sun' then Sales_Amount else 0 end as 'Sun'
            from <table_name>

kushal_alester

Yes eli,

The same i also came up with. But i was thinking if in any scenario- Name_of_Day has more columns like e.g.100, then we should have 100 more case statement.

Thanks for your effort.
I was looking if there is any other approach to resolve it..

Regards,
Kushal

Sajan_Sangraula

Hi Kushal,
If the anchor has a lot of values and you need to create a large number of columns, you can do the following:
create a table that has a row for each of the values. If you have 100 fields, then you will have a 100 rows in this table:
create table col_names ( col_name varchar(100));
insert into col_names ('Sunday');
...

Do a Cross join between the primary table and this anchor table and do a decode:
select decode(name_of_day,col_name,Sales_Amount,0) as col_name
from table_name cross join col_names;

You may have to aggregate the decoded value based on the rest of the select statement.

/Sajan

kushal_alester

Hi Sajan,

Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved :-|

Regards,
Kushal

kushal_alester

Hi Sajan,

Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved :-|

Regards,
Kushal

kushal_alester

Hi Sajan,

Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved :-|

Regards,
Kushal

kushal_alester

Hi Sajan,

Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved

Regards,
Kushal