pass rows to columns

Question

pass rows to columns

Navigation

#1 by (0 votes)

0

I wanted to see if you can help me with this.

My query is as follows:

   select 
   O.idgoOperacion [Operacion]
  ,uuu.Etiqueta
  ,uuu.Valor
   from Operacion O

docDocumentoArchivo Doa on doc.iddocDocumento = Doa.iddocDocumento [dbo] .ObtenerDatosPropios uuu (This function hits a web service)

The query returns what is in the image

I need to convert the labels into columns .

I tried to do with pivot but the truth is that it did not work for me. If someone can guide me ... I appreciate it!

sql pivot

asked by Jacqueline Stella 11.01.2018 в 20:39

source

1 answer

How to separate records from a query through ResultSet How to take the name of the elements of a folder in an orderly way JFileChooser

score 0 · Answer 1

If the value of the labels you already have it defined, you could do:

SELECT *
FROM
(
     SELECT ID, NOM_ETIQUETA, VALOR
     FROM ETIQUETA
) AS SourceTable
PIVOT
(    
     MAX(VALOR)
     FOR NOM_ETIQUETA IN ([A],[B],[C],[D],[E])
) AS PivotTable;

If the tags are dynamic, you might consider, for example:

CREATE TABLE ETIQUETA (
    ID INT,
    NOM_ETIQUETA VARCHAR(50),
    VALOR VARCHAR(10)
)

INSERT INTO ETIQUETA VALUES (1, 'A', 'VALOR A'), (1, 'B', 'VALOR B'), (1, 'C', 'VALOR C'), (1, 'D', 'VALOR D'), (1, 'E', 'VALOR E')

DECLARE @COLUMNAS VARCHAR(MAX), @SCRIPT VARCHAR(MAX)

SELECT @COLUMNAS = COALESCE(@COLUMNAS + ',', '') + '[' + NOM_ETIQUETA + ']'  FROM ETIQUETA

SELECT @SCRIPT = '
SELECT *
FROM
(
     SELECT ID, NOM_ETIQUETA, VALOR
     FROM ETIQUETA
) AS SourceTable
PIVOT
(    
     MAX(VALOR)
     FOR NOM_ETIQUETA IN (' + @COLUMNAS + ')
) AS PivotTable;'

EXEC(@SCRIPT);

You would get:

+----+---------+---------+---------+---------+---------+
| ID |    A    |    B    |    C    |    D    |    E    |
+----+---------+---------+---------+---------+---------+
|  1 | VALOR A | VALOR B | VALOR C | VALOR D | VALOR E |
+----+---------+---------+---------+---------+---------+

DEMO