First and/or Last Item different in Select

Question

Post reply

First and/or Last Item different in Select

Tom257

SSChampion

Points: 11475
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: 442447 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: 110192 More actions · Answer 2

Sergiy

SSC Guru

Points: 110192

May 30, 2007 at 4:26 pm

#709843

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: 442447 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442447

May 30, 2007 at 5:47 pm

#709862

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

Thanks Sergiy!

Tom257 SSChampion Points: 11475 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