SQL How to display row on column

Question

I have this table

CREATE TABLE [dbo].[MONITOR.Dati_Attivita]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [IDEsecuzione] [int] NOT NULL,
    [IDIndice] [int] NOT NULL,
    [Valore] [nvarchar](100) NOT NULL,
    [IDRipetizione] [int] NOT NULL,

    CONSTRAINT [PK_Monitor_Dati_Attivita] 
      PRIMARY KEY CLUSTERED ([ID] ASC)
) ON [PRIMARY]

So I would like to SELECT the result set,

I try this:

SELECT IDEsecuzione, Valore, IDRipetizioe

The result is

1 -  100  - 1
1 - 'abc' - 1
1 - 'VVV' - 1
2 - 'aaa' - 1
2 - 'bbb' - 1
2 - 'ccc' - 1

But I would like to display result set like this:

1 - 100 - abc - VVV - 1
2 - aaa- bbb - ccc - 1

Can you help me?

Regards

possible duplicate of [Transpose SQL result set](http://stackoverflow.com/questions/18765578/transpose-sql-result-set) — Himanshu Tyagi, Oct 01 '14 at 15:23
I didn't think SQL supports such a thing, but @NoComments may have a good point. Are you using this inside of an application? If you are, you can use the regular select statement and format the results the way you'd like inside the application. — AdamMc331, Oct 01 '14 at 15:25
http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql — Himanshu Tyagi, Oct 01 '14 at 15:27
Take a look at a Pivot example for transposing rows into columns: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — rfolt, Oct 01 '14 at 15:27
possible duplicate of [How to make a query with group\_concat in sql server](http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server) — Horaciux, Oct 01 '14 at 15:36
I think is not about transpose rows into columns (pivot) it is rows into one column with hyphen separated values http://stackoverflow.com/questions/17591490/how-to-make-a-query-with-group-concat-in-sql-server — Horaciux, Oct 01 '14 at 15:36
Do you want that data concatenated into a single row/column - or do you want the data converted from multiple rows into multiple columns? It's not exactly clear what you are trying to do. — Taryn, Oct 01 '14 at 15:39

score 1 · Accepted Answer · answered Oct 01 '14 at 18:31

To concatenate a column over rows, use the FOR XML PATH method.

    SELECT DISTINCT
        IDEsecuzione,
        Valore = SUBSTRING((SELECT ' - ' + t1.Valore 
                            FROM MONITOR.Dati_Attivita t1 
                            WHERE t1.IDEsecuzione = t2.IDEsecuzione 
                            FOR XML PATH('')), 4, 9999),
        IDRipetizione
    FROM MONITOR.Dati_Attivita t2

SQL How to display row on column

1 Answers1