Table format output

[ChelvySun]

Hi,

I have the following fields in my table (out of many other).:

FiscalYear
Fiscalmonth
Department (values eg: account, IT, Hiring..etc)
EmployeeName

Now my output should look like the following......

Fiscalyear fiscalmonth account it hiring ...
2004 2 Prasath mohan james
2004 3 john peter michelle

Can you help me to write a select statement for the above table give above output?

Than you.

[skhanal]

How many distinct departments do you have?. If have a lot it becomes a very big query as you are transposing rows to columns.

To see how you can do this, search for Cross-Tab Reports reports in Books online.

[ChelvySun]

Output format



fiscal year fiscal month account dept it dept hiring department
2004 3 john peter micheele
2004 4 bill jeff nartin
2004 5 ken david marcus

[ChelvySun]

actually about 114

[ChelvySun]

How many distinct departments do you have?. If have a lot it becomes a very big query as you are transposing rows to columns.

To see how you can do this, search for Cross-Tab Reports reports in Books online.

Create table #MyTable
(yearofJoining int,
EmpId int,
Deptid int)
go
insert into #MyTable select 1990,1,1
insert into #MyTable select 1991,2,2
insert into #MyTable select 1990,3,4
insert into #MyTable select 1991,4,2
insert into #MyTable select 1990,5,1
insert into #MyTable select 1990,6,3
insert into #MyTable select 1992,7,3
insert into #MyTable select 1990,8,4
insert into #MyTable select 1993,9,1
insert into #MyTable select 1994,10,2
insert into #MyTable select 1990,11,3
insert into #MyTable select 1995,12,3
insert into #MyTable select 1995,14,3
insert into #MyTable select 1995,15,3
insert into #MyTable select 1995,16,6
go



select YearofJoining,
count(case [DeptId] when 1 then 1 else null end) as [Department-1],
count(case [DeptId] when 2 then 1 else null end) as [Department-2],
count(case [DeptId] when 3 then 1 else null end) as [Department-3]
from #MyTable where deptid in(1,2,3)
group by Yearofjoining

SELECT YearofJoining, [1] as [Department-1],[2] as [Department-2],
[3] as [Department-3] FROM
(SELECT YearOfJoining,Deptid from #MyTable) p
PIVOT
( Count(DeptId) for DEPTID in ([1],[2],[3]))
AS pvt
ORDER BY Yearofjoining

I tried this whole set of commands ...but an error is returned
Incorrect syntax near "PIVOT"........NB. I am using SQL Server 2005.

[skhanal]

PIVOT is SQL 2005 command. I assumed you are using SQL 2000.

[ChelvySun]

you are correct my backend is 2000