CODEWITHSUNDEEP
sqlsql-server
Jake12342134
Jake12342134

Reputation: 1767

Selecting multiple rows of a column in a SQL Server query

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

Upvotes: 1

Views: 133

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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;

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

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

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

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

Upvotes: 2

Related Questions