Selecting multiple rows of a column in a SQL Server query

Question

I have a table, let's call it Case the case table contains basic information about a case, such as the CaseNumber and the CaseOwner.

In a separate table, CaseDetails a number of phone numbers associated with a particular case are stored. The Type column within CaseDetails represents the type of phone number e.g. Home, mobile or work. These two tables link on CaseNumber.

More clearly:

Case

CaseNumber     CaseOwner
------------------------
1              Bob
2              Jim
3              Gary

CaseDetails

CaseNumber     Detail        Type
----------------------------------
1              0123456789    1
1              1111111111    2
2              2222222222    1
1              0101001011    3
2              1234123412    2
3              0000011111    1
3              1231231231    2

I want to be able to write a query that can pull back the basic details of a case, as well as ALL of the associated phone numbers.

In my head I imagined the query to go something like the following

Select 
    CaseNumber, CaseOwner, 
    Detail where Type = 1, Detail where Type = 2, Detail where Type = 3
From
    Case
Join 
    CaseDetails on Case.CaseNumber = CaseDetails.CaseNumber

That way each individual Detail could be extracted from the CaseDetails table using the type column. However this is syntactically incorrect and won't execute.

How exactly would I construct a query to extract this information? I can't seem to find the information on this on Google as I'm not sure what to search for.

The whole point of this is so that I can find all of the associated numbers for a particular case and store them in one location.

This is what I want the final output to look like

CaseNumber    CaseOwner    Detail1       Detail2        Detail3
-------------------------------------------------------------------
1             Bob          0123456789    1111111111     0000011111

Fahmi · Accepted Answer · 2018-11-19 11:42:41Z

2

You can try below using CASE WHEN expression

Select a.CaseNumber, CaseOwner, max(case when Type = 1 then detail end) as as detail1, max(case when Type = 2 then detail end) as detail2, max(case when Type = 3 then detail end) as detail3
From Case a
Join CaseDetails b on a.CaseNumber = b.CaseNumber
group by a.CaseNumber, CaseOwner

OR you can use PIVOT

with cte as
(
 Select a.CaseNumber, CaseOwner, type, detail
    From Case a
    Join CaseDetails b on a.CaseNumber = b.CaseNumber
    group by a.CaseNumber, CaseOwner
)
select casenumber, caseowner,pv.* 
from cte pivot(max(detail) for type in (1,2,3)) as pv

edited Nov 19, 2018 at 11:42

answered Nov 19, 2018 at 11:34

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

junketsu Over a year ago

Pivot is your best option because it leverages your already persisted column [type] and is what your actually shooting for. Keep in mind this will get you to the last row of question; where you go from there is completely up to you i.e. ask another question etc.

Gordon Linoff · Accepted Answer · 2018-11-19 13:15:34Z

1

You can use conditional aggregation:

Select c.CaseNumber, c.CaseOwner,
       max(case when cd.type = 1 then cd.Detail end) as detail_1,
       max(case when cd.type = 2 then cd.Detail end) as detail_2,
       max(case when cd.type = 3 then cd.Detail end) as detail_3
From Case c Join
     CaseDetails cd
     on c.CaseNumber = cd.CaseNumber
group by c.CaseNumber, c.CaseOwner;

EDIT:

You can also do this using outer apply:

select c.*, cd.*
from case c outer apply
     (select max(case when cd.type = 1 then cd.Detail end) as detail_1,
             max(case when cd.type = 2 then cd.Detail end) as detail_2,
             max(case when cd.type = 3 then cd.Detail end) as detail_3
      from CaseDetails cd
      where c.CaseNumber = cd.CaseNumber
     ) cd;

edited Nov 19, 2018 at 13:15

answered Nov 19, 2018 at 11:37

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

4 Comments

Jake12342134 Over a year ago

Okay so I've managed to get it working in an isolated case, however in my actual query where I'm implementing this code I have a large number of select statements that are selecting data from 10+ tables. Do I have to include each selected column in the group by clause?

Gordon Linoff Over a year ago

@Jake12342134 . . . No. But it is only possible to answer the question that you actually ask. I would suggest that you ask another question with a better idea of what you want to accomplish -- if the edit doesn't do what you need.

Jake12342134 Over a year ago

Why is max used in this scenario?

Gordon Linoff Over a year ago

@Jake12342134 . . . No particular reason. I am guessing that the types are not duplicated. Some aggregation is needed and max() and min() would return the same results.

Zaynul Abadin Tuhin · Accepted Answer · 2018-11-19 11:44:16Z

0

use pivot

select  CaseNumber,CaseOwner,
 [1] as detail1, 
 [2] as detail2 ,
 [3] as detail3
 from 
(select c1.CaseNumber,c1.CaseOwner,c2.Detail,c2.Type
    From Case c1
    Join CaseDetails c2
    on c1.CaseNumber = c2.CaseNumber
) src
PIVOT
(
max(Detail) for Type in ([1],[2],[3])
) pvt

answered Nov 19, 2018 at 11:44

Zaynul Abadin Tuhin

32.1k6 gold badges37 silver badges66 bronze badges

Collectives™ on Stack Overflow

Selecting multiple rows of a column in a SQL Server query

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related