CODEWITHSUNDEEP
postgresqlamazon-redshift
user2866034
user2866034

Reputation: 1

i want to transpose the column into rows

I have my data in redshift sql server like:

MatchId TeamId  Teamname  Home/away  Teamstats  statsvalue
1          101     a          home      yards      0
1          101     a          home      firstdown  1
1          101     a          home      points     2
1          101     a          home      completion 4
1          202     b          away      sacks      3
1          202     b          away      penalties  5
1          202     b          away      yards      6
1          202     b          away      points     7

I want the data to be like:

MatchId TeamId  Teamname  Home/away  yards  firstdown points completion sacks penalties 
1          101     a          home     0       1        2        4        3       5
1          202     b          away     6       null     7         null    null    null

Upvotes: 0

Views: 1730

Answers (1)

podiluska
podiluska

Reputation: 51494

Then you need a pivot

Select *
from yourtable
pivot (max(statsvalue) for teamstats in 
     (yards, firstdown, points, completion, sacks, penalties) 
) p

Upvotes: 1

Related Questions