Row to Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Row to Column

Author

Topic

martalex
Starting Member

17 Posts

Posted - 2002-04-25 : 11:21:50

Hello,

I have a resume posting system. I need to generate a report on the different system activity such as how many new resumes were created, how many resumes were archived and how many resumes were approved by job category for a given time period.

I have the following SQL query that do the job but not exactly how I would like.

SELECT tlk_JobCategories.JC_ID, Info='RsmNewCount ', count(rsm_id) as Total
FROM tlk_JobCategories LEFT JOIN tbl_resumes ON (tlk_JobCategories.JC_Id = tbl_resumes.JC_Id) AND rsm_createdon BETWEEN { d '2002-04-01'} AND DATEADD(Day , 1, { d '2002-04-30'})
GROUP BY tlk_JobCategories.JC_Id

UNION

SELECT tlk_JobCategories.JC_ID, Info='RsmVisibleCount', count(DISTINCT rsm_id) as Total
FROM tlk_JobCategories LEFT JOIN tbl_historyresumes ON (tlk_JobCategories.JC_Id = tbl_historyresumes.JC_Id) AND (hrsm_statustype = 'both' AND (hrsm_enddate >= { d '2002-04-01'} OR hrsm_enddate is null) AND hrsm_startdate <= DATEADD(Day , 1, { d '2002-04-30'}))
GROUP BY tlk_JobCategories.JC_Id

UNION

SELECT tlk_JobCategories.JC_ID, Info='RsmArchivedCount', COUNT(DISTINCT rsm_id) as Total
FROM tlk_JobCategories LEFT JOIN tbl_historyresumes ON (tlk_JobCategories.JC_Id = tbl_historyresumes.JC_Id) AND (hrsm_statustype = 'actv' AND hrsm_enddate BETWEEN { d '2002-04-01'} AND DATEADD(Day , 1, { d '2002-04-30'}))
GROUP BY tlk_JobCategories.JC_Id
ORDER BY tlk_JobCategories.JC_ID

The result look like the following:

JC_ID Info Total
----------- ---------------- -----------
33 RsmArchivedCount 2
33 RsmNewCount 2
33 RsmVisibleCount 6
34 RsmArchivedCount 0
34 RsmNewCount 0
34 RsmVisibleCount 0
35 RsmArchivedCount 2
35 RsmNewCount 2
35 RsmVisibleCount 0
36 RsmArchivedCount 3
36 RsmNewCount 5
36 RsmVisibleCount 6
...

I would prefer that the result look like more something like that:

JC_ID RsmNewCount RsmNewCount RsmVisibleCount
----------- ----------- ----------- ---------------
33 2 2 6
34 0 0 0
35 2 2 0
36 5 3 6

Is this doable, if so, How?

Thanks.

--
Alex

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-04-25 : 11:24:08

search here for CSV....articles also in the FAQ on this....this is a variation on the basic problem except that your seperator is a space.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources