locked
Need Help in Writing a Complex and Generic TSQl Query RRS feed

  • Question

  • Hi Experts I need a help regarding a query I am developing, Let me explain the process first.
    I have an Excel File with Data and it is being loaded into a Table(File_Master) in SQl server.

    The header of the Excel File is Like:
    FileNo, Name, Address, Company, Phone, Email, Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10

    Structure Of My Table (File_Master):
    ID, FileNo, Name, Address, Company, Phone, Email, Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10

    Values That go into table are Like

    1, 101, aa, 12 St, Comp1, 657646984, htf@ku.com, bcd, gfh, hgf,jhg
    2, 101, zdvf, 13 St, Comp2, 657646984, htf@ku.com, bcd, gfh, hgf,jhg
    3, 101, er, 112 St, Comp3, 657646984, htf@ku.com, bcd, gfh, hgf,jhg
    4, 102, fef, 156 St, Comp11, 657646984, htf@ku.com, bcd, gfh, hgf,jhg,gfh, hgf,jhg
    5, 102, sdf, 165 St, Comp13, 657646984, htf@ku.com, bcd, gfh, hgf,jhg,gfh, hgf,jhg
    6, 103, hgj, 1656 St, Comp34, 657646984, htf@ku.com
    7, 103, er, 15645 St, Compa, 657646984, htf@ku.com
    8, 104, zd, 145 St, Comp23, 657646984, htf@ku.com, bcd
    9, 105, rt, 154 St, Compe2, 657646984, htf@ku.com, bcd, gfh
    10, 105, rer, 156 St, Comp456, 657646984, htf@ku.com, bcd, gfh
    11, 101, fd, 154 St, Comp65, 657646984, htf@ku.com, bcd, gfh, hgf
    12, 101, tw, 154 St, Comp34, 657646984, htf@ku.com, bcd, gfh, hgf

    I do have one more table (File_Column_Master) Like this:

    ID, FileNo,FileDesc, ColumnName, OriginalColumnName

    Values That go into table are Like

    1, 101, File1, Var1, OGCol1
    2, 101, File1, Var2, OGCol2
    3, 101, File1, Var3, OGCol3
    4, 101, File1, Var4, OGCol4
    5, 102, File2, Var1, File2Col1
    6, 102, File2, Var2, File2Col2
    7, 102, File2, Var3, File2Col3
    8, 102, File2, Var4, File2Col4
    9, 102, File2, Var5, File2Col5
    10, 102, File2, Var6, File2Col6
    11, 102, File2, Var7, File2Col7
    12, 104, File4, Var1, MyOrigCol1
    13, 105, File5, Var1, CCOL1
    14, 105, File5, Var2, CCol2

    The Column Names in the File_master Table Var1, Var2, Var3, Var4, Var5, Var6, Var7, Var8, Var9, Var10 have their original column names in the File_Column_Master table.

    My requirement is like I need to develop a generic query by joining these 2 tables to select data from a specific file with the original column names in the header. 

    Suppose If I have to select Only Data For FileNo:101 My Header should look like this

    ID, FileNo, Name, Address, Company, Phone, Email,OGCOL1, OGCOL2, OGCOL3, OGCOL4

    And If I have to select Data for FileNo 102 My Header should look like this

    ID, FileNo, Name, Address, Company, Phone, Email,File2Col1,File2Col2, File2Col3,File2Col4,File2Col5,File2Col6,File2Col7


    So My Requirement is like I need to Develop a generic query and the user changes the File No in the Where Clause when ever they want.

    Is there a way that this query can be written, Or is it possible. For me I really don't have a clue about how to develop a query like this.

    Your suggestions will help me greatly, Any Help would be highly Appreciated...


    Thanks In Advance.....



    Friday, July 16, 2010 4:12 PM

Answers

  • Try:

    declare @Columns varchar(max), @SQL varchar(max)
    
    set @Columns = (select ', ' + ColumnName + ' as ' + quotename(OriginalColumnName) from File_Column_Master where FileNo = @FileNo for XML PATH(''))
    
    set @SQL = 'select ID, FileNo, Name, Address, Company, Phone, Email ' + @Columns + ' from File_Master'
    
    print @SQL
    
    execute(@SQL)
    
    From the top of my head - may need minor adjustments.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by AaronAndy Friday, July 16, 2010 4:49 PM
    Friday, July 16, 2010 4:40 PM

All replies

  • Try:

    declare @Columns varchar(max), @SQL varchar(max)
    
    set @Columns = (select ', ' + ColumnName + ' as ' + quotename(OriginalColumnName) from File_Column_Master where FileNo = @FileNo for XML PATH(''))
    
    set @SQL = 'select ID, FileNo, Name, Address, Company, Phone, Email ' + @Columns + ' from File_Master'
    
    print @SQL
    
    execute(@SQL)
    
    From the top of my head - may need minor adjustments.


    Premature optimization is the root of all evil in programming. (c) by Donald Knuth

    Naomi Nosonovsky, Sr. Programmer-Analyst

    My blog
    • Marked as answer by AaronAndy Friday, July 16, 2010 4:49 PM
    Friday, July 16, 2010 4:40 PM
  • Wowwwwwwww, it was amazing....I never thought it is such simple,...It really worked like an ace....Thank you very much....
    Friday, July 16, 2010 4:49 PM