Select Columns based on input

Question

Select Columns based on input

erniemadden

Old Hand

Points: 327
More actions
August 26, 2010 at 5:39 am

#387665

I am creating a stored procedure that produces a fee forecast.
The figures in the table are based on periods 1-9 headed EV_FFM1, EV_FFM2, EV_FFM3 etc.
when I query the database I can only figure out how to select the data by using the forecast month number and then use that to determine the column list I use and then use it in a dynamic sql statement as follows.
set @fMonth = 6
Forecast FeeColumns
set @EV_colStr1 =
case
when @fMonth = 1 then 'EV_FFM1 '
when @fMonth = 2 then 'EV_FFM1,EV_FFM2 '
when @fMonth = 3 then 'EV_FFM1,EV_FFM2,EV_FFM3 '
when @fMonth = 4 then 'EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4 '
when @fMonth = 5 then 'EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4,EV_FFM5 '
when @fMonth = 6 then 'EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4,EV_FFM5,EV_FFM6 '
when @fMonth = 7 then 'EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4,EV_FFM5,EV_FFM6,EV_FFM7 '
when @fMonth = 8 then 'EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4,EV_FFM5,EV_FFM6,EV_FFM7,EV_FFM8 '
when @fMonth = 9 then 'EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4,EV_FFM5,EV_FFM6,EV_FFM7,EV_FFM8,
EV_FFM9 '
End
I then build up the sql string
set @strSql =''
set @strSql = @strSql + 'select distinct EV_PROJ_ID, EV_CC, EV_CONTRACT_TYPE, EV_DATE, EV_COST_TD, '
set @strSql = @strSql + 'EV_COST_FORECAST, EV_FEES_AT_COMPLETION, EV_FEES_TD, EV_TIME_VALUE, EV_EXPN_VALUE, EV_DISB_VALUE, EV_TM, '
set @strSql = @strSql + 'EV_PC_COMPLETE, EV_PC_TO_TAKE, EV_PROBABILITY, EV_WIP_CALCULATED, EV_WIP, '
set @strSql = @strSql + ''+@EV_colStr1 +''
set @strSql = @strSql + ' FROM [CCREPORT].[dbo].[vw_BH_Earned_Value] '
set @strSql = @strSql + 'where ev_contract_type !=''proposal'''
this works fine, but my question is is there a better way of building the column list without using dynamic sql?
I thought there maybe something in XML or Tally tables, but I have not been able to suss them out yet.
Cheers...Steve

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

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

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

If the columns you want to return are variable depending on input then your options on the SQL side are either to use dynamic sql or use if blocks to return different selects.

In the example you've given, I would probably return all the columns and then limit which are visible at the presentation layer if that is an option for you. (This may not be possible depending on what sort of application you are using.)

└> bt

Forum Etiquette: How to post data/code on a forum to get the best help[/url]

WayneS SSC Guru Points: 95460 More actions · Answer 2

This is actually a good example of a bad design.

Make a separate table:

CREATE TABLE Periods (

PKCOLUMNs from original table,

Period tinyint,

EV_FFM (unknown datatype),

CONSTRAINT PK_Periods PRIMARY KEY CLUSTERED (PKColumns + Period));

Now, your query becomes simple:

select EV_FFM

from Periods

where PKCOLUMNS = PKCOLUMNS

AND Period <= @fMonth

ORDER BY Period

Join this to the rest of your existing query.

A lot easier, isn't it?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

LutzM SSC Guru Points: 107049 More actions · Answer 3

Another option would be to normalize your data either by changing the design or using UNPIVOT. The required data could easily be selected from the resulting table (or cte or subquery) assuming the re-pivoting could be done within the frontend. Return the data in XML format would be an option, too.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

erniemadden Old Hand Points: 327 More actions · Answer 4

Thanks for the replies guys, most helpful.

I have used a little bit of each suggestion and come up with a solution.

I have no access to the presentation layer so that is out.

I am not sure of the origins of this table, how it is used and how it gets populated, so changing the table design is not an option.

The data in the table changes regularly so I did not fancy populating it from the original table periodically.

So my temporary fix is to create a view and use UNPIVOT to normalise the data and then just joined it to the existing query as suggested.

seems simple now its been highlighted to me.

Cheers...Steve

WayneS SSC Guru Points: 95460 More actions · Answer 5

Steve,

Thanks for the feedback. Sounds like you came up with a do-able solution.

(Isn't it amazing how many people run into the "can't see the forest for the trees" type of problem?)

Edit: I'm a bit curious about the view that you created - would you mind posting it here?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

erniemadden Old Hand Points: 327 More actions · Answer 6

Wayne,

thanks for the follow up. You are so right.

I have just had to take on some of the Accounts stuff and I have been so concerned with understanding the terminology they use and relating that to their data that I have completely missed some table design fundamentals....

Here is a copy of the view I came up with

CREATE view [dbo].[vw_BH_EV_FFM_UNPIVOT] as

select [EV_ID],[EV_PROJ_ID],[EV_CC],[EV_DATE],[EV_CONTRACT_TYPE], tblPivot.Property, tblPivot.Value

from (SELECT [EV_ID]

,[EV_PROJ_ID]

,[EV_CC]

,[EV_DATE]

,[EV_CONTRACT_TYPE]

,CONVERT(sql_variant,[EV_FFM1]) AS [EV_FFM1]

,CONVERT(sql_variant,[EV_FFM2]) AS [EV_FFM2]

,CONVERT(sql_variant,[EV_FFM3]) AS [EV_FFM3]

,CONVERT(sql_variant,[EV_FFM4]) AS [EV_FFM4]

,CONVERT(sql_variant,[EV_FFM5]) AS [EV_FFM5]

,CONVERT(sql_variant,[EV_FFM6]) AS [EV_FFM6]

,CONVERT(sql_variant,[EV_FFM7]) AS [EV_FFM7]

,CONVERT(sql_variant,[EV_FFM8]) AS [EV_FFM8]

,CONVERT(sql_variant,[EV_FFM9]) AS [EV_FFM9]

FROM [CCREPORT].[dbo].[vw_BH_Earned_Value])

EV

UNPIVOT (Value For Property In (EV_FFM1,EV_FFM2,EV_FFM3,EV_FFM4,EV_FFM5,EV_FFM6,EV_FFM7,EV_FFM8,EV_FFM9)) as tblPivot

GO

This is the query I use to query the data in the view

SELECT EV_ID,EV_proj_id,EV_CC,EV_DATE,EV_CONTRACT_TYPE,property,value

FROM [CCREPORT].[dbo].[vw_BH_EV_FFM_UNPIVOT]

where ev_date = @cDate

and EV_CC in (@strCCs)

and EV_CONTRACT_TYPE !='proposal'

and cast(right(property,1) as int) <=@fMonth

order by EV_Proj_id

I can now join this to the existing query using the EV_ID and EV_proj_id.

Another plus is that I have been able to strip out all the dynamic sql, which I can't stand.

Thanks again.

Steve