CODEWITHSUNDEEP
sqlgoogle-bigquery
crayoN
crayoN

Reputation: 9

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?

Upvotes: 0

Views: 63

Answers (2)

Pratik Patil
Pratik Patil

Reputation: 832

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

Upvotes: 0

eshirvana
eshirvana

Reputation: 24603

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

Upvotes: 1

Related Questions