Consulta dinámica de PIVOT que devuelve una Tabla a través de una funcion
Pregunta
Respuestas
Todas las respuestas
-
Juan Carlos, al igual que Jesús no estoy de acuerdo en como has modelado tus tablas. Y básicamente el enredo lo veo cuando quieras saber el significado de un valor en una relación, haces confuso el modelo y las consultas se cargarán de JOIN's de manera innecesaria.
Estoy de acuerdo en el uso de estás tablas que contengan varios tipos, pero sin caer en el abuso. Por ejemplo, la tabla países no las contendría en esas tablas. Las uso básicamente para crear listas como sexo, días de la semana, etc. Pero sólo tengo 2 tablas: CabListas y DetListas, y como regla general que contengan la misma cantidad de columnas, que generalmente son Código, Descripción y Descripción Corta.
Aún así, si ya no puedes cambiar el modelo, no creo que sea una buena idea mostrar todos los atributos y valores en un sólo informe ya que como bien mencionas las cabeceras son distintas, no sólo en cantidad sino en descripción. Creo que a lo mucho que podrías llegar es a manejar el informe por tipo mostrando en la grilla sólo sus valores, ajustando de manera correcta para ello la cantidad de columnas y su descripción.
EDITO:
Finalmente, para mostrar lo que te menciono, en Perú tenemos el organismo SUNAT que nos proporciona "n" tablas con distintas columnas y significados, sin importar como es la estructura de nuestras tablas finalmente mostramos la información por tipo y construimos la cantidad de columnas y su descripción también por tipo, de la siguiente manera:
-----------------------------------------------------------------------------------------------------
Espero haberte ayudado con mi sugerencia, si resolvió tu problema no olvides marcarla como respuesta.
Willams Morales P.
Arequipa - Perú- Editado Willams Morales lunes, 29 de diciembre de 2014 16:03
-
Hola Williams,
Te paso la información, bueno en realidad no son 3 tablas sino son 5.
Creación de tablas e imagen de relaciones.
CREATE TABLE [dbo].[CAB_TIPOS_PLA]( [CTPL_Codigo] [smallint] IDENTITY(1,1) NOT NULL, [CTPL_Desc] [varchar](100) NOT NULL, [CTPL_Activo] [bit] NOT NULL, [CTPL_FecCrea] [smalldatetime] NOT NULL, [CTPL_UsrCrea] [varchar](20) NOT NULL, [CTPL_FecMod] [smalldatetime] NULL, [CTPL_UsrMod] [varchar](20) NULL, [CTPL_recursivo] [bit] NULL, [CTPL_codigo_padre] [smallint] NULL, CONSTRAINT [PK_CAB_TIPOS_PLA] PRIMARY KEY CLUSTERED ([CTPL_Codigo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[CAB_TIPOS_PLA] WITH CHECK ADD CONSTRAINT [FK_CAB_TIPOS_PLA_CAB_TIPOS_PLA] FOREIGN KEY([CTPL_codigo_padre]) REFERENCES [dbo].[CAB_TIPOS_PLA] ([CTPL_Codigo]) GO ALTER TABLE [dbo].[CAB_TIPOS_PLA] CHECK CONSTRAINT [FK_CAB_TIPOS_PLA_CAB_TIPOS_PLA] GO ALTER TABLE [dbo].[CAB_TIPOS_PLA] ADD CONSTRAINT [DF_CAB_TIPOS_PLA_CTVT_FecCrea] DEFAULT (getdate()) FOR [CTPL_FecCrea] GO CREATE TABLE [dbo].[ATRIBUTO_TIPOS]( [ATTI_Codigo] [smallint] IDENTITY(1,1) NOT NULL, [ATTI_Desc] [varchar](100) NOT NULL, [ATTI_Activo] [bit] NOT NULL, [ATTI_Dato] [tinyint] NOT NULL, [ATTI_FecCrea] [smalldatetime] NOT NULL, [ATTI_UsrCrea] [varchar](20) NOT NULL, [ATTI_FecMod] [smalldatetime] NULL, [ATTI_UsrMod] [varchar](20) NULL, [ATTI_Fijo] [bit] NULL, CONSTRAINT [PK_ATRIBUTO_TIPOS] PRIMARY KEY CLUSTERED ([ATTI_Codigo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[ATRIBUTO_TIPOS] ADD CONSTRAINT [DF_DET_TIPOS_VTA_ATTI_FecCrea] DEFAULT (getdate()) FOR [ATTI_FecCrea] GO CREATE TABLE [dbo].[DET_TIPOS_PLA_ATRIBUTO_TIPO]( [CTPL_codigo] [smallint] NOT NULL, [ATTI_Codigo] [smallint] NOT NULL, [DTAT_Obligatorio] [bit] NOT NULL, [DTAT_FecCrea] [smalldatetime] NOT NULL, [DTAT_UsrCrea] [varchar](20) NOT NULL, [DTAT_FecMod] [smalldatetime] NULL, [DTAT_UsrMod] [varchar](20) NULL, [DTAT_eliminado] [bit] NULL, [DTAT_CTPL_codigo] [smallint] NULL, [DTAT_Tabla] [varchar](50) NULL, CONSTRAINT [PK_DET_TIPOS_PLA_ATRIBUTO_TIPO] PRIMARY KEY CLUSTERED ([CTPL_codigo] ASC, [ATTI_Codigo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DET_TIPOS_PLA_ATRIBUTO_TIPO] WITH CHECK ADD CONSTRAINT [FK_DET_TIPOS_PLA_ATRIBUTO_TIPO_ATRIBUTO_TIPOS] FOREIGN KEY([ATTI_Codigo]) REFERENCES [dbo].[ATRIBUTO_TIPOS] ([ATTI_Codigo]) GO ALTER TABLE [dbo].[DET_TIPOS_PLA_ATRIBUTO_TIPO] CHECK CONSTRAINT [FK_DET_TIPOS_PLA_ATRIBUTO_TIPO_ATRIBUTO_TIPOS] GO ALTER TABLE [dbo].[DET_TIPOS_PLA_ATRIBUTO_TIPO] WITH CHECK ADD CONSTRAINT [FK_DET_TIPOS_PLA_ATRIBUTO_TIPO_CAB_TIPOS_PLA] FOREIGN KEY([CTPL_codigo]) REFERENCES [dbo].[CAB_TIPOS_PLA] ([CTPL_Codigo]) GO ALTER TABLE [dbo].[DET_TIPOS_PLA_ATRIBUTO_TIPO] CHECK CONSTRAINT [FK_DET_TIPOS_PLA_ATRIBUTO_TIPO_CAB_TIPOS_PLA] GO CREATE TABLE [dbo].[DET_TIPOS_PLA]( [DTPL_Codigo] [smallint] IDENTITY(1,1) NOT NULL, [CTPL_Codigo] [smallint] NOT NULL, [DTPL_Desc] [varchar](100) NOT NULL, [DTPL_Activo] [bit] NOT NULL, [DTPL_FecCrea] [smalldatetime] NOT NULL, [DTPL_UsrCrea] [varchar](20) NOT NULL, [DTPL_FecMod] [smalldatetime] NULL, [DTPL_UsrMod] [varchar](20) NULL, [DTPL_Codigo_Padre] [smallint] NULL, CONSTRAINT [PK_DET_TIPOS_PLA] PRIMARY KEY CLUSTERED ( [DTPL_Codigo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[DET_TIPOS_PLA] WITH CHECK ADD CONSTRAINT [FK_DET_TIPOS_PLA_CAB_TIPOS_PLA] FOREIGN KEY([CTPL_Codigo]) REFERENCES [dbo].[CAB_TIPOS_PLA] ([CTPL_Codigo]) GO ALTER TABLE [dbo].[DET_TIPOS_PLA] CHECK CONSTRAINT [FK_DET_TIPOS_PLA_CAB_TIPOS_PLA] GO ALTER TABLE [dbo].[DET_TIPOS_PLA] WITH CHECK ADD CONSTRAINT [FK_DET_TIPOS_PLA_DET_TIPOS_PLA] FOREIGN KEY([DTPL_Codigo_Padre]) REFERENCES [dbo].[DET_TIPOS_PLA] ([DTPL_Codigo]) GO ALTER TABLE [dbo].[DET_TIPOS_PLA] CHECK CONSTRAINT [FK_DET_TIPOS_PLA_DET_TIPOS_PLA] GO CREATE TABLE [dbo].[ATRIBUTO_TIPOS_DET_TIPOS_PLA]( [ATDT_Codigo] [smallint] IDENTITY(1,1) NOT NULL, [DTPL_Codigo] [smallint] NOT NULL, [CTPL_Codigo] [smallint] NOT NULL, [ATTI_Codigo] [smallint] NOT NULL, [ATDT_Numerico] [decimal](12, 2) NULL, [ATDT_Texto] [varchar](max) NULL, [ATDT_Fecha] [smalldatetime] NULL, [ATDT_Tipo] [smallint] NULL, [ATDT_FecCrea] [smalldatetime] NOT NULL, [ATDT_UsrCrea] [varchar](20) NOT NULL, [ATDT_FecMod] [smalldatetime] NULL, [ATDT_UsrMod] [varchar](20) NULL, CONSTRAINT [PK_ATRIBUTO_TIPOS_DET_TIPOS_PLA_1] PRIMARY KEY CLUSTERED ( [ATDT_Codigo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[ATRIBUTO_TIPOS_DET_TIPOS_PLA] WITH CHECK ADD CONSTRAINT [FK_ATRIBUTO_TIPOS_DET_TIPOS_PLA_DET_TIPOS_PLA] FOREIGN KEY([DTPL_Codigo]) REFERENCES [dbo].[DET_TIPOS_PLA] ([DTPL_Codigo]) GO ALTER TABLE [dbo].[ATRIBUTO_TIPOS_DET_TIPOS_PLA] CHECK CONSTRAINT [FK_ATRIBUTO_TIPOS_DET_TIPOS_PLA_DET_TIPOS_PLA] GO ALTER TABLE [dbo].[ATRIBUTO_TIPOS_DET_TIPOS_PLA] WITH CHECK ADD CONSTRAINT [FK_ATRIBUTO_TIPOS_DET_TIPOS_PLA_DET_TIPOS_PLA_ATRIBUTO_TIPO] FOREIGN KEY([CTPL_Codigo], [ATTI_Codigo]) REFERENCES [dbo].[DET_TIPOS_PLA_ATRIBUTO_TIPO] ([CTPL_codigo], [ATTI_Codigo]) GO ALTER TABLE [dbo].[ATRIBUTO_TIPOS_DET_TIPOS_PLA] CHECK CONSTRAINT [FK_ATRIBUTO_TIPOS_DET_TIPOS_PLA_DET_TIPOS_PLA_ATRIBUTO_TIPO] GO
-
Juan Carlos,
Tengo el código de pivote dinámico de múltiple columnas del lado del cliente listo en Visual Basic 6.0
Data La siguiente consulta llamada "ProductSale_Year" en la base de datos de ejemplo NWind.mdb:
SELECT P.ProductID, P.ProductName, DatePart('yyyy',Orders.OrderDate) AS [Year], Round( Sum(OD.Quantity*OD.UnitPrice*(1-OD.Discount)), 2) AS Sales, Count(*) AS Orders FROM Products AS P LEFT JOIN ([Order Details] AS OD LEFT JOIN Orders ON OD.OrderID = Orders.OrderID) ON P.ProductID = OD.ProductID GROUP BY P.ProductID, P.ProductName, DatePart('yyyy',Orders.OrderDate);
Y el siguiente código VB 6.0 en un formulario:
Dim WithEvents SalesPivotTransform As PivotTransform Dim WithEvents YearPivotTransform As PivotTransform Private Sub Form_Load() Dim UnpivotedColumnNames() As String Dim pvTransforms() As PivotTransform ReDim UnpivotedColumnNames(0 To 1) ReDim pvTransforms(0 To 1) Dim cn As New ADODB.Connection cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Projects\PivotProject\Nwind.mdb" cn.CursorLocation = adUseClient cn.Open Dim cmd As New ADODB.Command Set cmd.ActiveConnection = cn cmd.CommandText = "SELECT * FROM ProductSale_Year ORDER BY ProductName, ProductID, Year" Dim rst As New ADODB.Recordset Set rst.source = cmd rst.Open cmd, , adOpenStatic, adLockReadOnly Set rst.ActiveConnection = Nothing cn.Close Set SalesPivotTransform = New PivotTransform SalesPivotTransform.PivotColumnName = "Year" SalesPivotTransform.ValueColumnName = "Sales" Set YearPivotTransform = New PivotTransform YearPivotTransform.PivotColumnName = "Year" YearPivotTransform.ValueColumnName = "Orders" Set pvTransforms(0) = SalesPivotTransform Set pvTransforms(1) = YearPivotTransform UnpivotedColumnNames(0) = "ProductName" UnpivotedColumnNames(1) = "ProductID" Dim pvDef As New PivotDef pvDef.PivotTransforms = pvTransforms pvDef.UnpivotedColumnNames = UnpivotedColumnNames Dim builder As New PivotBuilder Dim pvrst As ADODB.Recordset Set pvrst = builder.Build(pvDef, rst) Set Me.DataGrid1.DataSource = pvrst End Sub Private Sub SalesPivotTransform_PivotedColumnNameRequested(e As PCNRequestedEventArgs) e.PivotColumnName = "Sales" & CStr(e.PivotColumnValue) End Sub Private Sub YearPivotTransform_PivotedColumnNameRequested(e As PCNRequestedEventArgs) e.PivotColumnName = "Orders" & CStr(e.PivotColumnValue) End Sub
Esto es lo que sale:
Y te aseguro que va como un tiro de rápido.
¿Te interesa o no?
Jesús López
EntityLite a lightweight, database first, micro orm
