0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

[SQL]データの横持ちと縦持ちを入れ替える

Last updated at Posted at 2020-11-16

こんなテーブルがあるとする(mst_dog)

id dog_1 dog_2 dog_3 dog_4 coloer_1 coloer_2 coloer_3 coloer_4
1 柴犬 コーギー チワワ ダックス ノーマル 茶色 黒白
2 シェルティー コリー 秋田犬 ノーマル 黒白 黒白 茶色
3 チャウチャウ ボルゾイ ゴールデン ダックス プラチナブロンド 茶色

こんなカラムをこんな風に取得したいとき。

dog coloer
柴犬
コーギー ノーマル
チワワ 茶色
ダックス coloer_4
ETC...

UNIONを使ってもよいんだけれども。。。

get_dog.sql
WITH DOGLIST AS (
   SELECT dog_1 as dog , coloer_1 as coloer FROM mst_dog
   UNION ALL
   SELECT dog_2 as dog , coloer_2 as coloer FROM mst_dog
   UNION ALL
   SELECT dog_3 as dog , coloer_3 as coloer FROM mst_dog
   UNION ALL
   SELECT dog_4 as dog , coloer_4 as coloer FROM mst_dog
)
SELECT * FROM DOGLIST 

dog_の数が多いまたはカラム拡張された場合にしんどいので
こんな書き方もできるよ。

get_dog.sql
WITH DOGLIST AS (
        SELECT
            unnest ( 
                ARRAY [
                dog_1
                ,dog_2
                ,dog_3
                ,dog_4
                ]
            ) AS dog
            , unnest ( 
                ARRAY [
                coloer_1
                ,coloer_2
                ,coloer_3
                ,coloer_4
                ]
            ) AS coloer 
        FROM
            mst_dog
)
SELECT * FROM DOGLIST 

CROS JOIN よりも見やすいからこっちのが自分はすきです。

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?