Adding a new column in this SQL Query?

Question

I am querying data from the WIP and Employee tables:

WIP Id,Name

Employee Id,Name,Orgnization

Joining both I can query:

select w.ID,e.Organization,w.ConsultantName,e.OrganizationID, w.ConsultantID
from vwWIPRecords w
inner join vwEmployees e on w.ConsultantID=e.ID;

Resutls:

1   VHAA    Web User    1   1
2   VHAA    NZ RP       1   3
3   VHAA    Ghom Mure   1   2
4   VHAA    Ghom Mure   1   2

Requirment:

In query add anther column which will concatenate and group by e.Organization and e.ConsultantName but it will be only for first unique record. For next (where name and organization is same) it will not show anything. This column will show unique Consultants of a company. Please see record number 3 and 4 in second example.

1   VHAAWeb User    1   1
2   VHAANZ RP       1   3
3   VHAAGhom Mure   1   2
4                   1   2

Thanks a lot for your help

@DanBracuk I m not making sense how to quey this ? Can you please help — user576510
– user576510, Commented May 7, 2013 at 23:45
@DanBracuk I not able to understand the logic how to get first unique and avoid next of same combinations. — user576510
– user576510, Commented May 7, 2013 at 23:47
I suggest baby steps. Can you add the column that concatenates the organization and consultant name? — Dan Bracuk
– Dan Bracuk, Commented May 8, 2013 at 2:03

Nick.Mc · Accepted Answer · 2013-05-08 00:32:18Z

1

Here is a start. The final column is a flag indicating the row should be blank. Let me know if this works for you so far and I can help further.

select w.ID,e.Organization, w.ConsultantName,
e.OrganizationID, w.ConsultantID, CASE WHEN D.Dup > 1 AND D.ID <> w.ID THEN 'Y' 
ELSE 'N' END As HideMe
from vwWIPRecords w
inner join vwEmployees e on w.ConsultantID=e.ID
inner join
(
   select MIN(w.ID) As ID,  e.Organization,w.ConsultantName,
   e.OrganizationID, w.ConsultantID, COUNT(*) AS Dup
   from vwWIPRecords w
   inner join vwEmployees e on w.ConsultantID=e.ID
) D
ON  D.Organization  = w.Organization 
AND D.ConsultantName = w.ConsultantName
AND D.OrganizationID = w.OrganizationID
AND D.ConsultantID = w.ConsultantID

edited May 8, 2013 at 0:32

answered May 8, 2013 at 0:10

Nick.Mc

19.4k6 gold badges67 silver badges102 bronze badges

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

2 Comments

user576510 Over a year ago

Thanks for your time but it is not what I asked for. I dont want to query distinct records. Rather I want to query all records but just in new column want to concate two columns and in case there can be multiple combinations of same, only first one will be shown a sort of group by newcolumn.

Nick.Mc Over a year ago

I've changed my answer - see if it helps.

Collectives™ on Stack Overflow

Adding a new column in this SQL Query?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related