asked on

Unable to save SQL query as a view to interrogate

Hi,

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:

Job Ref
Job Title
Location
Primary Consultant.

Our directors would also like 'skillsets' included on the report. Long story short, these are stored in a separate table like this:

JobId Description
----- -------------
17893 Manager
17897 Software Development
17897 Web Development
17897 Web Services
17897 C#
17897 Java
17895 Windows 2008 Server
17895 Exchange 2007
17895 Active Directory.

I needed to list these in columns per job:

JobId Skills
----- -------
17893 Manager
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
INTO zztest
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)
FROM zztest
GROUP BY ordinal
ORDER BY ordinal

SET @sql = @sql + ' FROM zztest t1 ORDER BY t1.[JobId]'

EXEC(@sql)
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!

Nico Bontenbal

You could create a custom function that retrieves the skills for a jobID as a string. You can then use this function in and SQL statement, and this SQL statement can be saved as a query.

kaminda

When a ORDER BY clause is used in a View query it is Mandatory in SQL Server to use the TOP keyword in the SELECT list. You can use TOP (100) PERCENT to get all the result set in your view.

Something like this

SELECT TOP (100) PERCENT t1.[JobId], t1.Description,      
      (SELECT COUNT(t2.[JobId]) FROM TVAttributes t2 WHERE t2.[JobId] = t1.[JobId] AND t2.Description <= t1.Description)AS Ordinal
INTO zztest
FROM  TVAttributes t1
ORDER BY t1.[JobId], t1.Description

Open in new window

Nico Bontenbal

Here's a complete example that creates the table, fills the table with some test data, creates the function, creates the view and then returns the view. So it should be run in an Empty database.
I used 2008 R2, but I'd be surprised if it didn't work in previous versions.

--create table
create table TVAttributes(JobID int, Description varchar(100))

--insert data into table
insert into TVAttributes values(17893,'Manager')
insert into TVAttributes values(17897,'Software Development')
insert into TVAttributes values(17897,'Web Development')
insert into TVAttributes values(17897,'Web Services')
insert into TVAttributes values(17897,'C#')
insert into TVAttributes values(17897,'Java')
insert into TVAttributes values(17895,'Windows 2008 Server')
insert into TVAttributes values(17895,'Exchange 2007')
insert into TVAttributes values(17895,'Active Directory.')

--create the function
go
create function getSkills(@JobID int) returns varchar(8000)
as
begin
    declare @return varchar(8000) = ''
    declare @skill varchar(100)
    DECLARE cur CURSOR FOR SELECT Description FROM TVAttributes WHERE JobID = @JobID
    OPEN cur;

    FETCH NEXT FROM cur into @skill
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @return = @return + ',' + @skill
        FETCH NEXT FROM cur into @skill
    END
    CLOSE cur;
    DEALLOCATE cur;
    set @return = SUBSTRING(@return,2,LEN(@return))
    return @return
end
go

--create the view
create view showSkills
as
select JobID, dbo.getSkills(JobID) as Skills from TVAttributes group by JobID
go

--return the view
select * from showSkills

Open in new window

SOLUTION