locked
MSSQL - Merge two row into one RRS feed

  • Question

  • I wrote the below MS SQL Query:

    SELECT B.[Year],E.[Description] as GLClass, D.Code, D.[GLDescription], Sum(A.Jan) as Jan, Sum(A.Feb) as Feb, Sum(A.Mar) as Mar, Sum(A.Apr)as Apr
    From GeneralLedgers A inner join
    Years B on A.YearID = B.ID
    inner join CostCenters C on
    A.CostCenterID = C.ID
    inner join GLCodes D on
    A.GLCodeID = D.ID inner join
    GLClassificationTypes E on 
    D.GLClassificationTypeID = E.ID
    WHERE A.YearID = '13' 
    Group By B.[Year], D.Code, E.[Description],D.[GLDescription]
    Order by D.Code, E.[Description]

    It output the below result. enter image description here

    I want to combine "Freight-in (Go) and Freight Savings" row into a single row and sum the value from column Jan, Feb, Mar, Apr. I was trying to used the Case When clause on the "Code" column to look for '401040110' and '441010300' but still can't figure it out.

    How can this be accomplish?

    Thursday, February 26, 2015 10:04 PM

Answers

  • SELECT B.[Year],E.[Description] as GLClass, D.Code, 
    Case WHEN D.[GLDescription]='Freight-in (Go)' Or D.[GLDescription]='Freight Savings' 
    Then'Freight-in (Go) and Freight Savings'
    else D.[GLDescription]
    End as GLDescription, Sum(A.Jan) as Jan, Sum(A.Feb) as Feb, Sum(A.Mar) as Mar, Sum(A.Apr)as Apr
    From GeneralLedgers A inner join
    Years B on A.YearID = B.ID
    inner join CostCenters C on
    A.CostCenterID = C.ID
    inner join GLCodes D on
    A.GLCodeID = D.ID inner join
    GLClassificationTypes E on 
    D.GLClassificationTypeID = E.ID
    WHERE A.YearID = '13' 
    Group By B.[Year], D.Code, E.[Description],[GLDescription]
    Order by D.Code, E.[Description]

    • Marked as answer by Eric__Zhang Friday, March 6, 2015 2:40 AM
    Thursday, February 26, 2015 10:12 PM