SELECT within a SELECT problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

SELECT within a SELECT problem

Author

Topic

bh0526
Yak Posting Veteran

71 Posts

Posted - 2012-01-11 : 14:25:35

I have the following SQL Statement:

SELECT TANKID, PRODUCTIONDATE ITEMID, ProdID,
(SELECT TRANSDATE
FROM PRODJOURNALBOM AS P
WHERE P.ProdID = WT.BOTTLINGPRODID) AS BottledDates
FROM WFSTANK WT

But this bombs with this error message:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

There is usually 2 or 3 TRANSDATE rows found. I would like to return a value like: 1/1/2012, 1/5/2012. In this example, 2 rows were found. Is there a way to concatenate these values into a string seperated by commas (or anything really)?

Thanks,

Bob

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-11 : 14:37:12

Would this work for you?

SELECT DISTINCT
	TANKID,
	PRODUCTIONDATE ITEMID,
	ProdID,
	STUFF((
	    SELECT
	    	',' + CONVERT(VARCHAR(10),TRANSDATE,101) AS [text()]
	    FROM
	    	PRODJOURNALBOM AS P
	    WHERE
	    	P.ProdID = WT.BOTTLINGPRODID
	    FOR XML PATH('')
	),1,1,'') AS BottledDates
FROM
	WFSTANK WT

bh0526
Yak Posting Veteran

71 Posts

Posted - 2012-01-11 : 15:24:30

That worked!

Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources