CODEWITHSUNDEEP
sql-servert-sqlcommon-table-expressionrow-number
salvationishere
salvationishere

Reputation: 3511

creating a TSQL CTE to auto increment with a varchar(max)

I have the following CTE:

;WITH combo (id, [program_name]) AS
(
    SELECT
    1
    , CAST('' AS VARCHAR(MAX))
    UNION ALL
    SELECT
        cte.id + 1
        ,(cte.[program_name] + pl.[program_name] + '; ')
    FROM (
        SELECT
        RowNum = ROW_NUMBER() OVER (ORDER BY people_id)
        --people_id
        ,p.[program_name]
        FROM event_log_Rv E
        JOIN PROgrams_view p on p.program_info_id = e.program_providing_service
 where people_id = 'DFA3AFE5-F681-4B1B-89F0-31D04FA6BF7D'
   ) pl
    JOIN combo cte ON pl.RowNum = cte.id
)
SELECT TOP 1 id, [program_name] FROM combo ORDER BY id DESC

When I run this CTE I get this error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

The people_id and program_providing_service are uniqueidentifier datatypes.

How can I add in a column to this CTE that will list the line number? What I ultimately want to do is have this CTE display all programs / client.

I was trying to get this to work by adding in a row number to join CTE and combo in this CTE, but if you can think of a different way to display all programs / client in just one row I can accept this.

For example, I want:

people_id                               Programs
DFA3AFE5-F681-4B1B-89F0-31D04FA6BF7D    IHS; MCP; DCS

Upvotes: 1

Views: 1432

Answers (1)

djangojazz
djangojazz

Reputation: 13252

Comma Seperated list of a one to many relationship put into a single column where it is taking many rows and putting them in a '(char)' seperated list? I use the xml type for this as you can have complex structures that may change over time. Recursion is pretty intense for that sort of thing IMHO.

Here is a self extracting example and below I will explain it:

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Sean'),('Chad'),('Michael'),('Ray'),('Erik'),('Queyn');

declare @Orders table ( OrderID int identity, PersonID int, Desciption varchar(32), Amount int);

insert into @Orders values (1, 'Shirt', 20),(1, 'Shoes', 50),(2, 'Shirt', 22),(2, 'Shoes', 52),(3, 'Shirt', 20),(3, 'Shoes', 50),(3, 'Hat', 20),(4, 'Shirt', 20),(5, 'Shirt', 20),(5, 'Pants', 30),
(6, 'Shirt', 20),(6, 'RunningShoes', 70),(7, 'Shirt', 22),(7, 'Shoes', 40),(7, 'Coat', 80)

Select top 100
    p.person
,   o.Desciption
from @Person p
    join @Orders o on p.personID = o.PersonID
order by p.person

Select 
    p.person
,   replace(
        replace(
            replace(
            (
                select
                    o.Desciption as d
                from @Orders o
                where o.PersonID = p.personID
                for xml auto
            ), '"/><o d="', ', ')
        , '<o d="', '') 
    , '"/>', '') as SeperatedList
from @Person p
order by p.person
  1. I am doing a nested select to get all the child elements (in my example orders) of a parent object (person in my example).
  2. You cannot return an array in TSQL as a column.... unless it is stored as xml which can be it's own multi elemental tree structure as a single column.
  3. By nature 'for xml auto' will give me back text instead of the xml type (kind of cool). There is a lot I am leaving out and there is a whole section of white papers on just using 'FOR XML (something)'. Auto assumes the element name is my alias of 'o' though and my column is named 'd' which becomes an attribute.
    1. Since the cast is done by default to text I don't have to worry about a casting issue because I need to do some find and replaces
    2. I basically find the labels I applied for my elements and columns and 'replace()' them with a column seperator. I chose ',', you may use whatever you want.
    3. I replace the end and beginning elements with two more. Voila you now have a '(char)' seperated list.

This method works well as it seperates the child grouping in it's own dataset and from what I have seen is usually pretty fast compared to string operations pasting on to an existing string due to you have to do that method, once plus memory, once plus memory plus memory, once plus memory plus memory.... You get the idea. This method gets everything at once for a dataset and then just evaluates the presentation to the end user instead. If you have millions of child rows and it is slow I would consider dumping to a CTE, table variable, or temp table first and then cast those as XML columns first potentially.

Upvotes: 1

Related Questions