Creating a complicated view in SQL server 2005

Question

I have a table that looks like this:

Name     Job        Year    ImpFile   ImpDate
------------------------------------------------
John     Clerk      1986    Imp01     20.01.2012
James    Assistant  1990    Imp01     20.01.2012
Anna     Manager    1982    Imp02     26.02.2012
Sam      Salesman   1985    Imp02     26.02.2012
Dean     Cleaner    1985    Imp02     26.02.2012

And I need to create a view that looks something like this:

Name   ImpFile/Job         Year
--------------------------------
       Imp01 20.01.2012
John   Clerk               1986
James  Assistant           1990
       Imp02 26.02.2012
Anna   Manager             1982
Sam    Salesman            1985
Dean   Cleaner             1985

Is it possible to do something like this in Microsoft SQL Server 2005? If yes, then how do I do it?

This link may help http://stackoverflow.com/questions/235515/hierarchical-queries-in-sql-server-2005 — Chetter Hummin, Mar 30 '12 at 08:04
I think you just need the result set with columns 'Name', 'Job', 'Year' and 'ImpFile/Job' ordered by ImpDate and ImpFile. In this case the select will be very simple. — Alex_L, Mar 30 '12 at 08:19
It would be better to do this type of reformatting in a reporting tool. You're trying to force two different types of data to occupy the same column in the result set - that's not the SQL way. And, as @jbl points out, you can't guarantee the order of rows returned from a view either. — Damien_The_Unbeliever, Mar 30 '12 at 08:51

score 1 · Accepted Answer · answered Mar 30 '12 at 10:01

Without using a view:

create table test(
name varchar(10),
Job varchar(20),
year smallint,
ImpFile varchar(10),
ImpDate datetime)

insert into Test
values('John', 'Clerk', 1986, 'Imp01', '20.01.2012')

insert into Test
values('James', 'Assistant', 1990, 'Imp01', '20.01.2012')

insert into Test
values('Anna', 'Manager', 1982, 'Imp02', '26.02.2012')

insert into Test
values('Sam', 'Salesmas', 1985, 'Imp02', '26.02.2012')


--drop table test

select * from
(
select name,Job,ImpFile,year from test
group by name, Job, ImpFile, year
union all
select distinct null, null, ImpFile + ' ' + convert(nvarchar, ImpDate, 104), null from test
) tablePlusHeaders
order by left(ImpFile,5), name, year

score 0 · Answer 2 · answered Mar 30 '12 at 08:14

Well, it's definitely possible but you'll have to either use a stored procedure or else some trickery. It would be more straightforward with a stored procedure because you could simply loop over the records with a cursor (though depending on the number of records this could have a negative performance impact).

You could probably join the table to itself and somehow use case statements... if you like solving puzzles and aren't in a hurry :)

Or I've also heard you can do recursive queries with CTE's. You might be able to union two queries together (one that selects the ImpFile, and the other that selects the job records) and use a recursive CTE to somehow accomplish this.

Does it absolutely have to be a VIEW? Could you at least use a stored procedure to create a temporary table instead? Personally I would handle this formatting in C# (or your language of choice).

You don't need to use a view. Simply select from the subquery: — JotaBe, Mar 30 '12 at 10:00

Creating a complicated view in SQL server 2005

2 Answers2