Options
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
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
0
Comments
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>
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
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
Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved :-|
Regards,
Kushal
Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved :-|
Regards,
Kushal
Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved :-|
Regards,
Kushal
Can you please explain in detail. I couldn't understand the way you mentioned, it could be resolved
Regards,
Kushal