T-SQL - how to select rows distinct and move one row to column

Question

I need to query a table in order to return rows, but I am not able to query the table correctly. Here is my table view:

Base    Altezza Materiale
10      10      Ferro
10      10      Legno
10      30      Ferro
10      30      Legno
10      30      Acciaio
20      20      Legno

The result set should return:

Base    Altezza Materiale
10      10      Ferro, Legno
10      30      Ferro, Legno, Acciaio
20      20      Legno

Can someone help me please?

http://stackoverflow.com/questions/1874966/concatenate-row-values-t-sql OR http://stackoverflow.com/questions/16525064/sql-server-find-duplicates-in-a-table-based-on-values-in-a-single-column/16525236#16525236 — EricZ, Sep 15 '15 at 15:50
Did you try anything? Can you _explain_ how to get from the input to the desired output? — HABO, Sep 15 '15 at 16:00

score 1 · Answer 1 · answered Sep 15 '15 at 15:52

Use Stuff function with XML Path to concat your rows:

Create Sample Table:

create table mytable (Base int, Altezza int,  Materiale varchar(50));

insert into mytable values
(10, 10, 'Ferro'),
(10, 10, 'Legno'),
(10, 30, 'Ferro'),
(10, 30, 'Legno'),
(10, 30, 'Acciaio'),
(20, 20, 'Legno');

Query:

select distinct base,
       altezza,     
       STUFF((Select ','+ materiale
               from mytable T1
             where T1.base=T2.base
               and t1.altezza = t2.altezza
             FOR XML PATH('')),1,1,'') as Materiale
from mytable T2;

Result:

+------+---------+---------------------+
| base | altezza |      Materiale      |
+------+---------+---------------------+
|   10 |      10 | Ferro,Legno         |
|   10 |      30 | Ferro,Legno,Acciaio |
|   20 |      20 | Legno               |
+------+---------+---------------------+

SQL Fiddle Demo

T-SQL - how to select rows distinct and move one row to column

1 Answers1