Tricky Problem with Group by Clause

Question

Tricky Problem with Group by Clause

pshrvankumar

Grasshopper

Points: 21
More actions
November 4, 2015 at 10:17 pm

#322126

Hi All,
I have trapped with below problem, which am facing this issue from last 4 days, below is my table: CSAT
EQIDRevis FileName JobcodeJobqalMaterial
11Mick 1 A10
11Mick 2 B20
11Mick 3 C30
11Mick 4 C40
11Mick 5 D50
12Frank 1 A60
12Frank 2 B70
12Frank 6 D80
I need to get the Output as below table.
EQIDJOBCODEJOBQUALMATERIALMATERIAL
11 A 10 60
12 B 20 70
13 C 30 NULL
14 C 40 NULL
15 D 50 NULL
16 D NULL 80
I tried the below queries but helpless. Please help and correct my queries.
SELECT c.Revis,c2.Revis,c.eqid,c.jobcode,c.jobqal,c.material,c2.material
from CSAT c join CSAT c2 on c.EQID = c2.EQID
where c.Revis=1 and c2.Revis=2
and c.Jobcode = c2.Jobcode
and c.Jobqal = c2.Jobqal

Viewing 7 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004654 More actions · Answer 1

pshrvankumar (11/4/2015)
Hi All,
I have trapped with below problem, which am facing this issue from last 4 days, below is my table: CSAT
EQIDRevis FileName JobcodeJobqalMaterial
11Mick 1 A10
11Mick 2 B20
11Mick 3 C30
11Mick 4 C40
11Mick 5 D50
12Frank 1 A60
12Frank 2 B70
12Frank 6 D80
I need to get the Output as below table.
EQIDJOBCODEJOBQUALMATERIALMATERIAL
11 A 10 60
12 B 20 70
13 C 30 NULL
14 C 40 NULL
15 D 50 NULL
16 D NULL 80
I tried the below queries but helpless. Please help and correct my queries.
SELECT c.Revis,c2.Revis,c.eqid,c.jobcode,c.jobqal,c.material,c2.material
from CSAT c join CSAT c2 on c.EQID = c2.EQID
where c.Revis=1 and c2.Revis=2
and c.Jobcode = c2.Jobcode
and c.Jobqal = c2.Jobqal

What do you want to do if "Joe" shows up with a JobCode of 1 and JobQual of "A"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pshrvankumar Grasshopper Points: 21 More actions · Answer 2

Hi Jeff, I dint get you. Am working on a task to get the data into the grid but am stuck with this issue. Kindly help..

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Try this

if object_id('tempdb..#data', 'U') is not null

drop table #data;

create table #data

(

EQID int

,Revis int

,Filename varchar(20)

,JobCode int

,JobQual char(1)

,Material int

);

insert #data

(EQID, Revis, Filename, JobCode, JobQual, Material)

values (1, 1, 'Mick', 1, 'A', 10),

(1, 1, 'Mick', 2, 'B', 20),

(1, 1, 'Mick', 3, 'C', 30),

(1, 1, 'Mick', 4, 'C', 40),

(1, 1, 'Mick', 5, 'D', 50),

(1, 2, 'Frank', 1, 'A', 60),

(1, 2, 'Frank', 2, 'B', 70),

(1, 2, 'Frank', 6, 'D', 80);

select d.EQID

,d.JobCode

,d.JobQual

,Material1 = min(d.Material)

,Material2 = nullif(max(d.Material), min(d.Material))

from #data d

group by d.EQID

,d.JobCode

,d.JobQual;

After that, if you try adding a row to your source data in line with Jeff's question, you might understand why he asked it.

Ant-Green SSC Guru Points: 113566 More actions · Answer 4

pshrvankumar (11/4/2015)
Hi Jeff, I dint get you. Am working on a task to get the data into the grid but am stuck with this issue. Kindly help..

What Jeff is asking is that how far down the tree can this go, how many levels are there.

You example shows Mick and Frank with a job code 1 and Job Qual A, the expected outcome shows 2 material columns

If you had 2 other people, Bob and Fred also with job code 1 and job Qual A, is the expected outcome to show 4 material columns

pshrvankumar Grasshopper Points: 21 More actions · Answer 5

Thanks Anthony,Problem was partial solved but I have below table. Please help me with the below puzzle. My table CSAT has below columns.

TRANIDREVISION FileName JobCode JobQualDesc Cost

11 Test1 1AApplied 5

11 Test12BBond 6

11 Test13AApplied 10

11 Test14AApplied 3

11 Test15AApplied 2

12 Test21AApplied 7

12 Test22BBond 3

12 Test26AApplied 6

21 Test11ERejected 45

21 Test12FFail 64

22 Test21ERejected 35

22 Test22FFail 77

22 Test28YSelected 87

I need the output as below. Basically I Wanted to Link the Result (Cost and File name(FN)) against Revision1 and Revision2 based on Job Code and Job Qual

TRANIDJob_CodeJob_QualDescCost_Revis1Cost_Revis2FNRevis1FNRevis2

11AApplied57 Test1Test2

12BBond63 Test1Test2

13AApplied10NULL Test1NULL

14AApplied3NULL Test1NULL

15AApplied2NULL Test1NULL

16AAppliedNULL 6 NULLTest2

Phil Parkin SSC Guru Points: 247180 More actions · Answer 6

pshrvankumar (11/5/2015)
Thanks Anthony,Problem was partial solved but I have below table. Please help me with the below puzzle. My table CSAT has below columns.
TRANIDREVISION FileName JobCode JobQualDesc Cost
11 Test1 1AApplied 5
11 Test12BBond 6
11 Test13AApplied 10
11 Test14AApplied 3
11 Test15AApplied 2
12 Test21AApplied 7
12 Test22BBond 3
12 Test26AApplied 6
21 Test11ERejected 45
21 Test12FFail 64
22 Test21ERejected 35
22 Test22FFail 77
22 Test28YSelected 87
I need the output as below. Basically I Wanted to Link the Result (Cost and File name(FN)) against Revision1 and Revision2 based on Job Code and Job Qual
TRANIDJob_CodeJob_QualDescCost_Revis1Cost_Revis2FNRevis1FNRevis2
11AApplied57 Test1Test2
12BBond63 Test1Test2
13AApplied10NULL Test1NULL
14AApplied3NULL Test1NULL
15AApplied2NULL Test1NULL
16AAppliedNULL 6 NULLTest2

Please edit your post so that the desired output matches your sample source data. This requirement is puzzling enough without throwing in guesswork.

Perhaps you would also explain why sometimes %Revis1 is NULL and other times it's %Revis2?