First and/or Last Item different in Select

Question

Post reply

First and/or Last Item different in Select

Tom257

SSChampion

Points: 11543
More actions
May 30, 2007 at 9:48 am

#196050

I want either the first item, or last item in a select statement to be different from the normal case. e.g when assembling a list of names ("Bill, " "Joe, " "Tom" - e.g. no trailing comma on the final item)
So far I have:
DECLARE @MAXID INT
SELECT @MAXID = MAX(pKID) FROM Customer
SELECT CASE pKID
WHEN @MAXID THEN Name
ELSE Name + ', '
END
FROM Customer
Does anyone have any better suggestions, or is this the only way of doing this kind of query?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Not really sure what you are asking, but if you are trying to make a comma seperated list of names try this:

declare @NameList varchar(8000)

select @NameList = isnull(@NameList,'') + Name + ', ' from Customer

set @NameList = substring(@NameList, 1, len(rtrim(@NameList)) - 1)

This works better in SQL 2005 where you can use varchar(max).

Sergiy SSC Guru Points: 110208 More actions · Answer 2

Little correction:

select @NameList = isnull(@NameList + ', ','') + Name from Customer

and you don't need the next statement cutting off last comma.

_____________
Code for TallyGenerator

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Took a minute, but that little change makes sense. Something I can add to my little tool box.

Thanks Sergiy!

Tom257 SSChampion Points: 11543 More actions · Answer 4

Thanks Sergiy. I can use your suggestion in other places.

Howerver I think I over-simplified the problem in the original statement.

The users are allowed to create filters for their on-line reports. The filters translate into an SQL 'Where' clause. Currently the filters are in-memory only, but now they want to save the filters. Its still the same basic problem where the last item in the select is different from the rest.

As the first/last item different must be a fairly common requirement, I was wondering whether there is a generic or standard way of handling it, as my @MAXID method seems a bit clunky.

so here is a complete piece of code, including CREATE TABLE and sample data.

-- *** SET UP START ********************************************
CREATE TABLE dbo.Poms_LB_Criteria (
 pKey INT IDENTITY (1,1) PRIMARY KEY,
 ColumnType varchar(50),
 ColumnName varchar(50),
 DataType varchar(10),
 Operator varchar(10),
 [Value] varchar(80),
 Comparison varchar(10) )
--INSERTs generated by 'sp_generate_inserts' stored procedure written by Vyas
--Build number: 22
--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com
--http://vyaskn.tripod.com
 
SET NOCOUNT ON
 
SET IDENTITY_INSERT [dbo].[Poms_LB_Criteria] ON
GO
 

PRINT 'Inserting values into [Poms_LB_Criteria]'

INSERT INTO [Poms_LB_Criteria]

([pKey],[ColumnType],[ColumnName],[DataType],[Operator],[Value],[Comparison])VALUES(2,'Order_Card','COLOUR','String','=','''C.306''','AND')
INSERT INTO [Poms_LB_Criteria]

([pKey],[ColumnType],[ColumnName],[DataType],[Operator],[Value],[Comparison])VALUES(3,'Order_Card','BUILD_TO_MARKET','String','<>','''N''','O

R')
INSERT INTO [Poms_LB_Criteria]

([pKey],[ColumnType],[ColumnName],[DataType],[Operator],[Value],[Comparison])VALUES(4,'Order_Card','COLOUR','String','=','''C.664''','OR')
INSERT INTO [Poms_LB_Criteria]

([pKey],[ColumnType],[ColumnName],[DataType],[Operator],[Value],[Comparison])VALUES(5,'Order_Card','BUILD_TO_MARKET','String','=','''Y''','AN

D')

PRINT 'Done'
 
 
SET IDENTITY_INSERT [dbo].[Poms_LB_Criteria] OFF
GO
SET NOCOUNT OFF
-- *** SET UP END ********************************************

-- This creates the correct required output 
DECLARE @MAXID INT
SELECT @MAXID = MAX(pKey) FROM Poms_LB_Criteria
SELECT  CASE pKey
  WHEN @MAXID THEN '(' + ColumnName + ' ' + Operator + ' ' + Value + ') ' 
  ELSE '(' + ColumnName + ' ' + Operator + ' ' + Value + ') ' + Comparison
  END
FROM Poms_LB_Criteria
ORDER BY pKey

-- this is my attempt at applying Sergiy's suggestion to the problem

DECLARE @QueryList varchar(8000)
DECLARE @NL char(2)
SET @NL = CHAR(13) + CHAR(10)  -- just a newline constant 
SELECT @QueryList = IsNull(@QueryList + '(' + ColumnName + ' ' + Operator + ' ' + Value + ') ' + Comparison + @NL , '') +
      '(' + ColumnName + ' ' + Operator + ' ' + Value + ')' + @NL
FROM Poms_LB_Criteria
PRINT @QueryList

All suggestions welcome!

Tom

Sergiy SSC Guru Points: 110208 More actions · Answer 5

You overcomplicated this.

Added something which was not in my query:

SELECT @QueryList = IsNull(@QueryList + Comparison , '') +

'(' + ColumnName + ' ' + Operator + ' ' + Value + ')' + @NL

_____________
Code for TallyGenerator

Tom257 SSChampion Points: 11543 More actions · Answer 6

Thanks again.

(Can't believe my mistake in mis-reading your post )

Tom