none
Convertir Filas en Columnas RRS feed

  • Pregunta

  • Estimados,

    Mi pregunta es como mostrar ciertas filas de una consulta SQL a manera de columnas, es decir lograr lo siguiente:

    Este es el resultado de la consulta incial:

    Esto es lo que quiero lograr:

    Agradecería vuestra ayuda,

    Hector

    PD: Estoy utilizando SQL Server 2008

    viernes, 21 de enero de 2011 15:00

Respuestas

  • Hector,

    Debido a que el número de preguntas / respuestas por encuentas y usuario no se sabe a priori, entonces tendrias que optar por usar pivot dinamico (pivot y sql dinamico).

    create procedure ew_Respuesta_GetAll
    as
    SELECT
     R.RespuestaID,
     R.NombreCliente,
     R.Telefono1,
     R.Telefono2,
     T.TiendaNombre,
     U.NombreUsuario,
     R.Observaciones,
     R.FechaRegistro, 
     E.NombreEncuesta,
     P.PreguntaID,
     P.NombrePregunta,
     A.AlternativaNombre
    INTO
     #T
    from 
     ew_Respuesta R
     inner join ew_Tienda T on R.TiendaID=T.TiendaID
     inner join ew_Usuario U on R.UsuarioID=U.UsuarioID
     inner join ew_RespuestaDetalle RD on R.RespuestaID=RD.RespuestaID
     inner join ew_Encuesta E on R.EncuestaID=E.EncuestaID
     inner join ew_Pregunta P on RD.PreguntaID=P.PreguntaID
     inner join ew_Alternativa A on RD.AlternativaID=A.AlternativaID
    
    DECLARE @columns nvarchar(MAX);
    DECLARE @sql nvarchar(MAX);
    
    SET @columns = STUFF(
    (
    SELECT
      ',' + QUOTENAME('Pregunta' + LTRIM(PreguntaID))
    FROM
      (SELECT DISTINCT PreguntaID FROM #T) AS T
    ORDER BY
     PreguntaID
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @sql = N'
    SELECT
     *
    FROM
     (
     SELECT
      RespuestaID,
      NombreCliente,
      Telefono1,
      Telefono2,
      TiendaNombre,
      NombreUsuario,
      Observaciones,
      FechaRegistro, 
      NombreEncuesta,
      ''Pregunta'' + LTRIM(PreguntaID) AS Pregunta,
      AlternativaNombre
     FROM
      #T
     ) AS T
     PIVOT
     (
     MAX(AlternativaNombre)
     FOR Pregunta IN (' + @columns + N')
     ) AS P;';
    
    BEGIN TRY
     EXEC sp_executesql @sql;
    END TRY
    BEGIN CATCH
     SELECT 
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_LINE () as ErrorLine,
      ERROR_PROCEDURE() as ErrorProcedure,
      ERROR_MESSAGE() as ErrorMessage;
    END CATCH;
    GO
    Mucho cuidado con el uso de sql dinamico, pues es una de las causas fundamentales de la injeccion de codigo sql.

     


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta Hextor199 viernes, 21 de enero de 2011 20:39
    • Editado HunchbackMVP sábado, 22 de enero de 2011 3:04
    viernes, 21 de enero de 2011 19:05

Todas las respuestas

  • Te sugiero que postees la estructura de la tabla, asi como data de ejemplo en forma de sentencias "insert", y los resultados esperados. De esta forma no tendremos que invertir tiempo en recrear tu pregunta.

    Ejemplo:

    create table T(
    id int not null identity(1, 1) primary key,
    nombrecliente varchar(50),
    ...
    );

    insert into T(nombrecliente, ...)
    values (...)

    Mientras tanto, puedes hecharle un vistazo a este hilo y/o buscar en los BOL por el operador PIVOT.

    Referencias Cruzadas

     


    AMB

    Some guidelines for posting questions...

    viernes, 21 de enero de 2011 15:11
  • Buenas tades Hunchback:

    He visto como trabaja la cláusula "PIVOT" pero no encuentro la manera de aplicarlo a mi caso, de todas formas te dejo la estructura de mi BB.DD a ver si me puedes hechar una mano.

    create database EncuestaWeb
    GO
    use EncuestaWeb
    GO

    create table ew_Tienda(
    TiendaID int identity(1,1) primary key not null,
    TiendaNombre varchar(50) not null,
    FechaRegistro datetime default getdate() null,
    Estado bit default 1 null)
    GO

    insert into ew_Tienda(TiendaNombre) values('Centro Comercial Arenales')
    insert into ew_Tienda(TiendaNombre) values('Centro Comercial CyberPlaza')
    insert into ew_Tienda(TiendaNombre) values('Hipermercados Wong')
    GO

    create table ew_Encuesta(
    EncuestaID int identity(1,1) primary key not null,
    NombreEncuesta varchar(100) not null,
    FechaRegistro datetime default getdate() null,
    Estado bit default 1 null)
    GO

    insert into ew_Encuesta(NombreEncuesta) values('OFERTA 1')
    insert into ew_Encuesta(NombreEncuesta) values('OFERTA 2')
    select * from ew_Encuesta
    GO

    create table ew_Pregunta(
    PreguntaID int identity(1,1) primary key not null,
    NombrePregunta varchar(100) not null,
    FechaRegistro datetime default getdate() null,
    Estado bit default 1 null)
    GO

    insert into ew_Pregunta(NombrePregunta) values('Ofrecido Trío')
    insert into ew_Pregunta(NombrePregunta) values('¿Contrata Trío?')
    insert into ew_Pregunta(NombrePregunta) values('¿Cual es el motivo por el que rechaza el Trío?')
    insert into ew_Pregunta(NombrePregunta) values('¿Contrata Dúo?')
    select * from ew_Pregunta
    GO

    create table ew_EncuestaPregunta(
    EncuestaID int null foreign key references ew_Encuesta(EncuestaID),
    PreguntaID int null foreign key references ew_Pregunta(PreguntaID))
    GO

    insert into ew_EncuestaPregunta values(1,1)
    insert into ew_EncuestaPregunta values(1,2)
    insert into ew_EncuestaPregunta values(1,3)
    insert into ew_EncuestaPregunta values(1,4)
    select * from ew_EncuestaPregunta
    GO

    create table ew_Alternativa(
    AlternativaID int identity(1,1) primary key not null,
    AlternativaNombre varchar(100) not null,
    FechaRegistro datetime default getdate() null,
    Estado bit default 1 null)
    GO

    insert into ew_Alternativa(AlternativaNombre) values('Si')
    insert into ew_Alternativa(AlternativaNombre) values('No')
    insert into ew_Alternativa(AlternativaNombre) values('Rechaza Trío porque prefiere solo Internet)')
    insert into ew_Alternativa(AlternativaNombre) values('Rechaza Trío porque prefiere solo Televisión)')
    insert into ew_Alternativa(AlternativaNombre) values('Rechaza Trío y no le interesa nada')
    select * from ew_Alternativa
    GO

    create table ew_PreguntaAlternativa(
    PreguntaID int foreign key references ew_Pregunta(PreguntaID) not null,
    AlternativaID int foreign key references ew_Alternativa(AlternativaID) not null,
    FechaRegistro datetime default getdate() null,
    Estado bit default 1 null)
    GO

    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(1,1)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(1,2)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(2,1)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(2,2)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(3,3)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(3,4)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(3,5)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(4,1)
    insert into ew_PreguntaAlternativa(PreguntaID,AlternativaID) values(4,2)
    select * from ew_PreguntaAlternativa
    GO

    create table ew_Usuario(
    UsuarioID int identity(1,1) primary key not null,
    NombreUsuario varchar(50) not null,
    Login varchar(20) not null,
    Clave varchar(15) not null,
    FechaRegistro datetime default getdate() null,
    Estado bit default 1 null)
    GO

    insert into ew_Usuario(NombreUsuario,Login,Clave) values('Hector','hextor','123456')
    select * from ew_Usuario
    GO

    create table ew_Respuesta(
    RespuestaID int identity(1,1) primary key not null,
    NombreCliente varchar(100) null,
    Telefono1 varchar(9) null,
    Telefono2 varchar(9) null,
    EncuestaID int foreign key references ew_Encuesta(EncuestaID) not null,
    TiendaID int foreign key references ew_Tienda(TiendaID) not null,
    UsuarioID int foreign key references ew_Usuario(UsuarioID) not null,
    Observaciones varchar(2000) null,
    FechaRegistro datetime default getdate() null)
    GO

    create table ew_RespuestaDetalle(
    PreguntaID int foreign key references ew_Pregunta(PreguntaID) not null,
    RespuestaID int foreign key references ew_Respuesta(RespuestaID) not null,
    AlternativaID int foreign key references ew_Alternativa(AlternativaID) not null)
    GO

    insert into ew_Respuesta(NombreCliente,Telefono1,Telefono2,EncuestaID,TiendaID,UsuarioID,Observaciones) values('Irene','999999999','888888888',1,2,1,'Ninguna observación')
    insert into ew_RespuestaDetalle(PreguntaID,RespuestaID,AlternativaID) values(1,1,1)    --Ofrecido Trío / Si
    insert into ew_RespuestaDetalle(PreguntaID,RespuestaID,AlternativaID) values(2,1,2)    --¿Contrata Trío? / No
    insert into ew_RespuestaDetalle(PreguntaID,RespuestaID,AlternativaID) values(3,1,3)    --¿Cual es el motivo por el que rechaza el Trío? / Rechaza Trío porque prefiere solo Internet)
    insert into ew_RespuestaDetalle(PreguntaID,RespuestaID,AlternativaID) values(4,1,2)    --¿Contrata Dúo? / No
    GO

    create procedure ew_Respuesta_GetAll
    as
    select R.RespuestaID,R.NombreCliente,R.Telefono1,R.Telefono2,T.TiendaNombre,U.NombreUsuario,
        R.Observaciones,R.FechaRegistro, E.NombreEncuesta,P.NombrePregunta,A.AlternativaNombre from ew_Respuesta R
        inner join ew_Tienda T on R.TiendaID=T.TiendaID
        inner join ew_Usuario U on R.UsuarioID=U.UsuarioID
        inner join ew_RespuestaDetalle RD on R.RespuestaID=RD.RespuestaID
        inner join ew_Encuesta E on R.EncuestaID=E.EncuestaID
        inner join ew_Pregunta P on RD.PreguntaID=P.PreguntaID
        inner join ew_Alternativa A on RD.AlternativaID=A.AlternativaID
    GO

    exec ew_Respuesta_GetAll

    Saludos,

    Hector.
    PD: Esta base la modelé muy a la ligera y de seguro que se puede mejorar =)

    viernes, 21 de enero de 2011 16:54
  • Hector,

    Debido a que el número de preguntas / respuestas por encuentas y usuario no se sabe a priori, entonces tendrias que optar por usar pivot dinamico (pivot y sql dinamico).

    create procedure ew_Respuesta_GetAll
    as
    SELECT
     R.RespuestaID,
     R.NombreCliente,
     R.Telefono1,
     R.Telefono2,
     T.TiendaNombre,
     U.NombreUsuario,
     R.Observaciones,
     R.FechaRegistro, 
     E.NombreEncuesta,
     P.PreguntaID,
     P.NombrePregunta,
     A.AlternativaNombre
    INTO
     #T
    from 
     ew_Respuesta R
     inner join ew_Tienda T on R.TiendaID=T.TiendaID
     inner join ew_Usuario U on R.UsuarioID=U.UsuarioID
     inner join ew_RespuestaDetalle RD on R.RespuestaID=RD.RespuestaID
     inner join ew_Encuesta E on R.EncuestaID=E.EncuestaID
     inner join ew_Pregunta P on RD.PreguntaID=P.PreguntaID
     inner join ew_Alternativa A on RD.AlternativaID=A.AlternativaID
    
    DECLARE @columns nvarchar(MAX);
    DECLARE @sql nvarchar(MAX);
    
    SET @columns = STUFF(
    (
    SELECT
      ',' + QUOTENAME('Pregunta' + LTRIM(PreguntaID))
    FROM
      (SELECT DISTINCT PreguntaID FROM #T) AS T
    ORDER BY
     PreguntaID
    FOR XML PATH('')
    ), 1, 1, '');
    
    SET @sql = N'
    SELECT
     *
    FROM
     (
     SELECT
      RespuestaID,
      NombreCliente,
      Telefono1,
      Telefono2,
      TiendaNombre,
      NombreUsuario,
      Observaciones,
      FechaRegistro, 
      NombreEncuesta,
      ''Pregunta'' + LTRIM(PreguntaID) AS Pregunta,
      AlternativaNombre
     FROM
      #T
     ) AS T
     PIVOT
     (
     MAX(AlternativaNombre)
     FOR Pregunta IN (' + @columns + N')
     ) AS P;';
    
    BEGIN TRY
     EXEC sp_executesql @sql;
    END TRY
    BEGIN CATCH
     SELECT 
      ERROR_NUMBER() AS ErrorNumber,
      ERROR_SEVERITY() AS ErrorSeverity,
      ERROR_STATE() as ErrorState,
      ERROR_LINE () as ErrorLine,
      ERROR_PROCEDURE() as ErrorProcedure,
      ERROR_MESSAGE() as ErrorMessage;
    END CATCH;
    GO
    Mucho cuidado con el uso de sql dinamico, pues es una de las causas fundamentales de la injeccion de codigo sql.

     


    AMB

    Some guidelines for posting questions...

    • Marcado como respuesta Hextor199 viernes, 21 de enero de 2011 20:39
    • Editado HunchbackMVP sábado, 22 de enero de 2011 3:04
    viernes, 21 de enero de 2011 19:05
  • He quedado sin palabras... funciona perfecto!, sin duda eres un maestro! muchas gracias Hunchback, de veras tu actitud me inspira =)

    Un fuerte abrazo maestro!

    Hector.

    viernes, 21 de enero de 2011 20:39
  • Estimado le realizo la siguiente consulta tengo que realizar un pivot con la siguiente información : 

    y de dejarlo de forma dinámica a columnas según el nombre y su debido valor si fueras tan amable de indicarme cual el el paso 

    

    la query es :

    SELECT  GRL_DC.Nombre_Dctos, GRL_MVLD.Monto_Dctos
    FROM         dbo.GRL_FuncionariosAreas AS GRL_FA INNER JOIN
                          dbo.GRL_MovimientoLiquidacionDescuentos AS GRL_MVLD ON GRL_MVLD.Codigo_Area = GRL_FA.Codigo_Area AND 
                          GRL_MVLD.Rut = GRL_FA.Rut AND GRL_MVLD.Mes = GRL_FA.Mes AND GRL_MVLD.Ano = GRL_FA.Ano INNER JOIN
                          dbo.GRL_Descuentos AS GRL_DC ON GRL_MVLD.Codigo_Area = GRL_DC.Codigo_Area AND GRL_MVLD.Mes = GRL_DC.Mes AND 
                          GRL_MVLD.Ano = GRL_DC.Ano AND GRL_MVLD.Codigo_Dctos = GRL_DC.Codigo_Dctos

    espero tus alcances de este problemas

    viernes, 16 de marzo de 2012 21:22