Vertica SQL Build Issue

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>

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

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

Hi Sajan,

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

Regards,
Kushal

Hi Sajan,

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

Regards,
Kushal

Hi Sajan,

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

Regards,
Kushal

Hi Sajan,

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

Regards,
Kushal