SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select query help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ranvir_2k
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/02/2013 :  09:41:52  Show Profile  Reply with Quote
I have a table with the following structure, it is called Employees

Location Employee
London James
London Eddie
London Michelle
Madrid Diego
Madrid Julio
Madrid Marta
Berlin Heinze
Berlin Rudi
Berlin Lothar

I would like to write a query so that I can have Location written once and then each employee who belongs to that location listed underneath.

For example:
London
James
Eddie
Michelle



Madrid
Diego
Julio
Marta

Berlin
Heinze
Rudi
Lothar

Is this possible?

Edited by - ranvir_2k on 10/02/2013 09:53:09

ranvir_2k
Posting Yak Master

United Kingdom
171 Posts

Posted - 10/02/2013 :  10:21:06  Show Profile  Reply with Quote
The output can be a string or a table if that helps.
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

405 Posts

Posted - 10/02/2013 :  16:26:37  Show Profile  Reply with Quote
Formating the output like this, should be done in your frontend program. It can be done in sql, but it's not gonna be pretty:

with employees (location,employee)
  as (          select 'London','James'
      union all select 'London','Eddie'
      union all select 'London','Michelle'
      union all select 'Madrid','Diego'
      union all select 'Madrid','Julio'
      union all select 'Madrid','Marta'
      union all select 'Berlin','Heinze'
      union all select 'Berlin','Rudi'
      union all select 'Berlin','Lothar'
     )
    ,tmp_location (location,rn)
  as (select location
            ,(row_number() over (order by location)-1)*3 as rn
        from employees
       group by location
     )
select todisplay
  from (select location as todisplay
              ,rn
          from tmp_location
        union all
        select b.employee as todisplay
              ,a.rn+1 as rn
          from tmp_location as a
               inner join employees as b
                       on b.location=a.location
        union all
        select '' as todisplay
              ,rn+2 as rn
          from tmp_location
       ) as a
 order by rn
         ,todisplay

ps.: remove the red section to query your own table.
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
962 Posts

Posted - 10/02/2013 :  23:42:33  Show Profile  Reply with Quote
using bitsmed sample data

WITH Employees (Location,Employee) AS (
SELECT CAST('London' AS VARCHAR(10)),CAST('James' AS VARCHAR(10))
UNION ALL SELECT 'London','Eddie'
UNION ALL SELECT 'London','Michelle'
UNION ALL SELECT 'Madrid','Diego'
UNION ALL SELECT 'Madrid','Julio'
UNION ALL SELECT 'Madrid','Marta'
UNION ALL SELECT 'Berlin','Heinze'
UNION ALL SELECT 'Berlin','Rudi'
UNION ALL SELECT 'Berlin','Lothar'
), CTE AS(
	SELECT	DISTINCT
		Location
	,	Employee = CAST(Location AS VARCHAR(MAX))
	FROM	Employees
	UNION ALL
	SELECT	
		Employees.Employee
	,	CAST(CTE.Location + Employees.Employee AS VARCHAR(MAX))
	FROM	Employees
		JOIN CTE
		ON Employees.Location = CTE.Location
)
SELECT	Location
FROM	CTE
ORDER BY Employee
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 10/03/2013 :  00:07:54  Show Profile  Reply with Quote

;with cte as
(
	select	distinct output = Location, Location, rn = 0
	from	Employees
	union all
	select	output = Employee, Location, rn = row_number() over (partition by Location order by Employee)
	from	Employees
)
select	output
from	cte
order by Location, rn



KH
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000