'combine two table with different content to make a table where each id has two rows in bigquery

hope you can help me with this. so i have one table that contains value

user_id Monday. Tuesday Wednesday
aa11 100 164 284
bb22. 223 143 346

and another table that contains count

user_id Monday. Tuesday Wednesday
aa11 2 3 4
bb22 5 2 3

how can i combine both so it looks like this?

user_id type Monday. Tuesday Wednesday
aa11 value 100 164 284
aa11 count 2 3 4
bb22. value 223 143 346
bb22 count 5 2 3

is it even a good practice to pivot long like this or should i just pivot wide so there would be columns monday_value, monday_count, tuesday_value, tuesday_count, etc?

sqlgoogle-bigquery


Solution 1:[1]

Simple union? :

select * from (
  select user_id, 'value' as type,Monday.,Tuesday,Wednesday
  from table1 
  union all
  select user_id, 'count' as type,Monday.,Tuesday,Wednesday
  from table2 
) t

Solution 2:[2]

You can use simple way as given in answer by @eshirvana.

Or as per your ask, if you want to do it horizontally you can do it following way:

WITH week_value AS
 (
 SELECT 1 as user, 100 as monday
  UNION ALL SELECT 2 as user, 200 as monday
),
week_count as (
 SELECT 1 as user, 10 as monday
  UNION ALL SELECT 2 as user, 2 as monday
)
select A.user, A.monday as monday_value, B.monday as monday_count
from week_value as A
FULL JOIN week_count as B on A.user = B.user

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 eshirvana
Solution 2 Pratik Patil

Related Questions