SQL Declare Query only returns 1 row result

Question

I'm trying to get a result from our db with one identifier (ArtNr) and one big chunk of text that also contains returns (Edit), I also want the text from the (Edit) column to be stringed? (like this: "texttexttext" it's the "" I need in the beginning and end of the text)

I have come up with the query below but it only gives 1 row in reply (should be like 4000) and it does of course not give me the Edit captioned with "" since I have no clue how to do it :)

Thanks in advance! /Christian

USE MSPes2t 

DECLARE @result nvarchar(max) , @test nvarchar(255)

SELECT @result = AR.Edit , @test = AR.ArtNr

FROM DBO.AR

WHERE AR.ArtNr = '%'

ORDER BY AR.ArtNr

SELECT  @test AS artnr,
        @result AS edit

This is the result im searching for: Like this:

ArtNr | Edit
------+----------------------------------------
12001 | "edit"
12002 | "edit"
28001 | "edit"

Side note: `WHERE AR.ArtNr = '12001' ORDER BY AR.ArtNr` - what's that order meant to achieve? We know all rows have the same value for that column so no sorting is required. — Damien_The_Unbeliever, Apr 09 '15 at 07:41
Note/Correction: Sorry sent you a example where i tried with 1 specific row, artnr 12001. the WHERE could be just anything % (I want all) — Christian, Apr 09 '15 at 09:41

score 0 · Accepted Answer · edited May 23 '17 at 11:50

The following query should give you the desired results (although I am not exactly sure how you want to delimit your edits, I have used a semi-colon):

SELECT  ArtNr = '12001',
        Edit = STUFF((  SELECT  ';"' + ar.Edit + '"'
                        FROM    dbo.AR
                        WHERE   AR.ArtNr = '12001'
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

A full explanation of how using the xml extension FOR XML PATH() works to concanate rows into a single field can be found in this answer

If you need to get the edits concatenated for more than one ArtNo then you could use somethig like:

SELECT  ar.ArtNr,
        Edit = STUFF((  SELECT  ';"' + ar2.Edit + '"'
                        FROM    dbo.AR AS ar2
                        WHERE   AR2.ArtNr = ar.ArtNr
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    dbo.Ar
WHERE   ar.ArtNr IN ('12001', '12002')
GROUP BY ar.ArtNr;

SIMPLE WORKING EXAMPLE

DECLARE @AR TABLE (ArtNr VARCHAR(5), Edit NVARCHAR(MAX));
INSERT @AR (ArtNr, Edit)
VALUES ('12001', 'editeditedit'), ('12001', 'edit2 edit2'),
    ('12001', 'edit3'), ('12002', 'edit2 edit2');

SELECT  ar.ArtNr,
        Edit = STUFF((  SELECT  ';"' + ar2.Edit + '"'
                        FROM    @AR AS ar2
                        WHERE   AR2.ArtNr = ar.ArtNr
                        FOR XML PATH(''), TYPE
                        ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM    @AR AS ar
WHERE   ar.ArtNr IN ('12001', '12002')
GROUP BY ar.ArtNr;

Gives:

ArtNr | Edit
------+----------------------------------------
12001 | "editeditedit";"edit2 edit2";"edit3"
12002 | "editeditedit"

EDIT

Based on your required output I think your query is as simple as concatenating " to either end of your edit field:

SELECT  AR.ArtNr,
        '"' + AR.edit + '"' AS Edit
FROM    DBO.AR
WHERE   AR.ArtNr = '%'
ORDER BY ArtNr;

In SQL Server 2012 and later you can use CONCAT but it doesn't offer much advantage in this scenario.

Impressive the smallest code gave the correct answer, huge thanks for your support! — Christian, Apr 09 '15 at 11:00

SQL Declare Query only returns 1 row result

1 Answers1