In the same row from the same table

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)

In the same row from the same table

Author

Topic

rbcuny
Starting Member

2 Posts

Posted - 2003-07-23 : 09:00:54

I hope you can help me.
I need to have data in one row, where it's in the database from a different row.
For Example in a table "departments" there's a column "Department" and a column "Employee"
Department Employee
Administration Johnson
Administration Smith
Administration Houston

I need a query where there's ONE row where the employees is in different columns and in the same row.

I hope this is clear..

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-23 : 16:15:38

I imagine you want it by Department

This is close but no cigar (hey where's slick willie been?)

Guys help...

Must ....resist....urge....to....use...a....

CURSOR


USE Northwind
GO

CREATE TABLE myTable99 (
	  Department varchar(50)
	, Employee varchar(50)
)
GO

INSERT INTO myTable99 (Department, Employee)
SELECT 'Administration', 'Johnson' UNION ALL
SELECT 'Administration', 'Smith' UNION ALL
SELECT 'Administration', 'Houston'  UNION ALL
SELECT 'Technolgy Services', 'Kaiser'  UNION ALL
SELECT 'Technolgy Services', 'Lopez'  UNION ALL
SELECT 'Technolgy Services', 'Defeis'
GO

DECLARE @x varchar(8000), @y varchar(50)

SELECT @y = l.Department, @x = ISNULL(@x,'') + Employee + ','
FROM myTable99 l 
INNER JOIN (SELECT DISTINCT Department FROM myTable99) r
ON l.Department = r.Department
SELECT @y, @x  
GO

DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources