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

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
– AdamMc331, Commented Oct 1, 2014 at 15:25
Take a look at a Pivot example for transposing rows into columns: technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — rfolt
– rfolt, Commented Oct 1, 2014 at 15:27
possible duplicate of How to make a query with group_concat in sql server — Horaciux
– Horaciux, Commented Oct 1, 2014 at 15:36

jim31415 · Accepted Answer · 2014-10-01 18:31:42Z

1

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

answered Oct 1, 2014 at 18:31

jim31415

8,8186 gold badges45 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL How to display row on column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related