StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><strong>Edit 1: I have added another solution that shows how to simulate ARRAY_AGG on SQL Server (the last answer).</strong></p> <p><strong>Edit 2: For the solution number 4) I have added the third method for concatenation.</strong></p> <p>I'm not sure I have I understood correctly your question.</p> <p>a) Instead of using arrays in SQL Server I would use table variables or XML.</p> <p>b) To concatenate strings (in this case) I would use <code>SELECT @var = @var + Name FROM tbl</code> statements or <code>XML xqueries</code>.</p> <p>c) The solution based on CTEs and multiple subqueries (<code>WITH cte AS () FROM SELECT (SELECT * FROM cte.rn=1) + ()...</code>) will generates a lot of scans and logical reads. </p> <p>Solutions: 1) Table variable + <code>SELECT @var = @var + Name FROM tbl</code>:</p> <pre><code>--Creating the "array" DECLARE @Array TABLE ( Idx INT PRIMARY KEY, Val NVARCHAR(100) NOT NULL ); WITH Base AS ( SELECT Val = t.name, Idx = ROW_NUMBER() OVER(ORDER BY t.name ASC) FROM #t t WHERE t.id between 10 AND 100 ) INSERT @Array (Idx, Val) SELECT b.Idx, b.Val FROM Base b; --Concatenating all names DECLARE @AllNames NVARCHAR(4000); --”Reset”/Init @AllNames SET @AllNames = ''; --String concatenation SELECT @AllNames = @AllNames + ',' + a.Val FROM @Array a; --Remove first char (',') SELECT @AllNames = STUFF(@AllNames, 1, 1, ''); --The final result SELECT @AllNames [Concatenating all names - using a table variable]; /* Concatenating all names - using a table variable ------------------------------------------------ Ami,Bob,Jack,Pete,Steve */ --Concatenating Idx=2 and Idx=5 --”Reset” @AllNames value SET @AllNames = ''; --String concatenation SELECT @AllNames = @AllNames + ',' + a.Val FROM @Array a WHERE a.Idx IN (2,5) --or a.Idx IN (2, (SELECT COUNT(*) FROM @Array)) ORDER BY a.Idx ASC; --Remove first char (',') SELECT @AllNames = STUFF(@AllNames, 1, 1, ''); --The final result SELECT @AllNames [Concatenating Idx=2 and Idx=5 - using a table variable]; /* Concatenating Idx=2 and Idx=5 - using a table variable ------------------------------------------------------ Bob,Steve */ </code></pre> <p>2) Table variable + <code>PIVOT</code>:</p> <pre><code>--Concatenating a finite number of elements (names) SELECT pvt.[1] + ',' + pvt.[0] AS [PIVOT Concat_1_and_i(0)] ,pvt.[2] + ',' + pvt.[5] AS [PIVOT Concat_2_and_5] ,pvt.* FROM ( SELECT a.Idx, a.Val FROM @Array a WHERE a.Idx IN (1,2,5) UNION ALL SELECT 0, a.Val --The last element has Idx=0 FROM @Array a WHERE a.Idx = (SELECT COUNT(*) FROM @Array) ) src PIVOT (MAX(src.Val) FOR src.Idx IN ([1], [2], [5], [0])) pvt; /* PIVOT Concat_1_and_i(0) PIVOT Concat_2_and_5 ----------------------- -------------------- Ami,Steve Bob,Steve */ </code></pre> <p>3) XML + XQuery:</p> <pre><code>SET ANSI_WARNINGS ON; GO DECLARE @x XML; ;WITH Base AS ( SELECT Val = t.name, Idx = ROW_NUMBER() OVER(ORDER BY t.name ASC) FROM #t t WHERE t.id BETWEEN 10 AND 100 ) SELECT @x = ( SELECT b.Idx AS [@Idx] ,b.Val AS [text()] FROM Base b FOR XML PATH('Element'), ROOT('Array') ); /* @x content <Array> <Element Idx="1">Ami</Element> <Element Idx="2">Bob</Element> <Element Idx="3">Jack</Element> <Element Idx="4">Pete</Element> <Element Idx="5">Steve</Element> </Array> */ --Concatenating all names (the result is XML, so a cast is needed) DECLARE @r XML; --XML result SELECT @r=@x.query(' (: $e = array element :) for $e in (//Array/Element) return string($e) '); SELECT REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating all names - using XML]; /* Concatenating all names - using XML ----------------------------------- Ami,Bob,Jack,Pete,Steve */ --Concatenating Idx=1 and all names SELECT @r=@x.query(' (: $e = array element :) for $e in (//Array/Element[@Idx=1], //Array/Element) return string($e) '); SELECT REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating Idx=1 and all names - using XML]; /* Concatenating Idx=1 and all names - using XML --------------------------------------------- Ami,Ami,Bob,Jack,Pete,Steve */ --Concatenating Idx=1 and i(last name) DECLARE @i INT; SELECT @r=@x.query(' (: $e = array element :) for $e in (//Array/Element[@Idx=1], //Array/Element[@Idx=count(//Array/Element)]) return string($e) '); SELECT REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating Idx=1 and i(last name) - using XML]; /* Concatenating Idx=1 and i(last name) - using XML ------------------------------------------------ Ami,Steve */ --Concatenating Idx=2 and Idx=5 SELECT @r=@x.query(' (: $e = array element :) for $e in (//Array/Element[@Idx=2], //Array/Element[@Idx=5]) return string($e) '); SELECT REPLACE(CONVERT(NVARCHAR(4000), @r), ' ', ',') AS [Concatenating Idx=2 and Idx=5 - using XML (method 1)]; /* Concatenating Idx=2 and Idx=5 - using XML (method 1) ---------------------------------------------------- Bob,Steve */ --Concatenating Idx=2 and Idx=5 SELECT @x.value('(//Array/Element)[@Idx=2][1]', 'NVARCHAR(100)') + ',' + @x.value('(//Array/Element)[@Idx=5][1]', 'NVARCHAR(100)') AS [Concatenating Idx=2 and Idx=5 - using XML (method 2)];; /* Concatenating Idx=2 and Idx=5 - using XML (method 2) ---------------------------------------------------- Bob,Steve */ </code></pre> <p><strong>4) If the question is how to simulate <code>ARRAY_AGG</code> on SQL Server then, one answer might be: by using XML.</strong> Example:</p> <pre><code>SET ANSI_WARNINGS ON; GO DECLARE @Test TABLE ( Id INT PRIMARY KEY ,GroupID INT NOT NULL ,Name NVARCHAR(100) NOT NULL ); INSERT INTO @Test (Id, GroupID, Name) VALUES (3 , 1, 'John') ,(5 , 1, 'Mary') ,(8 , 1, 'Michael') ,(13, 1, 'Steve') ,(21, 1, 'Jack') ,(34, 2, 'Pete') ,(57, 2, 'Ami') ,(88, 2, 'Bob'); WITH BaseQuery AS ( SELECT a.GroupID, a.Name FROM @Test a WHERE a.Id BETWEEN 10 AND 100 ) SELECT x.* , CONVERT(XML,x.SQLServer_Array_Agg).query (' for $e in (//Array/Element[@Idx=1], //Array/Element[@Idx=count(//Array/Element)]) return string($e) ') AS [Concat Idx=1 and Idx=i (method 1)] , CONVERT(XML,x.SQLServer_Array_Agg).query(' let $a := string((//Array/Element[@Idx=1])[1]) let $b := string((//Array/Element[@Idx=count(//Array/Element)])[1]) let $c := concat($a , "," , $b) (: " is used as a string delimiter :) return $c ') AS [Concat Idx=1 and Idx=i (method 2)] , CONVERT(XML,x.SQLServer_Array_Agg).query (' for $e in (//Array/Element[@Idx=(1,count(//Array/Element))]) return string($e) ') AS [Concat Idx=1 and Idx=i (method 3)] FROM ( SELECT a.GroupID ,(SELECT ROW_NUMBER() OVER(ORDER BY b.Name) AS [@Idx] ,b.Name AS [text()] FROM BaseQuery b WHERE a.GroupID = b.GroupID ORDER BY b.Name FOR XML PATH('Element'), ROOT('Array') ) AS SQLServer_Array_Agg FROM BaseQuery a GROUP BY a.GroupID ) x; </code></pre> <p>Results:</p> <pre><code>GroupID SQLServer_Array_Agg Concat Idx=1 and Idx=i (method 1) Concat Idx=1 and Idx=i (method 2) Concat Idx=1 and Idx=i (method 3) ------- ---------------------------------------------------------------------------------------------------------- --------------------------------- --------------------------------- --------------------------------- 1 <Array><Element Idx="1">Jack</Element><Element Idx="2">Steve</Element></Array> Jack Steve Jack,Steve Jack Steve 2 <Array><Element Idx="1">Ami</Element><Element Idx="2">Bob</Element><Element Idx="3">Pete</Element></Array> Ami Pete Ami,Pete Ami Pete </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload