CODEWITHSUNDEEP
sqlsql-server-2012
S M
S M

Reputation: 159

How to get result with square bracket surrounded to each value when using IN keyword in Pivot table?

I am writing the sql query for creating the pivot table. I have the sample code where I am getting the result without error but the result should be values surrounded by square bracket to each value instead to the whole result. How do I setup for it?

I have tried using quote function but it is putting square bracket around the whole output.

DECLARE @CityNames NVARCHAR(MAX) = '', @t varchar(max) = 'jay, sam'

SELECT  @CityNames +=   QUOTENAME(@t)+ ','


select @CityNames

I expect the output to be [jay],[sam], but the actual output is [jay,sam].

Upvotes: 0

Views: 131

Answers (2)

S M
S M

Reputation: 159

I have found the answer by using QUOTENAME() function, we can follow the below code or above code by @gordon Linoff.

 DECLARE @CityNames NVARCHAR(MAX) = '', @t nvarchar(max) = 'jay, sam', @result 
 varchar(max),  @sSQL nvarchar(max)

SELECT  @CityNames +=    + QUOTENAME(value)+','  from (select value from 
 dbo.fx_split( @t, ','))x


IF (RIGHT(@CityNames, 1) = ',')
   set @CityNames = LEFT(@CityNames, LEN(@CityNames) - 1)

   select @citynames

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

Is this what you want?

select '[' + @t + replace(@t, ', ', '], [') + ']')

QUOTENAME() treats the string as a single identifier, quoting appropriately The above should work for most reasonable column names.

Upvotes: 1

Related Questions