SQLTeam.com | Weblogs | Forums

SELECT STUFF returning incorrect values

sql2012
#1

I have used STUFF in the past and with no problems but this one is a little different. Maybe due to the table structure. I have set up some nice testing script if anyone wants to take a look at it. Any input is appreciated.

DECLARE @DetailTbl TABLE(DetailID INT IDENTITY(1,1) NOT NULL, Roll VARCHAR(6))
INSERT INTO @DetailTbl (Roll) VALUES ('ABC')
INSERT INTO @DetailTbl (Roll) VALUES ('DEF')
INSERT INTO @DetailTbl (Roll) VALUES ('JKL')
INSERT INTO @DetailTbl (Roll) VALUES ('MNO')

DECLARE @RunTbl TABLE(ID INT IDENTITY(1,1) NOT NULL, Run VARCHAR(6))
INSERT INTO @RunTbl (Run) VALUES ('X12')
INSERT INTO @RunTbl (Run) VALUES ('Y34')
INSERT INTO @RunTbl (Run) VALUES ('Z56')

DECLARE @RunRollRef TABLE(ID INT IDENTITY(1,1) NOT NULL, RunID INT, DetailID INT)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (1, 1)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (1, 2)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (1, 3)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (2, 4)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (2, 1)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (3, 2)
INSERT INTO @RunRollRef (RunID, DetailID) VALUES (3, 3)

SELECT rt.ID,

(SELECT
STUFF((SELECT dt.Roll + ','
FROM @DetailTbl dt
INNER JOIN @RunRollRef ref ON ref.DetailID = dt.DetailID
WHERE dt.DetailID = r.DetailID
FOR XML PATH(''), TYPE).value('.','VARCHAR(MAX)'), 1, 0, '')) 
AS Rolls

FROM @RunTbl rt
INNER JOIN @RunRollRef r ON r.RunID = rt.ID

--Expected Output
--1 ABC,DEF,JKL,
--2 MNO,ABC,
--3 DEF,JKL,
#2

Is this what your'e looking for:

select a.id
      ,stuff((select c.roll,','
                from @runrollref as b
                     inner join @detailtbl as c
                             on c.detailid=b.detailid
               where b.runid=a.id
                 for xml path('')
                        ,type
             ).value('.','varchar(max)')
            ,1,0,''
            )
       as rolls
  from @runtbl as a
;
#3

Yes, that's it but just got it.

	SELECT rt.ID,
   (SELECT STUFF((SELECT ',' + dt.Roll
                  FROM @DetailTbl dt JOIN
                       @RunRollRef ref
                       ON ref.DetailID = dt.DetailID
                  WHERE ref.RunID = rt.ID
                  FOR XML PATH(''), TYPE
                 ).value('.', 'VARCHAR(MAX)'
                        ), 1, 1, '')
                ) AS Rolls
 FROM @RunTbl rt;