We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Unable to save SQL query as a view to interrogate

Rob Samuel
Rob Samuel asked
on
Medium Priority
488 Views
Last Modified: 2012-05-11
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!
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.

Commented:
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

CERTIFIED EXPERT

Commented:
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

CERTIFIED EXPERT
Top Expert 2012
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2012

Commented:
robsamuel2k8,

You may be able to use a VIEW providing that you are using at least SQL Server 2005.
CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT
Top Expert 2012

Commented:
Right, that is what I meant by "providing that you are using at least SQL Server 2005"
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.