how to get this report?
-
dept_id dep_name member_name
1 prod mem1
1 prod mem2
1 prod mem3
2 dev mem4
2 dev mem5
3 tech mem6
4 tech mem7
I wanted to show the above data in the following way
dep_name prod
dept_id dep_name member_name
1 prod mem1
1 prod mem2
1 prod mem3
dep_name dev
dept_id dep_name member_name
2 dev mem4
2 dev mem5
dep_name dev
dept_id dep_name member_name
3 tech mem6
4 tech mem7
How can I do this?
Thanks.
-
3 different SELECTs.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Source : Select dept_id, dep_name, member_name from table1
Grouping : dept_id
Grouping Header : Dep_name
Details Section : member_name
-
Like this...
SELECT *
FROM yourtable
WHERE Dept_ID = 1
ORDER BY Member_Name
SELECT *
FROM yourtable
WHERE Dept_ID = 2
ORDER BY Member_Name
SELECT *
FROM yourtable
WHERE Dept_ID IN (3,4)
ORDER BY Member_Name
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I think he ment this:
SELECT *
FROM yourtable
WHERE Dep_name = 'prod'
ORDER BY Member_Name
SELECT *
FROM yourtable
WHERE Dep_name = 'dev'
ORDER BY Member_Name
SELECT *
FROM yourtable
WHERE Dep_name = 'tech'
ORDER BY Member_Name
_____________
Code for TallyGenerator -
I believe he is talking about Crystal Reports as he mentioned in one of his other posts. This is the classic header detail report.
-
Nope... I meant it the way I posted it... but your's works, as well.
Ya just gotta love these non-discript posts, eh?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I meant not what you meant but what sql 2005 fan meant.
Hope you followed who meant what.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply