Writing my result as horizontal as String

Question

Can someone help me how I can rewrite this query to get following result.

 1   2   3   4   5
U.T A.H E.Z R.Z S.A

Sometimes it will return more or less than 5 results.

My query:

SELECT 
    LEFT(a.Vorname, 1) + '.' + LEFT(a.Name, 1) AS Name 
FROM 
    ADR_Adressen a 
LEFT JOIN 
    ADR_GruppenLink gl ON gl.AdressNrADR = a.AdressNrADR 
WHERE 
    a.Z_Klasse = 'BA' AND gl.GruppeADR != 'KIND'

Try looking at some of the ideas [here](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string). The 2nd one looks simple enough. — SMM, Jul 06 '16 at 12:21
Why do you need the data as columns? If you just want to transpose it for _display_ purposes that's a lot easier in the display layer (report, web page, form, etc) than in the data layer. — D Stanley, Jul 06 '16 at 13:16

score 1 · Answer 1 · edited Jan 18 '21 at 12:32

There's no pretty way to do this, if the number of rows is variable.

Building heavily on the answer to this question, you need to dynamically build the query before executing it.

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

-- Build a list of ids for each of the selected rows 
SELECT @cols = STUFF((SELECT ',[' + convert(varchar,ROW_NUMBER() OVER ( ORDER BY LEFT([vorname],1),LEFT([Name],1)  )  ) + ']'
    FROM #ADR_Adressen
    LEFT JOIN ADR_GruppenLink gl ON gl.AdressNrADR = a.AdressNrADR
    WHERE a.Z_Klasse = 'BA' AND gl.GruppeADR != 'KIND'
    FOR XML PATH(''), TYPE
      ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

-- Build a query using the list of IDs selected above.  These are pivotted into column names

set @query = N'SELECT ' + @cols + N' from 
             (
             select 
                ROW_NUMBER() OVER ( ORDER BY LEFT([vorname],1),LEFT([Name],1)  ) ID,
                LEFT(vorname,1) + ''.'' + LEFT(Name,1) Name
             from #ADR_Adressen a
             LEFT JOIN ADR_GruppenLink gl ON gl.AdressNrADR = a.AdressNrADR 
             WHERE a.Z_Klasse = 'BA' AND gl.GruppeADR != 'KIND'
            ) x
            pivot 
            (
                max(Name)
                for ID in (' + @cols + N')
            ) p 
            
            '

exec sp_executesql @query;

Updated: Given that no ID field is available I've updated this to incorporate the ROW_NUMBER suggestion from Alex below

Query example

Thanks for your help, i still have one Problem. I dont have any ID in my table. How can i solve this? Is it possible to build some row_number function into this query? @Paul Ellery — user2210516, Jul 06 '16 at 13:32

ɐlǝx · Answer 2 · 2016-07-07T07:18:34.873

this could help you, using ROW_NUMBER()

DECLARE @cols AS NVARCHAR(MAX)


SELECT  @cols = STUFF((SELECT ',[' + convert(varchar,ROW_NUMBER() OVER ( ORDER BY LEFT(a.[Name],1)  )  )+ ']'  AS ID
 FROM [ADR_Adressen] a 

 FOR XML PATH(''), TYPE).value('.', 'varchar(max)'),1,1, '')


DECLARE  @query  AS NVARCHAR(MAX);

SET @query = N'SELECT ' + @cols + N' from 
             (
            SELECT ROW_NUMBER() OVER ( ORDER BY LEFT(a.Name,1)  )   AS ID,
             LEFT(vorname,1) + ''.'' + LEFT(Name,1) Name
                from ADR_Adressen a
                LEFT JOIN ADR_GruppenLink gl ON gl.AdressNrADR = a.AdressNrADR 
                WHERE a.Z_Klasse = 'BA' AND gl.GruppeADR != 'KIND'
            ) x
            pivot 
            (
                max(Name)
                for ID in (' + @cols + N')
            ) p 

            '
exec sp_executesql @query;

Sorry I don't understand, what do you mean? – ɐlǝx Jul 07 '16 at 07:23 — ɐlǝx, Jul 07 '16 at 07:23

Writing my result as horizontal as String

2 Answers2