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!
Rob SamuelIT ManagerAsked:
Who is Participating?
 
Nico BontenbalConnect With a Mentor Commented:
0
 
Nico BontenbalCommented:
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.
0
 
kamindaCommented:
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

0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Nico BontenbalCommented:
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

0
 
Anthony PerkinsConnect With a Mentor Commented:
kaminda,

>>You can use TOP (100) PERCENT to get all the result set in your view.<<
Please don't encourage this.  While it makes it syntactically correct, the author may actually believe that the resultset will be sorted based on the ORDER BY clause.  Nothing can be further from the truth.  The QO is smart enough to know that you have requested all the data and will promptly ignore the ORDER BY clause.
0
 
Anthony PerkinsCommented:
robsamuel2k8,

You may be able to use a VIEW providing that you are using at least SQL Server 2005.
0
 
Anthony PerkinsCommented:
Right, that is what I meant by "providing that you are using at least SQL Server 2005"
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.