Post reply

Need help with Dynamic Query

  • October 22, 2008 at 9:03 am

    #199829

    Hello, I have the following table that I need to create a query out of where the rows become the field names

    Form Location FieldName

    RFX BidData REQUESTOR_NAME

    RFX BidData REQUESTOR_EMAIL

    RFX BidData REQUESTOR_PHONE

    RFX BidData REQUESTOR_DEPT

    RFX BidData REQUESTOR_TITLE

    RFX BidData REQUESTOR_COSTCENTER

    RFX BidData WIRELESSPERCENT

    RFX BidData WIRELESSUSERS

    RFX BidData ILECCOMPONENT

    RFX BidData VWCOMPONENT

    RFX BidData OTHERWIRELESS

    RFX BidData OTHERWIRELESSPROVIDER

    I need a dynamic query that will pull all the fields

    Select REQUESTOR_NAME, REQUESTOR_EMAIL, REQUESTOR_PHONE, REQUESTOR_DEPT, etc.

    From BidData

  • October 22, 2008 at 11:18 am

    #888240

    If you're using SQL 2005, look into PIVOT functions

    http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

    Although if there's unknown # of rows, PIVOT may not work so well

    Google for 'crosstab'

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005

  • October 22, 2008 at 11:23 am

    #888243

    http://qa.sqlservercentral.com/articles/T-SQL/63681/

  • October 22, 2008 at 11:39 am

    #888251

    or if that's all you need

    declare @SQL varchar(max)

    select @SQL = COALESCE(@SQL,'') + FieldName + ','

    from (

    select 'REQUESTOR_NAME' AS FieldName

    union all

    select 'REQUESTOR_EMAIL'

    ) T

    print 'SELECT ' + @SQL + ' FROM BidData (NOLOCK)'

    -- results, just need to remove the final ,

    SELECT REQUESTOR_NAME,REQUESTOR_EMAIL, FROM Table

    SQLServerNewbie MCITP: Database Administrator SQL Server 2005

  • October 22, 2008 at 11:41 am

    #888252

    Thanks but there are many more fields that I would rather not pull into a union all.

  • October 22, 2008 at 11:48 am

    #888253

    Do Ipossibly need some form of cursor here to produce a SQL statement

  • October 22, 2008 at 11:52 am

    #888259

    Heh, I actually wrote an entire data-drive application framework built like this about 6 years ago.

    Anyway, try this:

    Declare @sql Varchar(MAX)

    Set @sql = ''

    SELECT @sql = @sql

    --select form,location, (Row_Number() Over (Partition By Form,Location Order By Location)) as locno, Count(*) Over (Partition By Form, Location) loccnt,''

    + (Case When (Row_Number() Over (Partition By Form,Location Order By Location) = 1)

    Then 'Select' Else ',' End)

    + ' [' + FieldName + ']'

    + (Case When (Row_Number() Over (Partition By Form Order By Location)

    = Count(*) Over (Partition By Form,Location) )

    Then ' From [' + Location + ']

    ' Else '' End)

    FROM TableDefs

    WHERE Form = 'RFX'

    ORDER By Form, Location

    PRINT @sql -- just so we can see what the SQL command is

    EXEC (@sql)

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • October 22, 2008 at 11:54 am

    #888260

    Note that this will handle multiple locations within the same form.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply