Please excuse my topic title... i couldn't think how best to word it!
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