Getting Started with Adobe After Effects - Part 6: Motion Blur

Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

How to display detailed data below each header data?

Aug 6 2009 2:18PM by Madhivanan   

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
        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
        H-B39728F                                  Helen                Bennett
        Y-L77953M                                  Yoshi                Latimer
        HAS54740M                                  Howard               Snyder
Sales Representative                                                    
        PMA42628M                                  Paolo                Accorti
        TPO55093M                                  Timothy              O'Rourke
        CGS88322F                                  Carine               Schmitt
        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

Tags: t-sql, sql_server, report, detail, header,

2 · 40% · 13039


Your Comment

Sign Up or Login to post a comment.

"How to display detailed data below each header data?" rated 5 out of 5 by 2 readers
How to display detailed data below each header data? , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]