CODEWITHSUNDEEP
google-bigquery
Abo7aneen
Abo7aneen

Reputation: 67

Change direction of table in BigQuery

I have a dataset [lipid] that extracted from electronic medical record system (EMRS). In that EMRS, the physician give order to obtain a laboratory blood profile from a patient with a unique order number BUT with a different service types. So, if one order has 4 service types, EMRS will record the event on 4 rows (identical [duplicates] order number in Order_no column, BUT with a different service types in Service_type column) like this;

Order_no Service_type Result
1 TC 230
1 HDL 40
1 TG 150
1 LDL 90

Sometimes, one order may has <4 service types, hence, order will be like that;

Order_no Service_type Result
1 TC 230
1 HDL 40
1 TG 150
1 LDL 90
2 TC 230
2 HDL 40
4 TC 230
4 HDL 40
4 LDL 90
5 TC 230
5 TG 150
5 LDL 90
6 TC 230
8 TC 230
8 HDL 40
8 TG 150
8 LDL 90

What I'm trying to do is writing a query that keeps Order_no column and change direction of table as well as merge identical order number in one row like this;

Order_no TC HDL TG LDL
1 230 40 150 90
2 250 66
4 199 39 99
5 299 45 190
6 400
8 400 40 250 290

How can I write this query in Google BigQuery?

Upvotes: 1

Views: 323

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

Use below approach

select * from your_table
pivot (any_value(Result) for Service_type in ('TC', 'HDL', 'TG', 'LDL'))

In case if Service Type is not known in advance - you can use below

execute immediate (select '''
select * from your_table
pivot (any_value(Result) for Service_type in (''' || string_agg(distinct "'" || Service_type || "'") || 
"))"
from your_table
)

Upvotes: 2

dnnshssm
dnnshssm

Reputation: 1305

You can use PIVOT.

Example:

WITH your_table AS 
( 
  SELECT 1 AS Order_no, 'TC' AS Service_type, 230 AS Result
  UNION ALL
  SELECT 1, 'HDL', 40
  UNION ALL
  SELECT 1, 'TG', 150
  UNION ALL
  SELECT 1, 'LDL', 90
)

SELECT *
FROM your_table PIVOT(SUM(Result) FOR Service_type IN ('TC', 'HDL', 'TG', 'LDL'))

Upvotes: 0

Related Questions