SQLTeam.com | Weblogs | Forums

Repeat new conditional column value for every row

tsql

#1

Hi All I have a table in below format

Candidate Number ActivityCode Date
1 Submission 11/01/2015
2 Submission 11/08/2015
3 Submission 10/08/2015
1 ResumeScreen 11/05/2015
3 ResumeScreen 11/14/2015
2 ResumeScreen 11/19/2015

I want my output to be something like this

Candidate Number ActivityCode Date SubmissionDate ResumeScreenDate
1 Submission 11/01/2015 11/01/2015 11/05/2015
2 Submission 11/08/2015 11/08/2015 11/19/2015
3 Submission 10/08/2015 10/08/2018 11/14/2015
1 ResumeScreen 11/05/2015 11/01/2015 11/05/2015
3 ResumeScreen 11/14/2015 10/08/2015 11/14/2015
2 ResumeScreen 11/19/2015 11/08/2015 11/19/2015

I need to check for every candidatenumber the date when activity has happened and make it as a new column.

Can someone please help me how i can achieve this logic using T-SQL?

Thanks in Advance


#2

Since you did not provide a lot of information...

Something like?

SELECT a.C,a.A,a.D, b.a, c.a
FROM mytable a
inner join mytable b on a.c = b.c and a.a = 'Submission'
inner join mytable c on a.c = c.c and a.a = 'ResumeScreen';

#3
DECLARE @Candidate TABLE 
   ( [Candidate Number] int
   , ActivityCode varchar(50)
   , ActivityDate date
   );
INSERT @Candidate
      (
        [Candidate Number]
      , ActivityCode
      , ActivityDate
      )
   VALUES
      ( 1, 'Submission', '11/01/2015' )
    , ( 2, 'Submission', '11/08/2015' )
    , ( 3, 'Submission', '10/08/2015' )
    , ( 1, 'ResumeScreen', '11/05/2015' )
    , ( 2, 'ResumeScreen', '11/14/2015' )
    , ( 3, 'ResumeScreen', '11/19/2015' );

SELECT 
     s.[Candidate Number]
   , s.ActivityDate [SubmissionDate]
   , r.ActivityDate [ResumeScreenDate]
FROM
   @Candidate s
JOIN
   @Candidate r
ON s.[Candidate Number] = r.[Candidate Number]
WHERE s.ActivityCode = 'Submission'
AND r.ActivityCode = 'ResumeScreen';