This will select the maximum value of prod, not the first!

I haven't got an elegant solution for this yet 🙁

Can anyone help?

-- this is OP's data/table

CREATE TABLE [dbo].[YTD_nni](

[Custn] [nvarchar](50) NULL,

[Prod]...

Sorry, didn't fully read your last post, you want the first value, right?

gis a second, need to test this

What value of prod do you want in the output? minimum, maximum etc?

Going by your earlier post max(convert(int,prod)) as prod would do the trick... so that means:

select

...

Hi,

you need an aggregate function on each of the columns in your result set, I can't infer what the aggregate functions you need to apply to prod and pers, are...

Am I missing something here, this query gives the median?

You'll need to execute the query, check out objCmd.ExecuteScalar()

Heh heh, solutions above are for removing whitespace in column names.

If you post some sample data, and table structure, you are much more likely to get a useful solution.

See http://www.sqlservercentral.com/articles/Best+Practices/61537/...

here's a possible solution, depends on what you mean by 'whitespace', I'm assuming space i.e. character 20.

create table #temptest ([some col] int, [some othercol] int)

select * from...

Nice one Michelle,

glad you got there, and no need for dynamic SQL!

Take care

Allister

The wildcard doesn't work at the beginning of search term, i.e. "*ci*" will not match "uncival".

Please correct this if I'm wrong!

change DECLARE @SQLSelectList VARCHAR(100) to DECLARE @SQLSelectList VARCHAR(max).

///edit

If you need more help with this you'll have to post a sample of data from temporary table #NormalNVP.

Check out Jeff's artice how...

Ok, then putting it all together:

select

CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]

, CRM_PartsLabor.ORDERED_PROD as [Part No]

, CRM_PartsLabor.DESCRIPTION as [Part Desc]

, CRM_Confirmations.POSTING_DATE as [Date Consumed]

, CRM_StatusCodes.USER_STATUS as [Part Status]

, CRM_PartsLabor.QUANTITY as [Quantity]

, CRM_StatusCodes.END_DATE...

yeah, it's putting the result into a temporary table, have a look at the following:

select

CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]

, CRM_PartsLabor.ORDERED_PROD as [Part No]

, CRM_PartsLabor.DESCRIPTION as [Part Desc]

, CRM_Confirmations.POSTING_DATE as...

Sorry, got columns outta order:

CRM_PartsLabor.TRANSACTION_ID as [Service Order ID]

, CRM_PartsLabor.ORDERED_PROD as [Part No]

, CRM_PartsLabor.DESCRIPTION as [Part Desc]

, CRM_Confirmations.POSTING_DATE as [Date Consumed]

, CRM_StatusCodes.USER_STATUS as [Part Status]

, CRM_PartsLabor.QUANTITY as [Quantity]

, CRM_StatusCodes.END_DATE as...

OK, you are concatenating the two fields that you need separate for the required output: CRM_Partners.DESCRIPTION + ', ' + CRM_Partners.ADDRESS as [Role]... these need to be in two...