How to transform a column in Column Header in SQL?

Question

How to transform a column in Column Header in SQL?

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

What happens is that I need to transform a column into the head of the other columns, I do not know if I explain.

Here something more detailed:

I have this query which shows me the description column which I would like to be the header of the columns.

select cantidad,Punto1, Punto2,Punto3,Punto4,TallasDetalle.IdTalla,TallasDetalle.Descripcion
From tblProductoPedido join TallasDetalle
on tblProductoPedido.IdTalla = TallasDetalle.IdTalla
where Codigo = '900121220066'

I managed to do one I show the following query:

Select * From(
 select cantidad,Punto1, Punto2,Punto3,Punto4,TallasDetalle.IdTalla,TallasDetalle.Descripcion
 From tblProductoPedido join TallasDetalle
 on tblProductoPedido.IdTalla = TallasDetalle.IdTalla
 where Codigo = '900121220066') as a Pivot(AVG(Punto1) For [Descripcion] in ([25]))   as TablaPivote

and so it looks:

Now I do not know how to do so that others can also be replaced and appear for example:

Please, if someone could help me or support me, I would appreciate it.

sql-server

asked by Ezequie Lopez 21.06.2018 в 21:10

source

2 answers

Problem to change values in a column in dataFrame in R Problem with SQL statement when wanting to group (Group By)

score 1 · Answer 1

1

You need to use pivot . The structure of a pivot is:

'PIVOT (funcionDeAgregado(campoDeAgregado)FOR ColumnaPivote IN (valoresDeColumnaPivote)) AS Alias;'

In msdn you can find examples even for dynamic pivot MSDN

answered by 21.06.2018 в 21:27

score 0 · Answer 2

To your serious concern to offer support, show the implementation of a dynamic pivot as enunciated in the following example:

Having the following initial input tables:

The idea is to generate a process so that in the case of the example the dates become the columns of our report, for which with the following storeProcedure the sig would be generated. logic:

USE [tuEsquema]
GO

/****** Object:  StoredProcedure [dbo].[prcPivotDinamic]    Script Date: 6/21/2018 2:32:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[prcPivotDinamic]
    -- Add the parameters for the stored procedure here
    @fchIni VARCHAR(255),
    @fchFin VARCHAR(255),
    @identificacion VARCHAR(255)    
AS
BEGIN

    SET NOCOUNT ON;

    DECLARE @periodos AS NVARCHAR(MAX)  
    DECLARE @PivotDynamicEsp NVARCHAR(MAX)  
    DECLARE @PivotDynamicGnral NVARCHAR(MAX)    
    DECLARE @dropViewGnral NVARCHAR(MAX)    
    DECLARE @dropViewEsp NVARCHAR(MAX)  

    SELECT @periodos = COALESCE(@periodos + ', ', '')+ Quotename(Fecha)
                        FROM 
                        (
                            SELECT DISTINCT Fecha  
                            FROM consultHistoricoEstGnral  
                            WHERE Fecha BETWEEN @fchIni AND @fchFin
                            GROUP BY Fecha
                        ) AS periodos  

        --print 'periodos: '+@periodos;         

         SET @dropViewGnral = 'DROP VIEW MyPivotDynamicGnral'+@identificacion

         --print @dropViewGnral

            if object_id('MyPivotDynamicGnral'+@identificacion,'v') is not null
                EXEC sp_executesql @dropViewGnral

        SET @dropViewEsp = 'DROP VIEW MyPivotDynamicEsp'+@identificacion

            if object_id('MyPivotDynamicEsp'+@identificacion,'v') is not null
                EXEC sp_executesql @dropViewEsp

            SET @PivotDynamicEsp = ' CREATE VIEW MyPivotDynamicEsp'+@identificacion+' AS
            SELECT              
                EstadoEspecifico,
                '+@periodos+'               
            FROM
                (
                    SELECT 
                        CantidadEspecifica,
                        EstadoEspecifico,
                        Fecha
                    FROM consultHistoricoEstEsp
                ) AS SourceTable
            PIVOT
                (
                SUM(CantidadEspecifica)
                FOR Fecha IN ('+@periodos+')
                ) AS PivotTable;
            '
            SET @PivotDynamicGnral = 'CREATE VIEW MyPivotDynamicGnral'+@identificacion+' AS
            SELECT 
                EstadoGeneral,          
                '+@periodos+'               
            FROM
                (
                    SELECT 
                        CantidadGeneral,
                        EstadoGeneral,
                        Fecha
                    FROM consultHistoricoEstGnral
                ) AS SourceTable
            PIVOT
                (
                SUM(CantidadGeneral)
                FOR Fecha IN ('+@periodos+')
                ) AS PivotTable;'

            --print @PivotDynamicGnral

            IF(@periodos <> '')
                BEGIN                   
                    EXEC sp_executesql @PivotDynamicEsp                 
                    EXEC sp_executesql @PivotDynamicGnral
                    print 'done'
                END
            ELSE
                PRINT 'notRow'


END
GO

Now, when the call is made to the store procedure, the result in the constructed views is the following:

In the example I show how to implement a dynamic pivot based on what are two initial input tables for some requested reports. I hope you find this example of use and implementation useful, greetings.