Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server

Stupid CTE tricks -- string concatenation

5.00/5 (1 vote)
7 Jan 2012CPOL 9.5K  
Pretty cool little trick you've made there.With the help of an analytic function, I've fixed the ordering, and as a byproduct it also scales better:WITH ranked AS ( SELECT make,model,Rank() over (PARTITION BY make ORDER BY model) Rnk FROM MakeModel ),cte...
Pretty cool little trick you've made there.
With the help of an analytic function, I've fixed the ordering, and as a byproduct it also scales better:
WITH ranked AS (
    SELECT  make,model,Rank() over (PARTITION BY make ORDER BY model) Rnk 
    FROM    MakeModel
    )
,cte (make,models,rnk) AS
    (
    SELECT  Make
           ,Model Models
           ,rnk
    FROM    ranked
    WHERE   rnk = 1
    UNION ALL
    SELECT  cte.Make
           ,Models + N' , ' + Model
           ,r.Rnk
    FROM cte
    INNER JOIN ranked r
    ON cte.Make=r.Make
    AND cte.Rnk = r.rnk - 1
    )
SELECT Make,Max(Models) models,Max(rnk) cnt FROM cte GROUP BY make ORDER BY make

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)