我有一个连接多个记录的填充函数,我在每一个记录后面加一个换行符,它可以很好地处理这个查询:
STUFF((
SELECT CASE WHEN ROW_NUMBER() OVER (order by new_name) % 2 = 1 THEN CHAR(10) ELSE ',' END + new_name
FROM new_subcatagories
FOR XML PATH('')), 1, 1, '')
结果是
Auditory,Kinesthetic vestibular
Multitasking,Planning & organization
Proprioception,Tactile
Vestibular tactile,Visual
但我现在想用另一个需要区分的列来实现这一点,但我无法使其工作,我的查询是:
STUFF((
SELECT distinct (CASE WHEN ROW_NUMBER() OVER (order by new_maincatgoriesname) % 2 = 1 THEN CHAR(10) ELSE ',' END
+ new_maincatgoriesname)
FOR XML PATH('')), 1, 1, '')
我得到的结果是以多种意想不到的方式,例如
Executive Function
Sensory Discrimination
Sensory modulation ,Multitasking,Sensory Discrimination,Sensory modulation
或者其他不期望的方式,我希望结果是
Executive Function,Sensory Discrimination
Sensory modulation,Multitasking
如果有人能帮助我,我将不胜感激。
DISTINCT
适用于整行,因此用不需要的数据(如ROW_NUMBER()
(填充额外的列会产生无效结果。
若要修复此问题,您需要添加另一个查询嵌套级别。
DECLARE @Blah TABLE( new_maincatgoriesname VARCHAR( 200 ))
INSERT INTO @Blah
VALUES( 'Executive Function' ), ( 'Sensory Discrimination' ), ( 'Multitasking' ),
( 'Sensory Discrimination' ), ( 'Executive Function' ), ( 'Sensory modulation' )
SELECT
STUFF( CAST((
-- Step 2: manipulate result of Step 1
SELECT (CASE WHEN ROW_NUMBER() OVER (order by new_maincatgoriesname) % 2 = 1 THEN CHAR(10) ELSE ',' END + new_maincatgoriesname )
FROM
-- Step 1: Get distinct values
( SELECT DISTINCT new_maincatgoriesname
FROM @Blah ) AS MainQuery
FOR XML PATH('') ) AS VARCHAR( 2000 )), 1, 1, '' )
输出:
Executive Function,Multitasking
Sensory Discrimination,Sensory modulation