Reshape SQL date

Question

following problem

I am using the command to join Names with project numbers

SELECT DDR_namen.vorname_nachname, DDR_Erfinder_final.pubnr
FROM DDR_namen
RIGHT JOIN DDR_Erfinder_final
ON DDR_namen.vorname_nachname=DDR_Erfinder_final.vorname_nachname

which gives me someting like this (small example)

vorname_nachname  Pubnr (ID)
A. Heinrich       100
B. Müller         100
B. Müller         101
B. Müller         105
C. Krüger         120
C. Krüger         100

Now I want to reshape the data, so that the rows are unique and the ID are combined into a new collumn. Like this.

vorname_nachname  Pubnr (ID)
A. Heinrich       100
B. Müller         100;101;105
C. Krüger         120;100

Anybody and ideas?

Unfortunately, there's no concat aggregate function in Sql Server, so this isn't super easy to do. This [here](http://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) might help you though. — Tobberoth, Nov 12 '13 at 10:08

Upendra Chaudhari · Accepted Answer · 2013-11-12T10:28:37.117

Try grouping like

select vorname_nachname, 
(
    SELECT  STUFF((SELECT ';' + CAST(Pubnr AS VARCHAR(MAX)) 
    FROM    TestTable
    WHERE   vorname_nachname = t1.vorname_nachname
    FOR XML PATH('') ), 1, 1, '')
) AS Pubnr
from TestTable t1
group by vorname_nachname

SQL Fiddle Demo : http://sqlfiddle.com/#!3/d0916/1

UPDATE : For JOIN you can use CTE like below :

;WITH CTE AS
(
  SELECT DDR_namen.vorname_nachname, DDR_Erfinder_final.pubnr
  FROM DDR_namen
  RIGHT JOIN DDR_Erfinder_final
  ON DDR_namen.vorname_nachname=DDR_Erfinder_final.vorname_nachname

  )
select vorname_nachname, 
(
    SELECT  STUFF((SELECT ';' + CAST(Pubnr AS VARCHAR(MAX)) 
    FROM    CTE
    WHERE   vorname_nachname = t1.vorname_nachname
    FOR XML PATH('') ), 1, 1, '')
) AS Pubnr
from CTE t1
group by vorname_nachname

Stupid question how can I tell sql to write this in a new table. — user2963882, Nov 12 '13 at 11:25

Reshape SQL date

1 Answers1