Case With Group by Row Values into Column

Question

Case With Group by Row Values into Column

farooq.hbs

SSCrazy

Points: 2083
More actions
June 15, 2012 at 1:21 am

#392530

Hi Guys
TemplateId ItemNAme ItemDate Value
010HardSaving2010-01-01200
011HardSaving2010-02-01300
012HardSaving2010-03-01200
013HardSaving2010-04-01400
014CostAvoidance2010-01-01500
015CostAvoidance2010-02-01600
016CostAvoidance2010-03-01200
017HardSaving2011-01-01500
018HardSaving2011-02-015692
019HardSaving2011-03-016058
020CostAvoidance2011-01-01528
021CostAvoidance2011-02-01962
022CostAvoidance2011-03-01692
I need Solution Like this
ItemName HardSaving2010 Hardsaving2011 CostAvoidance2010CostAvoidance2011
HArdSaving 11000 1235013001654
I Tried With this Query
Case When ItemNAme = 'Hard Savings' then sum(m.value) End as HardSavingFY10,
Case When ItemNAme = 'Hard Savings' then sum(m.value) End as HardSavingFY11,
Case When ItemNAme = 'Cost Avoidance' then sum(m.value) End as CostAvoidanceFY10,
Case When ItemNAme = 'Cost Avoidance' then sum(m.value) End as CostAvoidanceFY10
From MyTable Group by itemNAme,ItemDate
Result Set Retriving two rows but i need only one row

Viewing 15 posts - 1 through 15 (of 21 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

First of all, what you posted as a query isn't, it is just a part of the query. We really need to see the whole query to be able to assist.

What would really help us help you is if you would post the DDL for the table(s) (CREATE TABLE statements), sample data as a series of INSERT INTO statements that we can cut, paste, and run to populate your tables, and the expected results based on your sample data.

vinu512 SSCoach Points: 15729 More actions · Answer 2

This is the query that I tried and is working fine according to the Logic.

--Creating Table

Create table Ex

(TemplateId int,

ItemNAme Varchar(20),

ItemDate Date,

Value int )

--Inserting Sample Data

Insert Into Ex

Select 010,'HardSaving','2010-01-01',200

Union ALL

Select 011,'HardSaving','2010-02-01',300

Union ALL

Select 012,'HardSaving','2010-03-01',200

Union ALL

Select 013,'HardSaving','2010-04-01',400

Union ALL

Select 014,'CostAvoidance','2010-01-01',500

Union ALL

Select 015,'CostAvoidance','2010-02-01',600

Union ALL

Select 016,'CostAvoidance','2010-03-01',200

Union ALL

Select 017,'HardSaving','2011-01-01',500

Union ALL

Select 018,'HardSaving','2011-02-01',5692

Union ALL

Select 019,'HardSaving','2011-03-01',6058

Union ALL

Select 020,'CostAvoidance','2011-01-01',528

Union ALL

Select 021,'CostAvoidance','2011-02-01',962

Union ALL

Select 022,'CostAvoidance','2011-03-01',692

--Query For Your Requirement

Select ItemNAme,

Sum(Case When DATEPART(YY, ItemDate) = 2010 AND ItemNAme = 'HardSaving' Then Value Else 0 End) As HardSavingFY10,

Sum(Case When DATEPART(YY, ItemDate) = 2011 AND ItemNAme = 'HardSaving' Then Value Else 0 End) As HardSavingFY11,

Sum(Case When DATEPART(YY, ItemDate) = 2010 AND ItemNAme = 'CostAvoidance' Then Value Else 0 End) As CostAvoidanceFY10,

Sum(Case When DATEPART(YY, ItemDate) = 2011 AND ItemNAme = 'CostAvoidance' Then Value Else 0 End) As CostAvoidanceFY11

From Ex

Group By ItemNAme

Why should it return only one row when there are two items in your Sample Data??

If you want only one row then you will have to take out one item by filtering the Result Set.

If this doesn't help then please elaborate on what you are trying to do.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

farooq.hbs SSCrazy Points: 2083 More actions · Answer 3

farooq.hbs

SSCrazy

Points: 2083

June 15, 2012 at 2:59 am

#1501454

Thanks Buddy...

I got the Solution

vinu512 SSCoach Points: 15729 More actions · Answer 4

farooq.hbs (6/15/2012)
Thanks Buddy...
I got the Solution

You're welcome.

Could you please post the solution so that other visitors to this thread(including me and Lynn) could know what was wrong and how you made it right??...Would be helpful for a lotta people.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

farooq.hbs SSCrazy Points: 2083 More actions · Answer 5

This is the Solution

Select

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

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

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

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

Group By item_name

farooq.hbs SSCrazy Points: 2083 More actions · Answer 6

Hey Guys

If I want to make Sum Of this HardSAvingFY11 And HardSavingFY12 in the Query ?? Is there any option for this?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

farooq.hbs (6/15/2012)
Hey Guys
If I want to make Sum Of this HardSAvingFY11 And HardSavingFY12 in the Query ?? Is there any option for this?

-- Enhanced Query For Your Requirement

SELECT

HardSavingFY10,

HardSavingFY11,

HardSavingTotal = HardSavingFY10+HardSavingFY11,

CostAvoidanceFY10,

CostAvoidanceFY11,

CostAvoidanceTotal = CostAvoidanceFY10+CostAvoidanceFY11

FROM (

Select

Sum(Case When x.ItemYear = 2010 AND ItemNAme = 'HardSaving' Then Value Else 0 End) As HardSavingFY10,

Sum(Case When x.ItemYear = 2011 AND ItemNAme = 'HardSaving' Then Value Else 0 End) As HardSavingFY11,

Sum(Case When x.ItemYear = 2010 AND ItemNAme = 'CostAvoidance' Then Value Else 0 End) As CostAvoidanceFY10,

Sum(Case When x.ItemYear = 2011 AND ItemNAme = 'CostAvoidance' Then Value Else 0 End) As CostAvoidanceFY11

FROM Ex

CROSS APPLY (SELECT ItemYear = DATEPART(YY, ItemDate)) x

) d

^{“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

farooq.hbs SSCrazy Points: 2083 More actions · Answer 8

Actually This is My Query. In this Query , How to sum

Declare @portfolioId Varchar(8000),

@ParentWorkids Varchar(50)

Set @portfolioId = '1800m380000iot22g3t0000000_1800m380000io7c5nr50000000'

set @ParentWorkids = '1801a2g0000ih0vl2abg000000'

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,

(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = ' Primary Financial BBP Metric Impacted') as PrimaryBBPProcess,

(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Primary Fin. BBP Metric (Level 2)') as PrimaryBBPlevel2,

(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Primary Fin. BBP Metric (Level 3)') as PrimaryBBPlevel3,

(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Workstream Business Group') as WorkstreamBusinessGroup,

(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Workstream Business Focus Area') as WorkBusArea,

(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Workstream Project Focus Area') as WorkProjectArea,

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

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

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

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

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

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

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

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

FromView_Work_Hierarchy vh

Inner join View_Shared_Portfolio vp On vp.work_id = vh.child_work_id

Inner Join View_Metric_Instance mi on mi.linked_project_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.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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

SELECT from the whole query as a derived table. Create the totals as new columns in the output list.

Are you happy with your query?

How does it perform?

Are the results correct?

How many values are returned from the correlated subqueries in the output list?

^{“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

farooq.hbs SSCrazy Points: 2083 More actions · Answer 10

Yup results are Correct

but Honestly not hapy with my queryy

Plz do let me know if there is any other option so tht query runs fast

Sean Lange SSC Guru Points: 286590 More actions · Answer 11

farooq.hbs (6/15/2012)
Yup results are Correct
but Honestly not hapy with my queryy
Plz do let me know if there is any other option so tht query runs fast

The answer starts here. http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Sean Lange (6/15/2012)
farooq.hbs (6/15/2012)
Yup results are Correct
but Honestly not hapy with my queryy
Plz do let me know if there is any other option so tht query runs fast
The answer starts here. http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Have a look at the query Sean, the table sources are all views - meaning a heck of a lot of DDL and not a little DML to boot. I reckon we can give the OP some assistance without it, and ask if necessary.

^{“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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

Here's a start. Test and comment.

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,

--(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = ' Primary Financial BBP Metric Impacted') as PrimaryBBPProcess,

--(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Primary Fin. BBP Metric (Level 2)') as PrimaryBBPlevel2,

--(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Primary Fin. BBP Metric (Level 3)') as PrimaryBBPlevel3,

--(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Workstream Business Group') as WorkstreamBusinessGroup,

--(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Workstream Business Focus Area') as WorkBusArea,

--(select tag_name from view_tag where object_id = vh.child_work_id and tagset_name = 'Workstream Project Focus Area') as WorkProjectArea,