17
Views
2
Comments
Solved
I need to show dynamic information in a table - Reactive Web
Question
Application Type
Reactive
Service Studio Version
11.10.6 (Build 37198)

I have the follow situation in a SQL Advancede Query... bellow picture...

...and I need to show something like that in a screen in the follow format:

ItemOne Up ServiçosSarcompy Com e ServiçosMKX Tecnologia e Serviços
Serviço R$  4.150,00 R$  2.850,00 R$ 3.850,00
Rack Server 44U X 880mm R$  2.350,00 R$  3.850,00 R$ 2.500,00
Path Cord R$  1.312,50 R$  1.275,00 R$ 1.462,50




Total R$ 7.812,50 R$ 7.975,00 R$ 7.812,50

In this case I have three collumns for three suppliers, but I could have more or less suppliers.

I need help, please.


I managed to solve this using a advanced query widget aplying the follow script:

SET NOCOUNT ON

    SELECT DISTINCT {Fornecedores}.[Razao] AS Fornecedor
               INTO #tmpRetorno
               FROM {Fornecedores}
          LEFT JOIN {Cotacao}              ON  {Cotacao}.[id_Fornecedor]   = {Fornecedores}.[Id]         
          LEFT JOIN {CotacaoItem}          ON  {CotacaoItem}.[id_Cotacao]  = {Cotacao}.[Id]
          LEFT JOIN {Item}                 ON  {Item}.[Id]                 = {CotacaoItem}.[id_Item]
          LEFT JOIN {SolicitacaoCompras}   ON  {SolicitacaoCompras}.[Id]   = {Cotacao}.[SolicitacaoComprasId]
              WHERE {SolicitacaoCompras}.[Id] = @SolicitacaoComprasId;

      WITH CTE_Fornecedor
        AS
   (SELECT Fornecedor, RANK() OVER (ORDER BY Fornecedor ASC) nRank
      FROM #tmpRetorno)

       SELECT (SELECT 'ITEM') Item,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 1) fornecedor1,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 2) fornecedor2,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 3) fornecedor3,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 4) fornecedor4,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 5) fornecedor5,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 6) fornecedor6,         
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 7) fornecedor7,                   
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 8) fornecedor8              
        INTO #tmpTable

    DROP TABLE #tmpRetorno;

             SELECT {Fornecedores}.[Razao]    AS Fornecedor,
                    {Item}.[Descricao]        AS Item,
                    {CotacaoItem}.[TotalItem] AS Valor
               INTO #tmpTable2
               FROM {Fornecedores}
          LEFT JOIN {Cotacao}              ON  {Cotacao}.[id_Fornecedor]   = {Fornecedores}.[Id]         
          LEFT JOIN {CotacaoItem}          ON  {CotacaoItem}.[id_Cotacao]  = {Cotacao}.[Id]
          LEFT JOIN {Item}                 ON  {Item}.[Id]                 = {CotacaoItem}.[id_Item]
          LEFT JOIN {SolicitacaoCompras}   ON  {SolicitacaoCompras}.[Id]   = {Cotacao}.[SolicitacaoComprasId]
              WHERE {SolicitacaoCompras}.[Id] = @SolicitacaoComprasId;
              
             SELECT Item, Fornecedor
               INTO #tmpItemFornecedor
               FROM (SELECT DISTINCT Item FROM #tmpTable2) AS a
         CROSS JOIN (SELECT DISTINCT Fornecedor FROM #tmpTable2) AS b
              
             SELECT a.Fornecedor, a.Item, isnull(Valor, 0) AS Valor
               INTO #tmpRetorno2
               FROM #tmpItemFornecedor a
          LEFT JOIN #tmpTable2         b ON (b.Item       = a.Item
                                         AND b.Fornecedor = a.Fornecedor);
                                         
    DROP TABLE #tmpItemFornecedor;
    DROP TABLE #tmpTable2;
    
             WITH CTE_ItemForn
             AS
            (SELECT Item, Fornecedor, Valor,
                    RANK() OVER (PARTITION BY Item ORDER BY Fornecedor ASC) nRank
               FROM #tmpRetorno2)
                    
             SELECT Item, fornecedor1, fornecedor2, fornecedor3, fornecedor4, fornecedor5, fornecedor6, fornecedor7, fornecedor8
               FROM #tmpTable
               UNION ALL
             SELECT DISTINCT Item,
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 1),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 2),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 3),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 4),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 5),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 6),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 7),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 8)
               FROM CTE_ItemForn x
               UNION ALL
             SELECT DISTINCT 'TOTAL',
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 1),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 2),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 3),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 4),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 5),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 6),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 7),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 8)
               FROM CTE_ItemForn x;
             
             
    DROP TABLE #tmpRetorno2;
    DROP TABLE #tmpTable;


I thank you for your attention and for the tip presented that also brought additional knowledge

Rank: #54

Hi Ricardo,


In order to do that, you will have to pivot your table and the way to do that in SQL is precisely to use the PIVOT command. You can see an example here on how to do it.


Moreover, you can check the Pivot SQL Sample Forge component which showcases how to use it in OutSystems.

In their Try Now demo, you have the option to download survey questions in columns through the Export Pivot option:


Hope it helps.


Regards,
João

I managed to solve this using a advanced query widget aplying the follow script:

SET NOCOUNT ON

    SELECT DISTINCT {Fornecedores}.[Razao] AS Fornecedor
               INTO #tmpRetorno
               FROM {Fornecedores}
          LEFT JOIN {Cotacao}              ON  {Cotacao}.[id_Fornecedor]   = {Fornecedores}.[Id]         
          LEFT JOIN {CotacaoItem}          ON  {CotacaoItem}.[id_Cotacao]  = {Cotacao}.[Id]
          LEFT JOIN {Item}                 ON  {Item}.[Id]                 = {CotacaoItem}.[id_Item]
          LEFT JOIN {SolicitacaoCompras}   ON  {SolicitacaoCompras}.[Id]   = {Cotacao}.[SolicitacaoComprasId]
              WHERE {SolicitacaoCompras}.[Id] = @SolicitacaoComprasId;

      WITH CTE_Fornecedor
        AS
   (SELECT Fornecedor, RANK() OVER (ORDER BY Fornecedor ASC) nRank
      FROM #tmpRetorno)

       SELECT (SELECT 'ITEM') Item,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 1) fornecedor1,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 2) fornecedor2,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 3) fornecedor3,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 4) fornecedor4,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 5) fornecedor5,
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 6) fornecedor6,         
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 7) fornecedor7,                   
              (SELECT Fornecedor FROM CTE_Fornecedor WHERE nRank = 8) fornecedor8              
        INTO #tmpTable

    DROP TABLE #tmpRetorno;

             SELECT {Fornecedores}.[Razao]    AS Fornecedor,
                    {Item}.[Descricao]        AS Item,
                    {CotacaoItem}.[TotalItem] AS Valor
               INTO #tmpTable2
               FROM {Fornecedores}
          LEFT JOIN {Cotacao}              ON  {Cotacao}.[id_Fornecedor]   = {Fornecedores}.[Id]         
          LEFT JOIN {CotacaoItem}          ON  {CotacaoItem}.[id_Cotacao]  = {Cotacao}.[Id]
          LEFT JOIN {Item}                 ON  {Item}.[Id]                 = {CotacaoItem}.[id_Item]
          LEFT JOIN {SolicitacaoCompras}   ON  {SolicitacaoCompras}.[Id]   = {Cotacao}.[SolicitacaoComprasId]
              WHERE {SolicitacaoCompras}.[Id] = @SolicitacaoComprasId;
              
             SELECT Item, Fornecedor
               INTO #tmpItemFornecedor
               FROM (SELECT DISTINCT Item FROM #tmpTable2) AS a
         CROSS JOIN (SELECT DISTINCT Fornecedor FROM #tmpTable2) AS b
              
             SELECT a.Fornecedor, a.Item, isnull(Valor, 0) AS Valor
               INTO #tmpRetorno2
               FROM #tmpItemFornecedor a
          LEFT JOIN #tmpTable2         b ON (b.Item       = a.Item
                                         AND b.Fornecedor = a.Fornecedor);
                                         
    DROP TABLE #tmpItemFornecedor;
    DROP TABLE #tmpTable2;
    
             WITH CTE_ItemForn
             AS
            (SELECT Item, Fornecedor, Valor,
                    RANK() OVER (PARTITION BY Item ORDER BY Fornecedor ASC) nRank
               FROM #tmpRetorno2)
                    
             SELECT Item, fornecedor1, fornecedor2, fornecedor3, fornecedor4, fornecedor5, fornecedor6, fornecedor7, fornecedor8
               FROM #tmpTable
               UNION ALL
             SELECT DISTINCT Item,
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 1),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 2),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 3),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 4),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 5),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 6),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 7),
                    (SELECT CAST(Valor AS NVARCHAR) FROM CTE_ItemForn WHERE Item = x.Item and nRank = 8)
               FROM CTE_ItemForn x
               UNION ALL
             SELECT DISTINCT 'TOTAL',
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 1),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 2),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 3),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 4),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 5),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 6),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 7),
                    (SELECT CAST(SUM(Valor) AS NVARCHAR) FROM CTE_ItemForn WHERE nRank = 8)
               FROM CTE_ItemForn x;
             
             
    DROP TABLE #tmpRetorno2;
    DROP TABLE #tmpTable;


I thank you for your attention and for the tip presented that also brought additional knowledge