How to display detailed data below each header data?
One of my friends asked me if it possible to generate Detailed data for each Header data without using a Cursor or while loop. (The output is something like the one usually seen via reporting tool). He wanted the result to be output to text file
Consider the jobs and employee tables from pubs database
select
job_id, job_desc from jobs
select
job_id,emp_id,fname,lname from employee
The task is to show employee details under each job description
The required output is
New Hire - Job not specified
Chief Executive Officer
PTC11962M Philip Cramer
Business Operations Manager
AMD15433F Ann Devon
Chief Financial Officier
F-C16315M Francisco Chang
Publisher
PXH22250M Paul Henriot
CFH28514M Carlos Hernadez
JYL26161F Janine Labrune
LAL21447M Laurence Lebihan
RBM23061F Rita Muller
SKO22412M Sven Ottlieb
MJP25939M Maria Pontes
Managing Editor
VPA30890F Victoria Ashworth
MGK44605M Matti Karttunen
DWR65030M Diego Roel
A-R89858F Annette Roulet
Marketing Manager
L-B31947F Lesley Brown
PDI47470M Palle Ibsen
HAN90777M Helvetius Nagy
M-L67958F Maria Larsson
Public Relations Manager
ARD36773F Anabela Domingues
MMS49649F Mary Saveley
PSP68661F Paula Parente
M-P91209M Manuel Pereira
Acquisitions Manager
KJJ92907F Karla Jablonski
M-R38834F Martine Rance
MAS70474F Margaret Smith
GHT50241M Gary Thomas
Productions Manager
A-C71970F Aria Cruz
PHF38899M Peter Franken
POK93028M Pirkko Koskitalo
MFS52347M Martin Sommer
Operations Manager
PCM98509F Patricia McKenna
R-M53550M Roland Mendel
DBT39435M Daniel Tonini
MAP77183M Miguel Paolino
Editor
H-B39728F Helen Bennett
Y-L77953M Yoshi Latimer
HAS54740M Howard Snyder
Sales Representative
PMA42628M Paolo Accorti
TPO55093M Timothy O'Rourke
CGS88322F Carine Schmitt
Designer
PSA89086M Pedro Afonso
ENL44273F Elizabeth Lincoln
KFJ64308F Karin Josephs
Here is the query that does it
select job_desc,first_name,last_name from
(
select job_id, job_desc,'' as first_name,''as last_name,1 as sorder from jobs
union all
select job_id,' '+emp_id,fname,lname ,2 as sorder from employee
) as t
order by job_id,sorder
Note that the above query should be used only if you have no option to use any front end application. Otherwise it should be done in front end application