August 2009 - Posts

How to Generate Script along with Data?

One of the new features available in SQL Server 2008 is scripting the tables along with data (in the form of INSERT statements). Here are the useful links that can help you generating script with data

SQL Server 2008

SQL Server 2000/2005


Posted by Madhivanan with 2 comment(s)
Filed under: , ,

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
        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

Posted by Madhivanan with 1 comment(s)

Regular expression

One of the posters asked a question in the forums about finding a number in an expression consists of alphabets and number if a particular digit is provided

Consider the following set of data

this is test place located at no 123456 test
foo 35234
bar 126831
72348707 foo
foo 987654 bar

If the digit provided is 7, then the numbers 72348707 and 987654 should be extracted from the data

One of the methods is using a regular expression

declare @sample table
        data varchar(200)

insert @sample

select 'this is test place located at no 123456 test' union all
select 'foo 35234' union all
select 'bar 126831' union all
select '72348707 foo ' union all
'foo 987654 bar'

declare @digits int

set @digits = 7


,@digits as expression,
    right(substring(' '+data+' ', 1,pos),charindex(' ',reverse(substring(' '+data+' ', 1,pos))))+
        substring(' '+data+' ', pos+1,len(' '+data+' ')),1,charindex(' ',substring(' '+data+' ', pos+1,len(' '+data+' ')))
    as number
        patindex('%' + cast(@digits as varchar(10)) + '%', data) as pos 
like '%'+CAST(@digits as varchar(10))+'%'
) as d