Vertica SQL Build Issue

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





 


Comments

  • 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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file