T-SQL Query help! - 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 2008 Forums

Transact-SQL (2008)

T-SQL Query help!

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2012-02-14 : 06:13:49

I have two tables like:

table1: dep

depid depname
1 DEVELOP
2 HR
3 ADMIN

table2: emp
empid empname depid
1 raj 1
2 jan 1
3 sab 1
4 scot 2
5 rag 2
6 sar 3
7 lal 3

I want the output like :

output
---------
DEVELOP
raj
jan
sab
HR
scot
rag
ADMIN
sar
lal

Is it possible by single query to achieve this. please help me in this.

Regards
Js.Reddy

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-02-14 : 06:49:18

Formatting should be done in the front end (such as Crystal Reports).

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 10:03:38

you want all field values to come inside same filed? dept,emp etc?
what meaning will it have?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2012-02-20 : 04:59:18

Hi visakhm,

Unfortunately, my requirement is like this: DepartmentName and EmployeeName should come in same column.

Anyway I got this finally.

Here is the query. But I am not sure this will be good practice or not.

SELECT DISTINCT a.Dept_Name AS Employees,a.dept_id, NULL AS Emp_id
FROM dept_info a INNER JOIN emp_info b ON a.dept_id = b.Emp_Dept_id
UNION ALL
SELECT b.Emp_Name,a.dept_id,b.Emp_id
FROM dept_info a INNER JOIN emp_info b ON a.dept_id = b.Emp_Dept_id
ORDER BY dept_id,Emp_id;

Thanks
Js.Reddy

quote:
Originally posted by visakh16

you want all field values to come inside same filed? dept,emp etc?
what meaning will it have?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-20 : 11:42:34

quote:
Originally posted by js.reddy

Hi visakhm,

Unfortunately, my requirement is like this: DepartmentName and EmployeeName should come in same column.

Anyway I got this finally.

Here is the query. But I am not sure this will be good practice or not.

SELECT DISTINCT a.Dept_Name AS Employees,a.dept_id, NULL AS Emp_id
FROM dept_info a INNER JOIN emp_info b ON a.dept_id = b.Emp_Dept_id
UNION ALL
SELECT b.Emp_Name,a.dept_id,b.Emp_id
FROM dept_info a INNER JOIN emp_info b ON a.dept_id = b.Emp_Dept_id
ORDER BY dept_id,Emp_id;

Thanks
Js.Reddy

quote:
Originally posted by visakh16

you want all field values to come inside same filed? dept,emp etc?
what meaning will it have?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

i really cant understand why you need to consider both employees and departments as one
if this is for presentation, then surely above is not the best way of doing this. you should be trying to get it done through front end application not in SQL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources