SQLTeam.com | Weblogs | Forums

Query to get multiple results from one column


#1

Hi,

I have column called Response in EmpQueries table which holds multile fields values based on QueryID

For Ex: If QueryID = 'EICONS' -- Response column will give "Employer to be assigned"
If QueryID = 'EIRCONS' - Response Column will give "Reason for Assigned" Value..

So i need to show both these columns ("Employer to be assigned" and "Reason for Assigned" ) in my results in below query.. but as of now i am able to show only "Employer to be assigned" value by giving QueryID = 'EICONS' ...

I am not sure how i can show both the columns .. I new to SQL programming..Below is the query which i have return.

How can i get "Reason for Assigned" column ?

select SD.RollNumber ,SD.Name As 'Student Name',EmpID As 'Requesting Employer',Eq.Response as 'Employer to be assigned',
** from EmpInfo Ei**
** INNER JOIN EmpQueries Eq**
** on Ei.OrderID = eq.OrderID**
** inner JOIN StudentsData SD**
** ON Ei.VisitID = SD.VisitID **
** where Category = 'CONS' and Ei.Status not in ('CANC') **
** and DateTime between '2017-03-23 00:00:00' and '2017-03-23 23:59:59'**
** and Eq.QueryID = 'EICONS'**
Order by EmpID

Thank you ...


#2

To make it easier for people to respond we need consumable data. Please post DDL for the table and insert statements.


#3

To do this you will need 2 outer joins to EmpQueries - one for each case:

Select sd.RollNumber
     , sd.Name As 'Student Name'
     , ei.EmpID As 'Requesting Employer'
     , eq1.Response as 'Employer to be Assigned'
     , eq2.Response As 'Reason for Assigned'
  From EmpInfo ei
 Inner Join StudentsData sd On sd.VisitID = ei.VisitID
  Left Join EmpQueries eq1 On eq1.OrderID = ei.OrderID
                          And eq1.QueryID = 'EICONS'
  Left Join EmpQueries eq2 On eq2.OrderID = ei.OrderID
                          And eq2.QueryID = 'EIRCONS'
 Where Category = 'CONS' 
   And ei.Status Not In ('CANC')
   And DateTime Between '2017-03-23 00:00:00' And '2017-03-23 23:59:59'
 Order By 
       ei.EmpID

If you can guarantee that you will always have a row where the QueryID is EICONS and another row where the QueryID is EIRCONS in the EmpQueries table then you can change the join to an inner join. If one or the other could be missing then you need to keep the outer join.


#4

Thank you jeff for the reply.. It helped me..

Thank you..