select query - 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 2005 Forums

Transact-SQL (2005)

select query

Author

Topic

alxtech
Yak Posting Veteran

66 Posts

Posted - 2009-07-23 : 10:21:09

hello forum,
Having this table:

userID firstname department status
==============================================
1 alex production master
2 mary sales admin
3 john production admin
4 lili clerk master
5 keith maintenance master
6 willi clerk admin
7 david sales master

I need a sql statement to extract and display all users group by department, the output in html will look like this:

DEPARTMENT:PRODUCTION
USERID | NAME | STATUS
-------------------------
1 alex master
3 john admin

****************************

DEPARTMENT:SALES
USERID | NAME | STATUS
-------------------------
2 mary admin
7 david master

****************************

DEPARTMENT:CLERK
USERID | NAME | STATUS
-------------------------
4 lili master
6 willi admin

****************************

DEPARTMENT:MAINTENANCE
USERID | NAME | STATUS
-------------------------
5 keith master

any suggestions will be appreciated.

thank you.

alxtech
Yak Posting Veteran

66 Posts

Posted - 2009-07-23 : 11:08:09

I figured it out:

Solution: create to statements.

first:
strSQLStatement = "SELECT distinct department FROM table"

then: inside first statement do this.
strSQLStatement = "SELECT * FROM table where department='" & recordset("department") & "'"

finally, populate table, looping through department value.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources