CODEWITHSUNDEEP
sqlsql-server
pnduke
pnduke

Reputation: 183

Removing last comma in SELECT statement

I have a select query which looks like this:

SELECT 
ROW_NUMBER() OVER (ORDER BY T.TitleId) as Row, 
T.TitleId as TitleID, 
T.TitleSort as TitleName, 
TT.Description as TitleType, 
TS.Description as TitleOrigin,
--get genres
(select Genre.Description + ', ' from Genre where GenreId IN
(Select GenreId from TitleGenre WHERE TitleId = T.TitleId)
group by Genre.Description for xml path('')) AS Genres, ....

This code works, but I can't find a way to get rid of the last comma. This is how the returned row looks:

Action, Drama, Romance,

I need to get rid of last comma without using a function or declaring a variable, right here in the query. Is that possible? Thanks in advance for your answers.

Upvotes: 2

Views: 9799

Answers (3)

ryana
ryana

Reputation: 16

I've done something like below using window functions to optionally insert a comma.

SELECT
c.Name +
CASE WHEN 
ROW_NUMBER() OVER (PARTITION BY t.Name ORDER BY c.Name DESC)  
<>
COUNT(t.name) OVER (PARTITION BY t.Name)   then ',' else '' END

FROM sys.tables t
INNER JOIN sys.columns c
on t.Object_id = c.Object_id

FOR XML PATH('')

Basically add a comma unless it's the last record of the result set.

Upvotes: 0

Taryn
Taryn

Reputation: 247810

You can always use the following which uses Left() and Len():

select *,
  left(Genres, len(Genres)-1) as Genres
from 
(
  SELECT 
  ROW_NUMBER() OVER (ORDER BY T.TitleId) as Row, 
  T.TitleId as TitleID, 
  T.TitleSort as TitleName, 
  TT.Description as TitleType, 
  TS.Description as TitleOrigin,
  --get genres
  (select Genre.Description + ', ' from Genre where GenreId IN
  (Select GenreId from TitleGenre WHERE TitleId = T.TitleId)
  group by Genre.Description for xml path('')) AS Genres, ....
) src

Upvotes: 5

sgeddes
sgeddes

Reputation: 62851

Something like this should be close -- basically move the comma to the beginning and then remove it with STUFF:

STUFF(
        (
        select ', ' + Genre.Description from Genre where GenreId IN
(Select GenreId from TitleGenre WHERE TitleId = T.TitleId)
group by Genre.Description for xml path('')
        ), 1, 2, '')

Good luck.

Upvotes: 9

Related Questions