Building strings from records

Please excuse my topic title... i couldn't think how best to word it! :smile:

I have columns similar to the example below and need to return the ItemQty, ItemNo and ItemDescription and in a single string (alias ItemsRqd) for each DocNo, e.g.

DocNo ..... ItemsRqd
1 .............. 2 x A001 Item A, 10 x A002 Item B, 7 x A003 Item C
2 .............. 1 x A001 Item A, 14 x A002 Item B

EXAMPLE....

DECLARE @Items TABLE (DocNo INT, ItemNo VARCHAR(20), ItemDescription VARCHAR(50), ItemQty INT)

INSERT INTO @Items
SELECT 1, 'A001', 'Item A', 2
UNION ALL
SELECT 1, 'A002', 'Item B', 10
UNION ALL
SELECT 1, 'A003', 'Item C', 7
UNION ALL
SELECT 2, 'A001', 'Item A', 1
UNION ALL
SELECT 2, 'A002', 'Item B', 14

The maximum number of items in the string is 25. I have played about with a solution using ROW_NUMBER() partitioned by DocNo combined with a CASE statement to build the string but the query runs very slow.

Can anyone offer advice?

Clutching at straws, this is the best solution I could come up with....

DECLARE @Items TABLE (DocNo INT, ItemNo VARCHAR(20), ItemDescription VARCHAR(50), ItemQty INT)

INSERT INTO @Items
SELECT 1, 'A001', 'Item A', 2
UNION ALL
SELECT 1, 'A002', 'Item B', 10
UNION ALL
SELECT 1, 'A003', 'Item C', 7
UNION ALL
SELECT 2, 'A001', 'Item A', 1
UNION ALL
SELECT 2, 'A002', 'Item B', 14

SELECT *,ROW_NUMBER() OVER (PARTITION BY DocNo ORDER BY DocNo) AS RowNo
INTO #Results
FROM @Items

SELECT
DocNo
,MAX(CASE WHEN RowNo = 1 THEN CAST(ItemQty AS VARCHAR(5)) + ' x ' + ItemNo + ', ' + ItemDescription ELSE '' END) + ', ' +
MAX(CASE WHEN RowNo = 2 THEN CAST(ItemQty AS VARCHAR(5)) + ' x ' + ItemNo + ', ' + ItemDescription ELSE '' END) + ', ' +
MAX(CASE WHEN RowNo = 3 THEN CAST(ItemQty AS VARCHAR(5)) + ' x ' + ItemNo + ', ' + ItemDescription ELSE '' END) + ', ' +
MAX(CASE WHEN RowNo = 4 THEN CAST(ItemQty AS VARCHAR(5)) + ' x ' + ItemNo + ', ' + ItemDescription ELSE '' END) + ', ' +
MAX(CASE WHEN RowNo = 5 THEN CAST(ItemQty AS VARCHAR(5)) + ' x ' + ItemNo + ', ' + ItemDescription ELSE '' END)
FROM #Results
GROUP BY DocNo
DROP TABLE #Results

SELECT    distinct d.DocNo, STUFF(item_qty, 1, 1, '')
FROM    @Items d
    CROSS APPLY
    (
        SELECT    ',' + CONVERT(VARCHAR(5), ItemQty) + ' x ' + ItemNo + ', ' + ItemDescription
        FROM    @Items x
        WHERE    x.DocNo    = d.DocNo
        FOR XML PATH('')
    ) i (item_qty)

what if you have more than 25 items per DocNo, how do you want to with item 26th onwards ?

Thanks for your help. I have never used "cross apply" or "stuff" so I look forward to trying this later on.
I have been informed by the end user that there will never be more than 25 items but, ideally, the solution should be able to cope with any number of items.

yes it can

Perfect! Can't wait to try it out later this morning. Thank so much for your advice.

I built this into my proc and it works a treat. It took me a while to figure out what role STUFF played in the script but now I understand. I still don't really understand why the the column name (item_qty) has to appear after the derived table. Is this a attribute of the FOR XML PATH(' ') or the CROSS APPLY structure?

actually it as nothing to do with cross apply or an attribute of for xml. It is just a column alias for the derived table.

for example,

select    *
from
(
    select    name , object_id
    from    sys.tables
) d (table_name, table_id)

For the query, for xml path does not return a usable column name. "item_qty" is to give it an alias so that you can reference the column in the SELECT clause

I see! I could see that the column alias was required and I also discovered that I could not add the alias inside the derived table itself. Thanks for clarifying.

One more question, and I appreciate this might be difficult to answer without further information. I ran my original query (the one that uses 25 CASE statements to build the string) and compared it with my new query in which the FOR XML PATH(' ') was used and my original query was twice as fast. Would this be because my original query is limited to 25 items in the string whereas your solution returns unlimited items in the string?

Do you have a DocNo table ? Change to that

SELECT  d.DocNo, STUFF(item_qty, 1, 1, '')
FROM    @docs d

I really dislike that way of aliasing column names - I see it a lot on CTEs.

My objection is that it "removes" the expressions in the SELECT from the name-definition in the Alias. Too much risk IMO of columns / expressions being added (to the SELECT) in the future, either during this DEV cycle or in future maintenance cycles, without someone spotting that there is an Alias name list.

I've always done it like this:

select    *
from
(
    select    name AS [table_name]
            , object_id AS [table_id]
    from    sys.tables
) d

is there some difference between the two that I'm overlooking? or a particular reason for using the alias name list method?

that is just to illustrate the usage of column alias on derived table.

For the APPLY join to a for xml statement, there isn't a choice but to do that.

I know it can be done directly with sub-query, personally, i prefer to do it in APPLY, makes my SELECT statement cleaner and easier to read. :grinning:

I'm finding uses for that too, for example:

SELECT Col1, Col2, ..., Coln, 
       CASE WHEN Col1 = 1 THEN 'My Error Message' ELSE '' END AS ErrorMessage
FROM MyTable

so that I get a nice error message at the end of the line. BUT ... then I have to scroll to the end of the line to see if there is any error ... so I change to

SELECT 
    CASE WHEN ErrorMessage = '' THEN '' ELSE '**' END,
    Col1, Col2, ..., Coln, ErrorMessage
FROM MyTable
CROSS APPLY
(
    SELECT CASE WHEN Col1 = 1 THEN 'My Error Message' 
                ELSE '' END AS ErrorMessage
) AS X

Keeps the final SELECT more neat-and-tidy and removes any slight-similar-duplicated-code

1 Like