locked
500+ cols to rows RRS feed

  • Question

  • Hi All

    Hope you are all having a lovely day

    I have a table with 500+ column headers

    Some cols are identifiers - the rest are all values

    First some table create code

    CREATE TABLE [data].[Fact_FinSnapshotSectionMvt](
    	[PK_FinSnapshotSection] [int] IDENTITY(-2000000000,1) NOT NULL,
    	[FK_DateID] [int] NOT NULL,
    	[FK_ContractID] [int] NOT NULL,
    	[FK_CompanyID] [int] NOT NULL,
    	[FK_LocationID] [int] NOT NULL,
    	[FK_CedantLocationID] [int] NOT NULL,
    	[FK_BrokerID] [int] NOT NULL,
    	[FK_ReportingUnitID] [int] NOT NULL,
    	[FK_ClassOfBusinessID] [int] NOT NULL,
    	[FK_LineOfBusinessID] [int] NOT NULL,
    	[FK_BrokerContactID] [int] NOT NULL,
    	[FK_CedantID] [int] NOT NULL,
    	[FK_RITypeID] [int] NOT NULL,
    	[FK_RIProgramID] [int] NOT NULL,
    	[FK_DerivativeID] [int] NOT NULL,
    	[FK_CurrencyID] [int] NOT NULL,
    	[FK_Reporting_CurrencyID] [int] NOT NULL,
    	[FK_RetroContractID] [int] NOT NULL,
    	[FK_Retro_ReportingUnit_ID] [int] NOT NULL,
    	[FK_UnderwriterNameID] [int] NOT NULL,
    	[FK_SubClassID] [int] NOT NULL,
    	[FK_EarningMethodID] [int] NOT NULL,
    	[FK_BasisOfCoverageID] [int] NOT NULL,
    	[FK_OriginalCurrencyID] [int] NOT NULL,
    	[FK_CounterPartyID] [int] NULL,
    	[FK_CessionPrcntID] [int] NULL,
    	[FK_AssetManagerID] [int] NULL,
    	[GrossFinalPremiumAdjustment] [decimal](38, 2) NOT NULL,
    	[GrossEarnedFinalPremiumAdjustment] [decimal](38, 2) NOT NULL,
    	[CededFinalPremiumAdjustment] [decimal](38, 2) NOT NULL,
    	[CededEarnedFinalPremiumAdjustment] [decimal](38, 2) NOT NULL,
    	[GrossNoClaimsBonus] [decimal](38, 2) NOT NULL,
    	[GrossEarnedNoClaimsBonus] [decimal](38, 2) NOT NULL,
    	[CededNoClaimsBonus] [decimal](38, 2) NOT NULL,
    	[CededEarnedNoClaimsBonus] [decimal](38, 2) NOT NULL,
    	[GrossPremium] [decimal](38, 2) NOT NULL,
    	[GrossEarnedPremium] [decimal](38, 2) NOT NULL,
    	[CededPremium] [decimal](38, 2) NOT NULL,
    	[CededEarnedPremium] [decimal](38, 2) NOT NULL,
    	[GrossReinstatementPremium] [decimal](38, 2) NOT NULL,
    	[GrossEarnedReinstatementPremium] [decimal](38, 2) NOT NULL,
    	[CededReinstatementPremium] [decimal](38, 2) NOT NULL,
    	[CededEarnedReinstatementPremium] [decimal](38, 2) NOT NULL,
    	[GrossRoundingErrors] [decimal](38, 2) NOT NULL,
    	[GrossEarnedRoundingErrors] [decimal](38, 2) NOT NULL,
    	[CededRoundingErrors] [decimal](38, 2) NOT NULL,
    	[CededEarnedRoundingErrors] [decimal](38, 2) NOT NULL,
    	[GrossBrokerage] [decimal](38, 2) NOT NULL,
    	[GrossEarnedBrokerage] [decimal](38, 2) NOT NULL,
    	[CededBrokerage] [decimal](38, 2) NOT NULL,
    	[CededEarnedBrokerage] [decimal](38, 2) NOT NULL,
    	[GrossCedingCommission] [decimal](38, 2) NOT NULL,
    	[GrossEarnedCedingCommission] [decimal](38, 2) NOT NULL,
    	[CededCedingCommission] [decimal](38, 2) NOT NULL,
    	[CededEarnedCedingCommission] [decimal](38, 2) NOT NULL,
    	[GrossTSMFrontingFee] [decimal](38, 2) NOT NULL,
    	[GrossEarnedTSMFrontingFee] [decimal](38, 2) NOT NULL,
    	[CededTSMFrontingFee] [decimal](38, 2) NOT NULL,
    	[CededEarnedTSMFrontingFee] [decimal](38, 2) NOT NULL,
    	[GrossFET] [decimal](38, 2) NOT NULL,
    	[GrossEarnedFET] [decimal](38, 2) NOT NULL,
    	[CededFET] [decimal](38, 2) NOT NULL,
    	[CededEarnedFET] [decimal](38, 2) NOT NULL,
    	[GrossInterestonFundsHeld] [decimal](38, 2) NOT NULL,
    	[GrossEarnedInterestonFundsHeld] [decimal](38, 2) NOT NULL,
    	[CededInterestonFundsHeld] [decimal](38, 2) NOT NULL,
    	[CededEarnedInterestonFundsHeld] [decimal](38, 2) NOT NULL,
    	[GrossProfitCommission] [decimal](38, 2) NOT NULL,
    	[GrossEarnedProfitCommission] [decimal](38, 2) NOT NULL,
    	[CededProfitCommission] [decimal](38, 2) NOT NULL,
    	[CededEarnedProfitCommission] [decimal](38, 2) NOT NULL,
    	[GrossCollateralOffsetFee] [decimal](38, 2) NOT NULL,
    	[GrossEarnedCollateralOffsetFee] [decimal](38, 2) NOT NULL,
    	[CededCollateralOffsetFee] [decimal](38, 2) NOT NULL,
    	[CededEarnedCollateralOffsetFee] [decimal](38, 2) NOT NULL,
    	[GrossDueDiligence] [decimal](38, 2) NOT NULL,
    	[GrossEarnedDueDiligence] [decimal](38, 2) NOT NULL,
    	[CededDueDiligence] [decimal](38, 2) NOT NULL,
    	[CededEarnedDueDiligence] [decimal](38, 2) NOT NULL,
    	[GrossLeverageFees] [decimal](38, 2) NOT NULL,
    	[GrossEarnedLeverageFees] [decimal](38, 2) NOT NULL,
    	[CededLeverageFees] [decimal](38, 2) NOT NULL,
    	[CededEarnedLeverageFees] [decimal](38, 2) NOT NULL,
    	[GrossOverridingCommission] [decimal](38, 2) NOT NULL,
    	[GrossEarnedOverridingCommission] [decimal](38, 2) NOT NULL,
    	[CededOverridingCommission] [decimal](38, 2) NOT NULL,
    	[CededEarnedOverridingCommission] [decimal](38, 2) NOT NULL,
    	[GrossOtherExpenses] [decimal](38, 2) NOT NULL,
    	[GrossEarnedOtherExpenses] [decimal](38, 2) NOT NULL,
    	[CededOtherExpenses] [decimal](38, 2) NOT NULL,
    	[CededEarnedOtherExpenses] [decimal](38, 2) NOT NULL,
    	[GrossOtherIncome] [decimal](38, 2) NOT NULL,
    	[GrossEarnedOtherIncome] [decimal](38, 2) NOT NULL,
    	[CededOtherIncome] [decimal](38, 2) NOT NULL,
    	[CededEarnedOtherIncome] [decimal](38, 2) NOT NULL,
    	[GrossTSMManagementFee] [decimal](38, 2) NOT NULL,
    	[GrossEarnedTSMManagementFee] [decimal](38, 2) NOT NULL,
    	[CededTSMManagementFee] [decimal](38, 2) NOT NULL,
    	[CededEarnedTSMManagementFee] [decimal](38, 2) NOT NULL,
    	[GrossSPC] [decimal](38, 2) NOT NULL,
    	[GrossEarnedSPC] [decimal](38, 2) NOT NULL,
    	[CededSPC] [decimal](38, 2) NOT NULL,
    	[CededEarnedSPC] [decimal](38, 2) NOT NULL,
    	[GrossOtherTax] [decimal](38, 2) NOT NULL,
    	[GrossEarnedOtherTax] [decimal](38, 2) NOT NULL,
    	[CededOtherTax] [decimal](38, 2) NOT NULL,
    	[CededEarnedOtherTax] [decimal](38, 2) NOT NULL,
    	[GrossTaxExpenses] [decimal](38, 2) NOT NULL,
    	[GrossEarnedTaxExpenses] [decimal](38, 2) NOT NULL,
    	[CededTaxExpenses] [decimal](38, 2) NOT NULL,
    	[CededEarnedTaxExpenses] [decimal](38, 2) NOT NULL,
    	[GrossUnclassified] [decimal](38, 2) NOT NULL,
    	[GrossEarnedUnclassified] [decimal](38, 2) NOT NULL,
    	[CededUnclassified] [decimal](38, 2) NOT NULL,
    	[CededEarnedUnclassified] [decimal](38, 2) NOT NULL,
    	[GrossAustralianWitholdingTax] [decimal](38, 2) NOT NULL,
    	[GrossEarnedAustralianWitholdingTax] [decimal](38, 2) NOT NULL,
    	[CededAustralianWitholdingTax] [decimal](38, 2) NOT NULL,
    	[CededEarnedAustralianWitholdingTax] [decimal](38, 2) NOT NULL,
    	[GrossGSTBrokerage] [decimal](38, 2) NOT NULL,
    	[GrossEarnedGSTBrokerage] [decimal](38, 2) NOT NULL,
    	[CededGSTBrokerage] [decimal](38, 2) NOT NULL,
    	[CededEarnedGSTBrokerage] [decimal](38, 2) NOT NULL,
    	[GrossGSTPremium] [decimal](38, 2) NOT NULL,
    	[GrossEarnedGSTPremium] [decimal](38, 2) NOT NULL,
    	[CededGSTPremium] [decimal](38, 2) NOT NULL,
    	[CededEarnedGSTPremium] [decimal](38, 2) NOT NULL,
    	[GrossRetroFET] [decimal](38, 2) NOT NULL,
    	[GrossEarnedRetroFET] [decimal](38, 2) NOT NULL,
    	[CededRetroFET] [decimal](38, 2) NOT NULL,
    	[CededEarnedRetroFET] [decimal](38, 2) NOT NULL,
    	[GrossPaidLAE] [decimal](38, 2) NOT NULL,
    	[GrossEarnedPaidLAE] [decimal](38, 2) NOT NULL,
    	[CededPaidLAE] [decimal](38, 2) NOT NULL,
    	[CededEarnedPaidLAE] [decimal](38, 2) NOT NULL,
    	[AssuemdPrem] [decimal](38, 2) NOT NULL,
    	[AssumedCedingCommission] [decimal](38, 2) NOT NULL,
    	[AssumedBrokerage] [decimal](38, 2) NOT NULL,
    	[AssumedprofitCommission] [decimal](38, 2) NOT NULL,
    	[AssumedOtherExpenses] [decimal](38, 2) NOT NULL,
    	[AssumedNoClaimsBonus] [decimal](38, 2) NOT NULL,
    	[GrossPremiumDepositRetained] [decimal](38, 2) NOT NULL,
    	[GrossEarnedPremiumDepositRetained] [decimal](38, 2) NOT NULL,
    	[CededPremiumDepositRetained] [decimal](38, 2) NOT NULL,
    	[CededEarnedPremiumDepositRetained] [decimal](38, 2) NOT NULL,
    	[GrossPremiumDepositReleased] [decimal](38, 2) NOT NULL,
    	[GrossEarnedPremiumDepositReleased] [decimal](38, 2) NOT NULL,
    	[CededPremiumDepositReleased] [decimal](38, 2) NOT NULL,
    	[CededEarnedPremiumDepositReleased] [decimal](38, 2) NOT NULL,
    	[GrossLossDepositRetained] [decimal](38, 2) NOT NULL,
    	[GrossEarnedLossDepositRetained] [decimal](38, 2) NOT NULL,
    	[CededLossDepositRetained] [decimal](38, 2) NOT NULL,
    	[CededEarnedLossDepositRetained] [decimal](38, 2) NOT NULL,
    	[GrossLossDepositReleased] [decimal](38, 2) NOT NULL,
    	[GrossEarnedLossDepositReleased] [decimal](38, 2) NOT NULL,
    	[CededLossDepositReleased] [decimal](38, 2) NOT NULL,
    	[CededEarnedLossDepositReleased] [decimal](38, 2) NOT NULL,
    	[GrossInterestOnPremiumDeposit] [decimal](38, 2) NOT NULL,
    	[GrossEarnedInterestOnPremiumDeposit] [decimal](38, 2) NOT NULL,
    	[CededInterestOnPremiumDeposit] [decimal](38, 2) NOT NULL,
    	[CededEarnedInterestOnPremiumDeposit] [decimal](38, 2) NOT NULL,
    	[GrossInterestOnLossDeposit] [decimal](38, 2) NOT NULL,
    	[GrossEarnedInterestOnLossDeposit] [decimal](38, 2) NOT NULL,
    	[CededInterestOnLossDeposit] [decimal](38, 2) NOT NULL,
    	[CededEarnedInterestOnLossDeposit] [decimal](38, 2) NOT NULL,
    	[GrossPortfolioInAccrual] [decimal](38, 2) NOT NULL,
    	[GrossEarnedPortfolioInAccrual] [decimal](38, 2) NOT NULL,
    	[CededPortfolioInAccrual] [decimal](38, 2) NOT NULL,
    	[CededEarnedPortfolioInAccrual] [decimal](38, 2) NOT NULL,
    	[GrossPortfolioOutAccrual] [decimal](38, 2) NOT NULL,
    	[GrossEarnedPortfolioOutAccrual] [decimal](38, 2) NOT NULL,
    	[CededPortfolioOutAccrual] [decimal](38, 2) NOT NULL,
    	[CededEarnedPortfolioOutAccrual] [decimal](38, 2) NOT NULL,
    	[GrossExchangeDifferences] [decimal](38, 2) NOT NULL,
    	[GrossEarnedExchangeDifferences] [decimal](38, 2) NOT NULL,
    	[CededExchangeDifferences] [decimal](38, 2) NOT NULL,
    	[CededEarnedExchangeDifferences] [decimal](38, 2) NOT NULL,
    	[GrossInuringPremium] [decimal](38, 2) NOT NULL,
    	[GrossEarnedInuringPremium] [decimal](38, 2) NOT NULL,
    	[CededInuringPremium] [decimal](38, 2) NOT NULL,
    	[CededEarnedInuringPremium] [decimal](38, 2) NOT NULL,
    	[GrossProtectionFees] [decimal](38, 2) NOT NULL,
    	[GrossEarnedProtectionFees] [decimal](38, 2) NOT NULL,
    	[CededProtectionFees] [decimal](38, 2) NOT NULL,
    	[CededEarnedProtectionFees] [decimal](38, 2) NOT NULL,
    	[GrossLossesPaid] [decimal](38, 2) NOT NULL,
    	[CededLossesPaid] [decimal](38, 2) NOT NULL,
    	[NetLossesPaid] [decimal](38, 2) NOT NULL,
    	[GrossALAEPaid] [decimal](38, 2) NOT NULL,
    	[CededALAEPaid] [decimal](38, 2) NOT NULL,
    	[NetALAEPaid] [decimal](38, 2) NOT NULL,
    	[GrossSSPaid] [decimal](38, 2) NOT NULL,
    	[CededSSPaid] [decimal](38, 2) NOT NULL,
    	[NetSSPaid] [decimal](38, 2) NOT NULL,
    	[GrossCaseReserve] [decimal](38, 2) NOT NULL,
    	[CededCaseReserve] [decimal](38, 2) NOT NULL,
    	[NetCaseReserve] [decimal](38, 2) NOT NULL,
    	[GrossCaseALAEReserve] [decimal](38, 2) NOT NULL,
    	[CededCaseALAEReserve] [decimal](38, 2) NOT NULL,
    	[NetCaseALAEReserve] [decimal](38, 2) NOT NULL,
    	[GrossIBNR] [decimal](38, 2) NOT NULL,
    	[CededIBNR] [decimal](38, 2) NOT NULL,
    	[NetIBNR] [decimal](38, 2) NOT NULL,
    	[GrossACR] [decimal](38, 2) NOT NULL,
    	[CededACR] [decimal](38, 2) NOT NULL,
    	[NetACR] [decimal](38, 2) NOT NULL,
    	[GrossUnclassedAmt] [decimal](38, 2) NOT NULL,
    	[CededUnclassedAmt] [decimal](38, 2) NOT NULL,
    	[NetUnclassedAmt] [decimal](38, 2) NOT NULL,
    	[FK_LayerID] [int] NULL,
    	[CHK_ContractAttributes]  AS (checksum([FK_CompanyID],[FK_LocationID],[FK_CedantLocationID],[FK_BrokerID],[FK_ReportingUnitID],[FK_BrokerContactID],[FK_CedantID],[FK_RITypeID],[FK_ClassOfBusinessID],[FK_LineOfBusinessID])),
    	[Sub_Class_Of_Business_ID] [int] NULL,
    	[Dominant_Sub_Class_Of_Business_ID] [int] NULL,
     CONSTRAINT [PK_FinSnapshotSectionMvt_NewCombinationFix] PRIMARY KEY NONCLUSTERED 
    (
    	[PK_FinSnapshotSection] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    

    I need a way instead of sitting typing it out to dynamically de-normalise this as its going to take forever to type it all out

    Rules

    A) if its an ID either PK or FK output that as a col

    B) if its not an ID PK or FK then I would like a new "Name" column with the column and its value under a column "AMT"

    is there a swizzy dynamic way of generating this as i have multiple other tables i will need to repeat it for

    Thanks 


    James

    Friday, July 26, 2019 10:00 AM

Answers

  • DECLARE @sqlcmd nvarchar(max), @nonkeycols nvarchar(max);
    
    /* Get non-key cols */
    select @nonkeycols = COALESCE( @nonkeycols + ', ','') + QUOTENAME(c.COLUMN_NAME)
    from INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_SCHEMA = 'data'
    AND c.TABLE_NAME = 'Fact_FinSnapshotSectionMvt'
    AND c.COLUMN_NAME NOT LIKE 'PK[_]%'
    AND c.COLUMN_NAME NOT LIKE 'FK[_]%'
    AND c.DATA_TYPE = 'decimal'
    ORDER BY c.ORDINAL_POSITION
    
    
    SET @sqlcmd = '
    SELECT *
    FROM (
    SELECT * FROM data.Fact_FinSnapshotSectionMvt
    ) p
    UNPIVOT (
        Amt FOR Name IN (
        ' + @nonkeycols + '
        )
    ) as unpvt;
    ';
    
    EXEC (@sqlcmd);
    

    Friday, July 26, 2019 2:41 PM
  • declare @TABLE_SCHEMA varchar(128)='data' ,
    @TABLE_NAME varchar(128)='Fact_FinSnapshotSectionMvt'
    
    Declare @sqlUnpivot as NVarchar(max)
    Declare @ColsKeep as NVarchar(max)
    Declare @ColsUnpivot as NVarchar(max)
    
     
     
    Select @ColsKeep =  COALESCE(@ColsKeep + ', ', '')  + QUOTENAME(COLUMN_NAME,'[')
    FROM   [INFORMATION_SCHEMA].[COLUMNS]
    WHERE TABLE_SCHEMA=@TABLE_SCHEMA and TABLE_NAME=@TABLE_NAME
     and COLUMN_NAME Like 'PK_%' or COLUMN_NAME Like 'FK_%' 
     order by ORDINAL_POSITION
     
     Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
    FROM  [INFORMATION_SCHEMA].[COLUMNS]
    WHERE TABLE_SCHEMA=@TABLE_SCHEMA and TABLE_NAME=@TABLE_NAME
     and COLUMN_NAME not Like 'PK_%' AND COLUMN_NAME not Like 'FK_%' 
     order by ORDINAL_POSITION
    
     
    
     Set @sqlUnpivot='Select '+@ColsKeep+ ',Name, AMT From '+@TABLE_SCHEMA+'.'+ @TABLE_NAME
     + ' CROSS APPLY (Values ' + @ColsUnpivot + '  ) d(Name, AMT) '
    
    EXEC sp_executesql @sqlUnpivot 

    Friday, July 26, 2019 4:34 PM
  •  Set @sqlUnpivot='Select '+@ColsKeep+ ',Name, AMT From '+@TABLE_SCHEMA+'.'+ @TABLE_NAME
     + ' CROSS APPLY (Values ' + @ColsUnpivot + '  ) d(Name, AMT) 
     WHERE AMT<>0.0'

    • Marked as answer by pituachMVP Tuesday, October 8, 2019 7:52 PM
    Monday, July 29, 2019 1:34 PM

All replies

  • I'm not sure what your desired result is. Please post an example.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Friday, July 26, 2019 11:06 AM
  • DECLARE @sqlcmd nvarchar(max), @nonkeycols nvarchar(max);
    
    /* Get non-key cols */
    select @nonkeycols = COALESCE( @nonkeycols + ', ','') + QUOTENAME(c.COLUMN_NAME)
    from INFORMATION_SCHEMA.COLUMNS c
    WHERE c.TABLE_SCHEMA = 'data'
    AND c.TABLE_NAME = 'Fact_FinSnapshotSectionMvt'
    AND c.COLUMN_NAME NOT LIKE 'PK[_]%'
    AND c.COLUMN_NAME NOT LIKE 'FK[_]%'
    AND c.DATA_TYPE = 'decimal'
    ORDER BY c.ORDINAL_POSITION
    
    
    SET @sqlcmd = '
    SELECT *
    FROM (
    SELECT * FROM data.Fact_FinSnapshotSectionMvt
    ) p
    UNPIVOT (
        Amt FOR Name IN (
        ' + @nonkeycols + '
        )
    ) as unpvt;
    ';
    
    EXEC (@sqlcmd);
    

    Friday, July 26, 2019 2:41 PM
  • declare @TABLE_SCHEMA varchar(128)='data' ,
    @TABLE_NAME varchar(128)='Fact_FinSnapshotSectionMvt'
    
    Declare @sqlUnpivot as NVarchar(max)
    Declare @ColsKeep as NVarchar(max)
    Declare @ColsUnpivot as NVarchar(max)
    
     
     
    Select @ColsKeep =  COALESCE(@ColsKeep + ', ', '')  + QUOTENAME(COLUMN_NAME,'[')
    FROM   [INFORMATION_SCHEMA].[COLUMNS]
    WHERE TABLE_SCHEMA=@TABLE_SCHEMA and TABLE_NAME=@TABLE_NAME
     and COLUMN_NAME Like 'PK_%' or COLUMN_NAME Like 'FK_%' 
     order by ORDINAL_POSITION
     
     Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'
    FROM  [INFORMATION_SCHEMA].[COLUMNS]
    WHERE TABLE_SCHEMA=@TABLE_SCHEMA and TABLE_NAME=@TABLE_NAME
     and COLUMN_NAME not Like 'PK_%' AND COLUMN_NAME not Like 'FK_%' 
     order by ORDINAL_POSITION
    
     
    
     Set @sqlUnpivot='Select '+@ColsKeep+ ',Name, AMT From '+@TABLE_SCHEMA+'.'+ @TABLE_NAME
     + ' CROSS APPLY (Values ' + @ColsUnpivot + '  ) d(Name, AMT) '
    
    EXEC sp_executesql @sqlUnpivot 

    Friday, July 26, 2019 4:34 PM
  • Hi Li

    This is fantastic! one more Q;

    The majority of cols return a 0.00

    If i wanted to exclude any values that return as 0.00 how would i modify this to exclude those?

    Thanks 
    J


    Monday, July 29, 2019 8:41 AM
  •  Set @sqlUnpivot='Select '+@ColsKeep+ ',Name, AMT From '+@TABLE_SCHEMA+'.'+ @TABLE_NAME
     + ' CROSS APPLY (Values ' + @ColsUnpivot + '  ) d(Name, AMT) 
     WHERE AMT<>0.0'

    • Marked as answer by pituachMVP Tuesday, October 8, 2019 7:52 PM
    Monday, July 29, 2019 1:34 PM
  • After you unpivot your data, you can write a simple where clause to filter on your new dataset.
    Monday, July 29, 2019 7:05 PM