SQL Server: collect values in an aggregation temporarily and reuse in the same query
- by Erwin Brandstetter
How do I accumulate values in t-SQL? AFAIK there is no ARRAY type.
I want to reuse the values like demonstrated in this PostgreSQL example using array_agg().
SELECT a[1] || a[i] AS foo
,a[2] || a[5] AS bar -- assuming we have >= 5 rows for simplicity
FROM (
SELECT array_agg(text_col ORDER BY text_col) AS a
,count(*)::int4 AS i
FROM tbl
WHERE id between 10 AND 100
) x
How would I best solve this with t-SQL?
Best I could come up with are two CTE and subselects:
;WITH x AS (
SELECT row_number() OVER (ORDER BY name) AS rn
,name AS a
FROM #t
WHERE id between 10 AND 100
), i AS (
SELECT count(*) AS i
FROM x
)
SELECT (SELECT a FROM x WHERE rn = 1) + (SELECT a FROM x WHERE rn = i) AS foo
,(SELECT a FROM x WHERE rn = 2) + (SELECT a FROM x WHERE rn = 5) AS bar
FROM i
Test setup:
CREATE TABLE #t(
id INT PRIMARY KEY
,name NVARCHAR(100))
INSERT INTO #t VALUES
(3 , 'John')
,(5 , 'Mary')
,(8 , 'Michael')
,(13, 'Steve')
,(21, 'Jack')
,(34, 'Pete')
,(57, 'Ami')
,(88, 'Bob')
Is there a simpler way?
