Click here to Skip to main content
14,982,169 members
Articles / Database Development / SQL Server
Alternative
Tip/Trick
Posted 3 Jan 2012

Tagged as

Stats

8K views
1 bookmarked

Stupid CTE tricks -- string concatenation

Rate me:
Please Sign up or sign in to vote.
5.00/5 (1 vote)
7 Jan 2012CPOL
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)

Share

About the Author

Jörgen Andersson
Database Developer
Sweden Sweden
No Biography provided

Comments and Discussions

 
GeneralAh, very good. Pin
PIEBALDconsult2-Jan-12 3:46
professionalPIEBALDconsult2-Jan-12 3:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.