Case With Group by Row Values into Column

  • I'm right with you on that Sean. There are some other quirks in the query which suggest that if it's generating correct results, it is, to some extent, a happy accident.

    FWIW I think simplifying it may iron out some of the controversial areas.

    Declare @portfolioId Varchar(8000),

    @ParentWorkids Varchar(50)

    Set @portfolioId = '1800m380000iot22g3t0000000_1800m380000io7c5nr50000000' -- this looks like TWO concatenated portfolioIds

    set @ParentWorkids = '1801a2g0000ih0vl2abg000000'

    ---------------------------------------------------------------------------

    SELECT child_work_id = [object_id],

    PrimaryBBPProcess = MAX(CASE WHEN tagset_name = ' Primary Financial BBP Metric Impacted' THEN tag_name ELSE NULL END),

    PrimaryBBPlevel2 = MAX(CASE WHEN tagset_name = 'Primary Fin. BBP Metric (Level 2)' THEN tag_name ELSE NULL END),

    PrimaryBBPlevel3 = MAX(CASE WHEN tagset_name = 'Primary Fin. BBP Metric (Level 3)' THEN tag_name ELSE NULL END),

    WorkstreamBusinessGroup = MAX(CASE WHEN tagset_name = 'Workstream Business Group' THEN tag_name ELSE NULL END),

    WorkBusArea = MAX(CASE WHEN tagset_name = 'Workstream Business Focus Area' THEN tag_name ELSE NULL END),

    WorkProjectArea = MAX(CASE WHEN tagset_name = 'Workstream Project Focus Area' THEN tag_name ELSE NULL END)

    INTO #View_Tag

    FROM view_tag

    GROUP BY [object_id]

    ---------------------------------------------------------------------------

    SelectTOP 20

    vh.parent_work_id As ParentWorkId,

    vh.parent_name As ParentName,

    vh.child_work_id As ChildWorkId,

    vh.child_name As ChildName,

    vh.child_sequence_id As SequencesNo,

    vh.parent_owner_id As OwnerNo,

    vh.child_work_type_code As ChildworkCode,

    vh.child_status_current As ChildStatus,

    (Select (first_name+ ' ' + last_name)

    from view_user

    where user_id = vh.parent_owner_id) as ProjectManager,

    Stuff((select ',' + (first_name+ ' ' + last_name)

    from vieW_user vw

    join view_work_role r on vw.user_id = r.user_id

    Join view_configurable_role cr On cr.role_id =r.role_id and cr.name = 'Process Owner' and r.work_id = vh.child_work_id

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS ProjectSponsor,

    (select name

    from View_Work

    where work_id = vh.child_work_id and type_name = 'Location') As Location,

    Stuff((select ',' +(first_name+ ' ' + last_name)

    from vieW_user vw

    join view_work_role r on vw.user_id = r.user_id

    where r. role = 'Championship' and r.work_id = vh.child_work_id

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') as OtherTeamMembers,

    Stuff((select (first_name+ ' ' + last_name)

    from vieW_user vw

    join view_work_role r on vw.user_id = r.user_id

    Join view_configurable_role cr On cr.role_id = r.role_id and cr.name = 'CI Manager' and r.work_id = vh.child_work_id

    FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,1,'') AS CIManager,

    (Select (first_name+ ' ' + last_name)

    From view_User vw

    join View_work_role r on vw.user_id = r.user_id

    Join view_tag vt on vt.object_id = r.work_id and vt.tag_name = 'Controller' and r.work_id = vh.child_work_id) AS Controller,

    (Select (first_name+ ' ' + last_name)

    From view_User vw

    join View_work_role r on vw.user_id = r.user_id

    Join view_tag vt on vt.object_id = r.work_id

    and vt.tag_name = 'Master Black Belt' and r.work_id = vh.child_work_id) as MasterBB,

    (Select value from view_work_custom_field where work_id = vh.child_work_id and name = 'Project Objective') as ProjectObjective,

    (Select value from view_work_custom_field where work_id = vh.child_work_id and name = 'Problem Statement') as ProblemStatement,

    vt.PrimaryBBPProcess,

    vt.PrimaryBBPlevel2,

    vt.PrimaryBBPlevel3,

    vt.WorkstreamBusinessGroup,

    vt.WorkBusArea,

    vt.WorkProjectArea,

    ---------------------------------------------------------------------------

    x.HardSavingFY10,

    x.HardSavingFY11,

    HardSavingTotal = x.HardSavingFY10 + x.HardSavingFY11,

    x.CostAvoidanceFY10,

    x.CostAvoidanceFY11,

    x.OneTimeImpCostsFY10,

    x.OneTimeImpCostsFY11,

    x.CapitalExpenditures,

    x.CapitalExpenditures

    ---------------------------------------------------------------------------

    FROM View_Work_Hierarchy vh

    LEFT JOIN #View_Tag vt ON vt.child_work_id = vh.child_work_id

    ---------------------------------------------------------------------------

    CROSS APPLY (

    SELECT mt.item_name,

    HardSavingFY10= Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),

    HardSavingFY11= Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Hard Savings' then m.value End),

    CostAvoidanceFY10= Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Cost Avoidance' then m.value End),

    CostAvoidanceFY11= Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Cost Avoidance' then m.value End),

    OneTimeImpCostsFY10 = Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),

    OneTimeImpCostsFY11 = Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),

    CapitalExpenditures = Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Capital Expenditures' then m.value End),

    CapitalExpenditures =Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Capital Expenditures' then m.value End)

    FROM View_Shared_Portfolio vp

    --Inner Join View_Metric_Instance mi on mi.linked_project_id = vh.child_work_id -- outer ref

    Inner Join View_Metric_Instance mi on mi.linked_project_id = vp.work_id -- doesn't need to be outer ref (vp.work_id = vh.child_work_id)

    Inner Join View_Metric_Template mt on mt.metric_template_id = mi.metric_template_id

    Inner Join fn_Metrics('2011-01-01', '2012-12-31' )m ON m.metric_instance_id = mi.metric_instance_id

    and mt.item_id = m.template_item_id

    WHERE vp.work_id = vh.child_work_id -- outer ref

    AND (vp.shared_portfolio_id = @portfolioId Or @portfolioId Is nuLL)

    and mt.template_name = N'Savings / Controller Validation'

    and mt.locale_id = N'en'

    and mt.item_name IN ( 'Hard Savings','Cost Avoidance','One-Time / Impl. Costs','Capital Expenditures')

    GROUP BY mt.item_name

    ) x

    ---------------------------------------------------------------------------

    Where--(vp.shared_portfolio_id = @portfolioId Or @portfolioId Is nuLL)

    --And

    (vh.parent_work_id = @ParentWorkids Or @ParentWorkids Is Null)

    and vh.child_work_type_code Like 'Tollgate%'

    and vh.child_depth != 0

    --and vh.child_work_id = '1800m380000ioetr0e5g000000'

    --and mt.template_name = N'Savings / Controller Validation'

    --and mt.locale_id = N'en'

    --and mt.item_name IN ( 'Hard Savings','Cost Avoidance','One-Time / Impl. Costs','Capital Expenditures')

    GROUP BY vh.parent_work_id,vh.parent_name,vh.child_work_id,vh.child_name,vh.child_sequence_id,vh.parent_owner_id,

    vh.child_work_type_code,vh.child_status_current --,mt.item_name

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey Guys

    I want to add column value and show in the next column.

    Like (HardSavingFY10 + HardSavingFY11)

    And show it in the another value for each rows

    Is there any possibilities..??? If the Column is Null then it is not summin

  • If i Execute the above query

    I'm getting Result set as

    Msg 2714, Level 16, State 6, Line 8

    There is already an object named '#View_Tag' in the database.

  • farooq.hbs (6/17/2012)


    If i Execute the above query

    I'm getting Result set as

    Msg 2714, Level 16, State 6, Line 8

    There is already an object named '#View_Tag' in the database.

    The #temp table will exist when the code is successfully run. For repeated runs of the same batch you must drop the #temp table.

    IF OBJECT_ID('tempdb..#View_Tag') IS NOT NULL DROP TABLE #View_Tag

    This statement can be placed anywhere in the batch before the temp table is created or after it's finished with. Most folks would put the statement at the top of the batch.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey

    My Metric Values returning two times Lyk

    IsNUll(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY11,

    IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY12,

    IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY11,

    IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY12,

    IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY11,

    IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY12,

    HardSavingFY11 and Cost AvoidingFY12 Value are not coming in one row for the Project.it is making two rows for hardsaving and cost avoidingFY12 if two value is not null..

    I used Max Option for this but i got an error

    Help me on this

  • farooq.hbs (6/21/2012)


    Hey

    My Metric Values returning two times Lyk

    IsNUll(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY11,

    IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Hard Savings' then m.value End),0) as HardSavingFY12,

    IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY11,

    IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'Cost Avoidance' then m.value End),0) as CostAvoidanceFY12,

    IsNull(Sum(Case When Datepart(year,mdate)= 2011 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY11,

    IsNull(Sum(Case When Datepart(year,mdate)= 2012 and mt.item_name = 'One-Time / Impl. Costs' then m.value End),0) as OneTimeImpCostsFY12,

    HardSavingFY11 and Cost AvoidingFY12 Value are not coming in one row for the Project.it is making two rows for hardsaving and cost avoidingFY12 if two value is not null..

    I used Max Option for this but i got an error

    Help me on this

    Not enough information. Can you post the whole query?

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Hey Chris

    I got the Solution

    I removed Group by item_name in the Query

    thankz For the reply..

    I have one more issue in "catch All" queries

    Without declaring Local Variable to check in the Where Condition for the Query

    How to Check

    This is Query With local variable

    Declare @portfolioId Varchar(8000),

    @ParentWorkids Varchar(50)

    Set @portfolioId = '1800m380000iot22g3t0000000_1800m380000io7c5nr50000000'

    set @ParentWorkids = '1801a2g0000ih0vl2abg000000'

    Select * from Mytable1 join My table2 On

    (vp.shared_portfolio_id = @portfolioId Or @portfolioId Is nuLL)

    And

    (vh.parent_work_id = @ParentWorkids Or @ParentWorkids Is Null)

    Now i want to write a query Without Declaring local variable because vp.shared_portfolio_id can be Null Or Single Id Or Multiple Id So i should use IN Condition in the Where Clause

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply