SQLTeam.com | Weblogs | Forums

Need SQL help


#1

Hi All,

I have table which has a data in below form.

Project_Id Status ProcessName
3 START process1
2 START process1
1 END process1
1 START process1

And I want to display like this

OUTPUT -

Project_Id Col1 Col2 ProcessName
1 START END process1
2 START NULL process1
3 START NULL process1

Please assist me on this


#2
SELECT
	a.Project_Id,
	a.[Status],
	b.[Status],
	a.[ProcessName]
FROM
	YourTable a
	LEFT JOIN YourTable b ON
		a.Project_id = b.Project_id
		AND a.ProcessName = b.ProcessName
		AND b.[Status] = 'END'
WHERE
	a.[Status] = 'START'
ORDER BY
	a.Project_id;

#3
SELECT
	Project_Id,
	MAX(CASE WHEN Status = 'START' THEN 'START' END) AS Status1,
	MAX(CASE WHEN Status = 'END'   THEN 'END'   END) AS Status2,
	MAX(CASE WHEN Status = 'START' THEN ProcessName END) AS ProcessName
FROM table_name
WHERE
    Status IN ('START', 'END')
GROUP BY Project_Id
ORDER BY Project_Id;