Mixing
Selecting multiple rows of a column in a SQL Server query
up vote
1
down vote
favorite
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
sql
sql-server edited 2 days ago
marc_s
565k12610921245
asked 2 days ago
Jake12342134
786
add a comment |
up vote
1
down vote
favorite
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
sql
sql-server edited 2 days ago
marc_s
565k12610921245
asked 2 days ago
Jake12342134
786
what is your dbms name
– fa06
2 days ago
Apologies, SQL Server
– Jake12342134
2 days ago
add a comment |
up vote
1
down vote
favorite
up vote
1
down vote
favorite
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
sql
sql-server edited 2 days ago
marc_s
565k12610921245
asked 2 days ago
Jake12342134
786
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
sql
sql-server sql
sql-server edited 2 days ago
marc_s
565k12610921245
asked 2 days ago
Jake12342134
786
edited 2 days ago
marc_s
565k12610921245
asked 2 days ago
Jake12342134
786
edited 2 days ago
marc_s
565k12610921245
edited 2 days ago
marc_s
565k12610921245
edited 2 days ago
marc_s
565k12610921245
565k12610921245
asked 2 days ago
Jake12342134
786
asked 2 days ago
Jake12342134
786
asked 2 days ago
Jake12342134
786
786
what is your dbms name
– fa06
2 days ago
Apologies, SQL Server
– Jake12342134
2 days ago
add a comment |
what is your dbms name
– fa06
2 days ago
Apologies, SQL Server
– Jake12342134
2 days ago
what is your dbms name
– fa06
2 days ago
what is your dbms name
– fa06
2 days ago
Apologies, SQL Server
– Jake12342134
2 days ago
Apologies, SQL Server
– Jake12342134
2 days ago
add a comment |
4 Answers
4
active
oldest
votes
up vote
2
down vote
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
answered 2 days ago
fa06
8,7971816
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.
– junketsu
2 days ago
add a comment |
up vote
1
down vote
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;
answered 2 days ago
Gordon Linoff
744k32285390
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?
– Jake12342134
2 days 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.
– Gordon Linoff
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days ago
@Jake12342134 . . . No particular reason. I am guessing that thetypes are not duplicated. Some aggregation is needed andmax()andmin()would return the same results.
– Gordon Linoff
2 days ago
add a comment |
up vote
0
down vote
Try:
Select a.CaseNumber, a.CaseOwner,
min(CASE WHEN b.Type = 1 IS NOT NULL THEN b.Detail END) as 'Detail1',
min(CASE WHEN b.Type = 2 IS NOT NULL THEN b.Detail END) as 'Detail2',
min(CASE WHEN b.Type = 3 IS NOT NULL THEN b.Detail END) as 'Detail3'
From Case a
Join CaseDetails b
on a.CaseNumber = b.CaseNumber
GROUP BY a.CaseNumber, a.CaseOwner
answered 2 days ago
Mayank Porwal
2,5341620
add a comment |
up vote
0
down vote
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 2 days ago
Zaynul Abadin Tuhin
10.4k2730
add a comment |
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
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
answered 2 days ago
fa06
8,7971816
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.
– junketsu
2 days ago
add a comment |
up vote
2
down vote
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
answered 2 days ago
fa06
8,7971816
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.
– junketsu
2 days ago
add a comment |
up vote
2
down vote
up vote
2
down vote
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
answered 2 days ago
fa06
8,7971816
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
answered 2 days ago
fa06
8,7971816
edited 2 days ago
answered 2 days ago
fa06
8,7971816
answered 2 days ago
fa06
8,7971816
answered 2 days ago
fa06
8,7971816
8,7971816
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.
– junketsu
2 days ago
add a comment |
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.
– junketsu
2 days 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.
– junketsu
2 days 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.
– junketsu
2 days ago
add a comment |
up vote
1
down vote
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;
answered 2 days ago
Gordon Linoff
744k32285390
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?
– Jake12342134
2 days 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.
– Gordon Linoff
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days ago
@Jake12342134 . . . No particular reason. I am guessing that thetypes are not duplicated. Some aggregation is needed andmax()andmin()would return the same results.
– Gordon Linoff
2 days ago
add a comment |
up vote
1
down vote
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;
answered 2 days ago
Gordon Linoff
744k32285390
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?
– Jake12342134
2 days 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.
– Gordon Linoff
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days ago
@Jake12342134 . . . No particular reason. I am guessing that thetypes are not duplicated. Some aggregation is needed andmax()andmin()would return the same results.
– Gordon Linoff
2 days ago
add a comment |
up vote
1
down vote
up vote
1
down vote
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;
answered 2 days ago
Gordon Linoff
744k32285390
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;
answered 2 days ago
Gordon Linoff
744k32285390
edited 2 days ago
answered 2 days ago
Gordon Linoff
744k32285390
answered 2 days ago
Gordon Linoff
744k32285390
answered 2 days ago
Gordon Linoff
744k32285390
744k32285390
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?
– Jake12342134
2 days 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.
– Gordon Linoff
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days ago
@Jake12342134 . . . No particular reason. I am guessing that thetypes are not duplicated. Some aggregation is needed andmax()andmin()would return the same results.
– Gordon Linoff
2 days ago
add a comment |
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?
– Jake12342134
2 days 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.
– Gordon Linoff
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days ago
@Jake12342134 . . . No particular reason. I am guessing that thetypes are not duplicated. Some aggregation is needed andmax()andmin()would return the same results.
– Gordon Linoff
2 days 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?
– Jake12342134
2 days 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?
– Jake12342134
2 days 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.
– Gordon Linoff
2 days 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.
– Gordon Linoff
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days ago
Why is max used in this scenario?
– Jake12342134
2 days 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.– Gordon Linoff
2 days 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.– Gordon Linoff
2 days ago
add a comment |
up vote
0
down vote
Try:
Select a.CaseNumber, a.CaseOwner,
min(CASE WHEN b.Type = 1 IS NOT NULL THEN b.Detail END) as 'Detail1',
min(CASE WHEN b.Type = 2 IS NOT NULL THEN b.Detail END) as 'Detail2',
min(CASE WHEN b.Type = 3 IS NOT NULL THEN b.Detail END) as 'Detail3'
From Case a
Join CaseDetails b
on a.CaseNumber = b.CaseNumber
GROUP BY a.CaseNumber, a.CaseOwner
answered 2 days ago
Mayank Porwal
2,5341620
add a comment |
up vote
0
down vote
Try:
Select a.CaseNumber, a.CaseOwner,
min(CASE WHEN b.Type = 1 IS NOT NULL THEN b.Detail END) as 'Detail1',
min(CASE WHEN b.Type = 2 IS NOT NULL THEN b.Detail END) as 'Detail2',
min(CASE WHEN b.Type = 3 IS NOT NULL THEN b.Detail END) as 'Detail3'
From Case a
Join CaseDetails b
on a.CaseNumber = b.CaseNumber
GROUP BY a.CaseNumber, a.CaseOwner
answered 2 days ago
Mayank Porwal
2,5341620
add a comment |
up vote
0
down vote
up vote
0
down vote
Try:
Select a.CaseNumber, a.CaseOwner,
min(CASE WHEN b.Type = 1 IS NOT NULL THEN b.Detail END) as 'Detail1',
min(CASE WHEN b.Type = 2 IS NOT NULL THEN b.Detail END) as 'Detail2',
min(CASE WHEN b.Type = 3 IS NOT NULL THEN b.Detail END) as 'Detail3'
From Case a
Join CaseDetails b
on a.CaseNumber = b.CaseNumber
GROUP BY a.CaseNumber, a.CaseOwner
answered 2 days ago
Mayank Porwal
2,5341620
Try:
Select a.CaseNumber, a.CaseOwner,
min(CASE WHEN b.Type = 1 IS NOT NULL THEN b.Detail END) as 'Detail1',
min(CASE WHEN b.Type = 2 IS NOT NULL THEN b.Detail END) as 'Detail2',
min(CASE WHEN b.Type = 3 IS NOT NULL THEN b.Detail END) as 'Detail3'
From Case a
Join CaseDetails b
on a.CaseNumber = b.CaseNumber
GROUP BY a.CaseNumber, a.CaseOwner
answered 2 days ago
Mayank Porwal
2,5341620
answered 2 days ago
Mayank Porwal
2,5341620
answered 2 days ago
Mayank Porwal
2,5341620
answered 2 days ago
Mayank Porwal
2,5341620
2,5341620
add a comment |
add a comment |
up vote
0
down vote
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 2 days ago
Zaynul Abadin Tuhin
10.4k2730
add a comment |
up vote
0
down vote
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 2 days ago
Zaynul Abadin Tuhin
10.4k2730
add a comment |
up vote
0
down vote
up vote
0
down vote
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 2 days ago
Zaynul Abadin Tuhin
10.4k2730
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 2 days ago
Zaynul Abadin Tuhin
10.4k2730
answered 2 days ago
Zaynul Abadin Tuhin
10.4k2730
answered 2 days ago
Zaynul Abadin Tuhin
10.4k2730
answered 2 days ago
Zaynul Abadin Tuhin
10.4k2730
10.4k2730
add a comment |
add a comment |
draft saved
draft discarded
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53373694%2fselecting-multiple-rows-of-a-column-in-a-sql-server-query%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
what is your dbms name
– fa06
2 days ago
Apologies, SQL Server
– Jake12342134
2 days ago