8 Replies Latest reply on Dec 13, 2016 4:57 AM by Daniel Andersen

    Custom SQL - group by/blend

    Daniel Andersen

      Hi guys!

       

      I have this join that I cannot use due to duplicate rows. I can blend the tables but afterwards I need to perform a LOD calculation, to get the right values, but is unable to drag in coloums from blended data.

      Isn't it possible to write a custom SQL that acts like a blend, so that i do have all the functionalities - i mean like a group by or so?

       

      The join is specified below - the relation between [NUMBER_] is many to many and therefore i get the duplicated rows. I would like the [PROJTABLEARCH] to be grouped by/aggregated by [Number_] so the relationship is 1:N if it makes sense/is possible.

      I have tried to show the desired solution in the bottom.

       

      SELECT

        [PROJLINEARCH].[DATASET] AS [DATASET],

        [PROJLINEARCH].[NUMBER_] AS [NUMBER_],

        [PROJLINEARCH].[AMOUNT] AS [AMOUNT],

        [PROJTABLEARCH].[DATASET] AS [DATASET (PROJTABLEARCH)],

        [PROJTABLEARCH].[NUMBER_] AS [NUMBER_ (PROJTABLEARCH)],

        [PROJTABLEARCH].[NAME] AS [NAME (PROJTABLEARCH)],

        [PROJTABLEARCH].[CURRENCY] AS [CURRENCY]

      FROM [dbo].[PROJLINEARCH] [PROJLINEARCH]

        INNER JOIN [dbo].[PROJTABLEARCH] [PROJTABLEARCH] ON ([PROJLINEARCH].[NUMBER_] = [PROJTABLEARCH].[NUMBER_])

       

      PROJLINEARCHPROJTABLEARCH
      Number_PurposeAmountNameNumber_Currency
      100161111000Hans100DKK
      1001611110000Hans100DKK
      10016111100Poul101DKK
      1011611110000Poul101DKK
      10216112100Poul101EUR
      102161121000Torben102EUR
      The output by join
      Number_PurposeAmountNameNumber_Currency
      100161111000Hans100DKK
      100161111000Hans100DKK
      1001611110000Hans100DKK
      1001611110000Hans100DKK
      10016111100Hans100DKK
      10016111100Hans100DKK
      22200
      The output I need
      Number_PurposeAmountNameCurrency
      100161111000HansDKK
      1001611110000HansDKK
      10016111100PoulDKK
      1011611110000PoulDKK
      10216112100PoulEUR
      102161121000TorbenEUR