quote:

---

Originally posted by mike13

Hi all,


i want to select various rows into 1 output parameter.

I got this code, but for some reason i just get 1 record

SELECT DISTINCT @imageslist = isnull(@imageslist,'') + convert(varchar(255),T_Product_Main.Image,103) +',' + convert(varchar(255),T_Product_Description.Name,103) +'-'

FROM T_Product_CategorySub

what i'm doing wrong? ;-)

---

CREATE TABLE #tmp(col1 VARCHAR(32), col2 VARCHAR(32));
INSERT INTO #tmp VALUES ('A','B'),('C','D'),('C','D');

-- 1 This returns a,b,c,d,c,d
DECLARE @imageslist VARCHAR(256);
SELECT @imageslist = ISNULL(@imageslist,'') + col1 + ',' + col2 +',' FROM #tmp;
SELECT @imageslist;
GO

-- 2. This returns only one pair, most likely C,D.
DECLARE @imageslist VARCHAR(256);
SELECT DISTINCT @imageslist = ISNULL(@imageslist,'') + col1 + ',' + col2 +',' FROM #tmp;
SELECT @imageslist;
GO

-- 3. If you want distinct, use that in a suquery like this
DECLARE @imageslist VARCHAR(256);
SELECT @imageslist = ISNULL(@imageslist,'') + col1 + ',' + col2 +',' 
	FROM (SELECT DISTINCT col1, col2 FROM #tmp) s;
SELECT @imageslist;

DROP TABLE #tmp;

quote:

---

Originally posted by mike13

Thanks!!
It was the distinct!
that did the trick, didn't know you could do a subquery like that

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by mike13

Thanks!!
It was the distinct!
that did the trick, didn't know you could do a subquery like that

---

In the interest of proper terminology it's actually called a Derived Table or Inline View. :)

---