bugfix> sql > 投稿

私はSqlServerを使用していますが、私はそれが本当にうまくないことを認めなければなりません... これは上級ユーザーにとって簡単な質問かもしれません(私は願っています)

このようなテーブルが2つあります

最初のテーブル(IDは主キーではありません)

ID      IdCust   Ref
1       300      123
1       300      124
2       302      345

2番目(IDは主キーではありません)

ID     Ref      Code    Price
1      123      A       10
1      123      Y       15
2      124      A       14
3      345      C       18

2番目の表の「Ref」列は、最初の表の「Ref」の外部キーです。

次の出力を生成しようとしています:

[編集] 列「Stock」、「Code」、および「Price」にはxの値を設定できるため、事前にはわかりません...

「PIVOT」のようなものをたくさん試しましたが、正しい結果が得られなかったので、誰かが私の問題を解決できることを願っています...

回答 2 件
  • row_number() を使用する  機能して条件付き 集約:

    select id, IdCust, Ref,
           max(case when Seq = 1 then stock end) as [Stock A], -- second table *id*
           max(case when Seq = 1 then code end) as [Code 1],
           max(case when Seq = 1 then price end) as [Price1],
           max(case when Seq = 2 then stock end) as [Stock B], -- second table *id*
           max(case when Seq = 2 then code end) as [Code 2],
           max(case when Seq = 2 then price end) as [Price2]
    from (select f.*, s.Id Stock, s.Code, s.Price,
                 row_number() over (partition by f.Ref order by s.id) as Seq
         from first f
         inner join second s on s.Ref = f.Ref 
         ) t
    group by id, IdCust, Ref;
    
    

    しかしながら、この と一緒に行くだろう知られている 他に必要な値動的 そのためのソリューション。

  • @YogeshSharmaが優れた回答を提供しました。

    これは Pivot を使用して同じことを行います ;SQL Fiddleデモ。

    機能的には、2つの答えに違いはありません。ただし、Yogeshのソリューションは理解しやすく、パフォーマンスが向上しています。個人的に私はそれを選ぶだろう...あなたが質問でPIVOTに言及しているという理由だけでこの答えを含めた

    select ft.Id
    , ft.IdCust
    , ft.Ref
    , x.Stock1
    , x.Code1
    , x.Price1
    , x.Stock2
    , x.Code2
    , x.Price2
    from FirstTable ft
    left outer join (
      select Ref
      , max([Stock1]) Stock1
      , max([Stock2]) Stock2
      , max([Code1]) Code1
      , max([Code2]) Code2
      , max([Price1]) Price1
      , max([Price2]) Price2
      from
      (
        select Ref
        , Id Stock
        , Code
        , Price
        , ('Stock' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) StockLineNo
        , ('Code' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) CodeLineNo
        , ('Price' + cast(Row_Number() over (partition by Ref order by Id, Code) as nvarchar)) PriceLineNo
        from SecondTable
      ) st
      pivot (max(Stock) for StockLineNo in ([Stock1],[Stock2])) pvtStock
      pivot (max(Code) for CodeLineNo in ([Code1],[Code2])) pvtCode
      pivot (max(Price) for PriceLineNo in ([Price1],[Price2])) pvtPrice
      Group by Ref
    ) x
    on x.Ref = ft.Ref
    order by ft.Ref
    
    

    Yogeshのソリューションと同様に、これは指定した数の列のみを処理します。データに合わせて列の数を動的に変更することはありません。そのためには、動的SQLを実行する必要があります。しかしながら;それを行う必要がある場合は、間違った方法で問題を解決しようとしている可能性が高くなります...そのため、設計を検討し、追加の行/いくつかの代替アプローチではなく、結果ごとに追加の列が本当に必要かどうかを判断してください...


    @YogeshSharmaの答えに基づいた動的SQLの実装は次のとおりです。DBFiddle

    declare @sql nvarchar(max) = 'select id, IdCust, Ref'
    select @sql = @sql + '
            ,max(case when Seq = 1 then stock end) as [Stock' + rowNumVarchar + '] 
            ,max(case when Seq = 1 then code end) as [Code' + rowNumVarchar + ']
            ,max(case when Seq = 1 then price end) as [Price' + rowNumVarchar + ']
    '
    from 
    (
        select distinct cast(row_number() over (partition by ref order by ref) as nvarchar) rowNumVarchar
        from second s
    ) z
    set @sql = @sql + '
        from (select f.*, s.Id Stock, s.Code, s.Price,
                     row_number() over (partition by f.Ref order by s.id) as Seq
             from first f
             inner join second s on s.Ref = f.Ref 
             ) t
        group by id, IdCust, Ref;
    '
    print @sql --see what the SQL produced is
    exec (@sql)
    
    

    (これは、このリンクのSQL Fiddleリンクです。ただし、SQLが有効であっても機能しません。

あなたの答え