I am in the middle of producing a report for my directors which shows all jobs raised in the last 7 days from our database system.
I've produced a view for the basic information:
Our directors would also like 'skillsets' included on the report. Long story short, these are stored in a separate table like this:
17897 Software Development
17897 Web Development
17897 Web Services
17895 Windows 2008 Server
17895 Exchange 2007
17895 Active Directory.
I needed to list these in columns per job:
17897 Software Development Web Development Web Services C# Java
17895 Windows 2008 Server Exchange 2007 Active Directory.
I managed to get this done using the code below:
SELECT t1.[JobId], t1.Description,
(SELECT COUNT(t2.[JobId]) FROM TVAttributes t2 WHERE t2.[JobId] = t1.[JobId] AND t2.Description <= t1.Description)AS Ordinal
FROM TVAttributes t1
ORDER BY t1.[JobId], t1.Description
DECLARE @sql varchar(8000)
SET @sql = 'SELECT DISTINCT t1.[JobId]'
SELECT @sql = @sql + ', (SELECT t2.Description FROM zztest t2 WHERE t2.[JobId] = t1.[JobId] AND t2.ordinal = ' +
CONVERT(varchar, ordinal) + ') AS Column' + CONVERT(varchar, ordinal)
GROUP BY ordinal
ORDER BY ordinal
SET @sql = @sql + ' FROM zztest t1 ORDER BY t1.[JobId]'
DROP TABLE zztest
My problem is that I am unable to save this query as a view. Is there a way of saving this as a view (tweaking the code if necessary)??
Once it's in a view, I can call it from another query and produce the data that I need to.
I look forward to your comments,
Thanks in advance!