CODEWITHSUNDEEP
sqlsql-servert-sql
carlosm
carlosm

Reputation: 755

Workaround for PIVOT statement

I have this query, is taking like 2 minutes to resolve, I need to find a workaround, I know that UNPIVOT has a better solution using CROSS APPLY, is there anything similar for PIVOT? 

SELECT [RowId],  [invoice date], [GL], [Entité], [001], [Loc], [Centre Cout], [Compte_1], [Interco_1], [Futur_1], [Department], [Division], [Compagnie], [Localisation], [Centre/Cout], [Compte], [Interco], [Futur], [Account], [Mobile], [Last Name], [First Name], [license fee], [GST], [HST], [PST], [Foreign Tax], [Sales Tax License], [Net Total], [Total], [ServiceType], [Oracle Cost Center], [CTRL], [EXPENSE], [Province]
        FROM 
                (SELECT fd.[RowId], fc.[ColumnName], fd.[Value]
                    FROM dbo.FileData fd
                INNER JOIN dbo.[FileColumn] fc  
                    ON fc.[FileColumnId] = fd.[FileColumnId]               
                WHERE FileId = 1
                    AND TenantId = 1) x
        PIVOT
        (
        MAX(Value)
        FOR [ColumnName] IN ( [invoice date], [GL], [Entité], [001], [Loc], [Centre Cout], [Compte_1], [Interco_1], [Futur_1], [Department], [Division], [Compagnie], [Localisation], [Centre/Cout], [Compte], [Interco], [Futur], [Account], [Mobile], [Last Name], [First Name], [license fee], [GST], [HST], [PST], [Foreign Tax], [Sales Tax License], [Net Total], [Total], [ServiceType], [Oracle Cost Center], [CTRL], [EXPENSE], [Province])
        ) AS p

Upvotes: 1

Views: 126

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82000

Pivots are great, but so are Conditional Aggregations. Also, there would be no datatype conficts or conversions necessary

SELECT [RowId]
      ,[invoice date] = max(case when [FileColumnId] = ??? then Value end)
      ,[GL]           = max(case when [FileColumnId] = ??? then Value end)
      ,... more fields
 FROM  dbo.FileData fd
 WHERE FileId = 1
  AND TenantId = 1
 Group By [RowId]

EDIT

You could add back the join to make it more readable.

Upvotes: 1

Related Questions