Forum Home
Press F1
 
Thread ID: 119657 2011-08-03 03:45:00 SQL view combining records Mike (15) Press F1
Post ID Timestamp Content User
1220651 2011-08-08 22:06:00 OK well I thought it was all working nicely, but have just discovered a problem. If any of the records has a Null for ModelNo (even if other ModelNo for same ShortID is not Null), the whole concatenated string is Null. Any ideas how I tell the above script to ignore the Null value (or substitute it with an empty "" string)?

Thanks,
Mike. 		Mike (15)
1220652 2011-08-08 22:06:00 Take a closer look at your GROUP BY clause; that should allow you to remove any duplicates.thanks for the suggestion nofam, but I'm afraid all I see is a bunch of words :(

Mike. 		Mike (15)
1220653 2011-08-09 01:54:00 OK well I thought it was all working nicely, but have just discovered a problem. If any of the records has a Null for ModelNo (even if other ModelNo for same ShortID is not Null), the whole concatenated string is Null. Any ideas how I tell the above script to ignore the Null value (or substitute it with an empty "" string)?

Thanks,
Mike.Use COALESCE - that's pretty much the whole point of this function :). COALESCE returns its first non-null argument. 		Erayd (23)
1220654 2011-08-09 04:02:00 Use COALESCE - that's pretty much the whole point of this function :). COALESCE returns its first non-null argument.

Thanks Erayd :)


with Ranked(SHORTID, rnk, MODELNO)
as( SELECT SHORTID, ROW_NUMBER() OVER (PARTITION BY SHORTID
ORDER BY SHORTID), CAST(ModelNO AS VARCHAR(8000))
FROM view_1), AnchorRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM Ranked
WHERE rnk = 1), RecurRanked(SHORTID, rnk, ModelNO) AS
(SELECT SHORTID, rnk, ModelNO
FROM AnchorRanked
UNION ALL
SELECT Ranked.SHORTID, Ranked.rnk, coalesce(RecurRanked.ModelNO + ', ', '') + Ranked.ModelNO
FROM Ranked INNER JOIN
RecurRanked ON Ranked.SHORTID = RecurRanked.SHORTID AND Ranked.rnk = RecurRanked.rnk + 1)
SELECT SHORTID, MAX(ModelNO) AS MODELS
FROM RecurRanked
GROUP BY SHORTID;

Cheers,
Mike. 		Mike (15)
1 2