Need help with Dynamic Query

Question

Post reply

Need help with Dynamic Query

marty.seed

SSCrazy

Points: 2661
More actions
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
FormLocationFieldName
RFXBidDataREQUESTOR_NAME
RFXBidDataREQUESTOR_EMAIL
RFXBidDataREQUESTOR_PHONE
RFXBidDataREQUESTOR_DEPT
RFXBidDataREQUESTOR_TITLE
RFXBidDataREQUESTOR_COSTCENTER
RFXBidDataWIRELESSPERCENT
RFXBidDataWIRELESSUSERS
RFXBidDataILECCOMPONENT
RFXBidDataVWCOMPONENT
RFXBidDataOTHERWIRELESS
RFXBidDataOTHERWIRELESSPROVIDER
I need a dynamic query that will pull all the fields
Select REQUESTOR_NAME, REQUESTOR_EMAIL, REQUESTOR_PHONE, REQUESTOR_DEPT, etc.
From BidData

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

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 1

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'

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Glen SSCertifiable Points: 6737 More actions · Answer 2

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

Jerry Hung SSChampion Points: 12968 More actions · Answer 3

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

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

marty.seed SSCrazy Points: 2661 More actions · Answer 4

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

marty.seed SSCrazy Points: 2661 More actions · Answer 5

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

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]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

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]