Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.