locked
Problem in Query RRS feed

  • Question

  • Hello how r u every body

    i face a problem please any body help me

    i run a query

    SELECT Level1Code, Level2Code, Level3Code, Level4Code, [1],[2],[3],[4]

    FROM vwForRep

    pivot

    (

    Sum(ReqQty) for LotNo in ([1],[2],[3],[4])

    ) as PivotState

    but the answer does not proper

    the result is this why does not group the value and why show null value 

    please any body help me

    Monday, November 23, 2015 11:15 AM

Answers

  • Hello Abdullah,

    I think  there may be some other column that has different values that differentiate in the view

    below code that could eliminate the other column and give you the desired result

    --Method 1
    SELECT Level1Code, Level2Code, Level3Code, Level4Code, [1],[2],[3],[4] 
    from 
    (select Level1Code, Level2Code, Level3Code, Level4Code,ReqQty  FROM vwForRep 
    ) as  a
    pivot 
    (
    Sum(ReqQty) for LotNo in ([1],[2],[3],[4])
    ) as PivotState
    -- Method 2
    SELECT Level1Code, Level2Code, Level3Code, Level4Code, [1],[2],[3],[4] 
    from 
    (select Level1Code, Level2Code, Level3Code, Level4Code,sum(ReqQty)ReqQty  FROM vwForRep 
    Group by Level1Code, Level2Code, Level3Code, Level4Code) as  a
    pivot 
    (
    Sum(ReqQty) for LotNo in ([1],[2],[3],[4])
    ) as PivotState
    --Example that explains the issue
    -- colhidden is issue that duplicate records as seen
    WITH CTE1 as
    (select 1 as  no,  2  as val, 2 as colhidden,2 as coldisplay union all 
    select 1 ,  3 val, 3 as colhidden,2 as coldisplay  )
    select coldisplay,[1] From CTE1
    pivot (Sum(val) for no in ([1])) as PivotState


    Thanks Saravana Kumar C

    • Proposed as answer by Charlie Liao Tuesday, November 24, 2015 5:45 AM
    • Marked as answer by Abdullah_Umar Tuesday, November 24, 2015 5:45 AM
    Monday, November 23, 2015 11:59 AM

All replies

  • Hello Abdullah,

    I think  there may be some other column that has different values that differentiate in the view

    below code that could eliminate the other column and give you the desired result

    --Method 1
    SELECT Level1Code, Level2Code, Level3Code, Level4Code, [1],[2],[3],[4] 
    from 
    (select Level1Code, Level2Code, Level3Code, Level4Code,ReqQty  FROM vwForRep 
    ) as  a
    pivot 
    (
    Sum(ReqQty) for LotNo in ([1],[2],[3],[4])
    ) as PivotState
    -- Method 2
    SELECT Level1Code, Level2Code, Level3Code, Level4Code, [1],[2],[3],[4] 
    from 
    (select Level1Code, Level2Code, Level3Code, Level4Code,sum(ReqQty)ReqQty  FROM vwForRep 
    Group by Level1Code, Level2Code, Level3Code, Level4Code) as  a
    pivot 
    (
    Sum(ReqQty) for LotNo in ([1],[2],[3],[4])
    ) as PivotState
    --Example that explains the issue
    -- colhidden is issue that duplicate records as seen
    WITH CTE1 as
    (select 1 as  no,  2  as val, 2 as colhidden,2 as coldisplay union all 
    select 1 ,  3 val, 3 as colhidden,2 as coldisplay  )
    select coldisplay,[1] From CTE1
    pivot (Sum(val) for no in ([1])) as PivotState


    Thanks Saravana Kumar C

    • Proposed as answer by Charlie Liao Tuesday, November 24, 2015 5:45 AM
    • Marked as answer by Abdullah_Umar Tuesday, November 24, 2015 5:45 AM
    Monday, November 23, 2015 11:59 AM
  • Hi,

    Please post your question in MSDN T-SQL forum for quick response. 


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Monday, November 23, 2015 12:06 PM
  • you can simply use this

    SELECT Level1Code,
    Level2Code,
    Level3Code,
    Level4Code,
    SUM(CASE WHEN LotNo = 1 THEN ReqQty END) AS [1],
    SUM(CASE WHEN LotNo = 2 THEN ReqQty END) AS [2],
    SUM(CASE WHEN LotNo = 3 THEN ReqQty END) AS [3],
    SUM(CASE WHEN LotNo = 4 THEN ReqQty END) AS [4]
    FROM vwForRep
    GROUP BY Level1Code,
    Level2Code,
    Level3Code,
    Level4Code
    


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, November 23, 2015 2:41 PM
  • Thanks SaravanaC 

    my problem is solved please can u explain why this reason

    Tuesday, November 24, 2015 5:51 AM
  • Thanks SaravanaC 

    my problem is solved please can u explain why this reason

    Hi Abdullah,

    i think i have explained the reason in below example

    --Example that explains the issue
    -- colhidden is issue that duplicate records as seen
    WITH CTE1 as
    (select 1 as  no,  2  as val, 2 as colhidden,2 as coldisplay union all 
    select 1 ,  3 val, 3 as colhidden,2 as coldisplay  )
    select coldisplay,[1] From CTE1
    pivot (Sum(val) for no in ([1])) as PivotState

    Here when you directly pivot the table, it takes account of all the columns in the table(not just the column in select clause),

    to avoid this you can use a derived table , like the my previous response


    Thanks Saravana Kumar C


    • Edited by SaravanaC Tuesday, November 24, 2015 7:29 AM
    Tuesday, November 24, 2015 7:25 AM