none
Crear una tabla temporal con campos variables RRS feed

  • Pregunta

  • Que tal?

    Estoy haciendo una consulta, en la cual, quiero crear una tabla temporal pero el numero de columnas depende de los datos de los registros (una variable), por ejemplo:

    Si la variable es 5, entonces la tabla sera:

    Create Table #Temporal1 (Uno Integer, Dos Integer, Tres Integer, Cuatro Integer, Cinco Integer)

     

    Si la variable es 3 entonces:

    Create Table #Temporal1 (Uno Integer, Dos Integer, Tres Integer)

     

    Como poder hacer un ciclo? para hacer asi las tablas?

     

    Gracias!!!

     

    jueves, 3 de noviembre de 2011 16:33

Respuestas

  • Buenas,
    Aqui te dejo un script de ejemplo que creo que reproduce tu problema. Utilizando PIVOT pero dinámicamente.
    --creacion de la tabla
    create table #ventas
    (
    	Nombre varchar(100),
    	Anyo int,
    	cantidadVenta int
    )
    
    --inserto datos de prueba
    insert into #ventas
    values
    ('Adolfo',2012,100),
    ('Adolfo',2013,250),
    ('Adolfo',2014,200),
    ('Adolfo',2015,300),
    ('Pedro',2012,300),
    ('Pedro',2013,100)
    
    --defino año minimo y maximo
    declare @anyomax int, @anyomin int
    declare @variable varchar(max)
    declare @sql varchar(max)
    
    set @variable=''
    set @sql=''
    
    --establezco el año minimo y maximo
    select @anyomax=Max(Anyo), @anyoMin=Min(Anyo) from #ventas
    
    --genero dinamicamente los años
    while(@anyomin<=@anyomax)
    begin
    
    	if @anyomin=@anyomax
    	begin
    		set @variable=@variable+'['+cast(@anyomin as varchar(10))+']'
    	end
    	else
    	begin
    		set @variable=@variable+'['+cast(@anyomin as varchar(10))+'],'
    	end
    
    	set @anyomin=@anyomin+1
    end
    
    --ejecuto la sentencia con PIVOT
    set @sql='
    select Nombre,'+@variable+'
    from
    (
    	select Nombre, Anyo,CantidadVenta
    	from #ventas
    ) as p
    pivot
    (
    	SUM(CantidadVenta)
    	for Anyo in ('+@variable+')
    ) as Pvt'
    
    exec(@sql)
    
    drop table #ventas
    

    El resultado que obtenemos de este script es el siguiente:
    Si modificamos los datos de la tablacomo sigue:
    insert into #ventas
    values
    ('Adolfo',2012,100),
    ('Adolfo',2013,250),
    ('Adolfo',2014,200),
    ('Adolfo',2015,300),
    ('Pedro',2012,300),
    ('Pedro',2013,100),
    ('Pedro',2018,300),
    ('Adolfo',2019,100)
    

    Ahora el resultado cambia al siguiente:
    Como ves, se han añadido los años dinámicamente.
    Espero que te sirva de ayuda :)
    Un Saludo.

    Entre todos aprendemos mas :) Blog personal: http://blogs.solidq.com/EnriquePuigNouselles/Home.aspx El rincon del DBA: http://blogs.solidq.com/ElRinconDelDBA/Home.aspx La Biblioteca de PowerPivot http://blogs.solidq.com/powerpivot/Home.aspx
    jueves, 3 de noviembre de 2011 18:07
  • Concuerdo con Alberto; mientras tanto, tal vez este código te ayude a obtener por lo menos la sentencia T-SQL:

    USE AdventureWorks2008R2
    GO

    DECLARE @numCampos INT
    SET @numCampos = 8
    DECLARE @contador INT
    SET @contador = 1
    DECLARE @strSQL varchar(256)
    SET @strSQL = 'CREATE TABLE #Test ('
    DECLARE @strCampo varchar(256)

    WHILE (@contador <= @numCampos)
    BEGIN
     SET @strCampo = 'Campo' + CONVERT(varchar(256),@contador)
     IF (@contador = @numCampos)
      SET @strSQL = @strSQL + @strCampo + ' INT'
     ELSE
      SET @strSQL = @strSQL + @strCampo + ' INT, '
     SET @contador = @contador + 1
    END

    SET @strSQL = @strSQL + ')'
    GO

    Saludos,

     

     


    Guillermo Taylor F.

    IT Pro & Xbox gamer

    My blog

    • Marcado como respuesta Adolfo 71 viernes, 4 de noviembre de 2011 21:51
    jueves, 3 de noviembre de 2011 16:52
  • Para ese escenario que planteas veo mas apropiado que uses PIVOT, calcular con cursores y tablas temporales esas columnas lo veo muy pesado.  Aqui un articulo que siempre sugiero como punto de referencia, es del colega Salvador Ramos.

    http://www.elguille.info/NET/ADONET/firmas_salva_Pivot_Unpivot.htm


    "El talento es una disciplina tenaz y una larga paciencia"  Gustave Flaubert

     Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

     

    • Marcado como respuesta Adolfo 71 viernes, 4 de noviembre de 2011 21:51
    jueves, 3 de noviembre de 2011 17:18

Todas las respuestas

  • Hola.

    La única forma es con sql dinámico (construir la sentencia en tiempo de ejecución y luego lanzarla). También puedes dimensionar tu tabla con suficientes campos, aunque luego sólo rellenarás unos pocos.

    Sería interesante conocer el requerimiento funcional que tienes, es posible que exista algún otro mecanismo que te permita lograr lo mismo sin este rodeo de la tabla temporal de columnas variables.


    Alberto López Grande
    SQL Server MVP
    Visita mi blog en http://qwalgrande.blogspot.es/ Sígueme en twitter en http://twitter.com/qwalgrande

    jueves, 3 de noviembre de 2011 16:45
    Moderador
  • Concuerdo con Alberto; mientras tanto, tal vez este código te ayude a obtener por lo menos la sentencia T-SQL:

    USE AdventureWorks2008R2
    GO

    DECLARE @numCampos INT
    SET @numCampos = 8
    DECLARE @contador INT
    SET @contador = 1
    DECLARE @strSQL varchar(256)
    SET @strSQL = 'CREATE TABLE #Test ('
    DECLARE @strCampo varchar(256)

    WHILE (@contador <= @numCampos)
    BEGIN
     SET @strCampo = 'Campo' + CONVERT(varchar(256),@contador)
     IF (@contador = @numCampos)
      SET @strSQL = @strSQL + @strCampo + ' INT'
     ELSE
      SET @strSQL = @strSQL + @strCampo + ' INT, '
     SET @contador = @contador + 1
    END

    SET @strSQL = @strSQL + ')'
    GO

    Saludos,

     

     


    Guillermo Taylor F.

    IT Pro & Xbox gamer

    My blog

    • Marcado como respuesta Adolfo 71 viernes, 4 de noviembre de 2011 21:51
    jueves, 3 de noviembre de 2011 16:52
  • Gracias Alberto por la ayuda!!

    Tengo una consulta con un Select, en esa consulta obtengo un campo con el año.

    Select Nombre, Anio Into #Temporal From Ventas where Anio>'2011'

     

    Entoces, en esa tambla #Temporal, obtengo el mayor año. Por ejemplo si es 2015.

    Ocupo hacer una tablita, que sea:

    Nombre 2012     2013     2014    2015

    Adolfo  $100       $250      $200    $300

    Pedro   $300       $100      -----     -------

     

    Donde los valores de $ los calculo con un cursor, ahi hago varias operaciones, entonces, pense en hacer una tablita temporal

    para ahi ir vaciando los datos. En caso de que el maximo año sea 2016, seria asi:

    Nombre    2012     2013     2014    2015    2016

    Juan        $500      $230    $500    $200    $400

    Adolfo      $100    $250      $200     $300  ------

    Pedro       $300     $100     -----      -------

     

    Saludos!!

    jueves, 3 de noviembre de 2011 16:58
  • Para ese escenario que planteas veo mas apropiado que uses PIVOT, calcular con cursores y tablas temporales esas columnas lo veo muy pesado.  Aqui un articulo que siempre sugiero como punto de referencia, es del colega Salvador Ramos.

    http://www.elguille.info/NET/ADONET/firmas_salva_Pivot_Unpivot.htm


    "El talento es una disciplina tenaz y una larga paciencia"  Gustave Flaubert

     Email: info@geohernandez.com Blog: geeks.ms/blogs/ghernandez

     

    • Marcado como respuesta Adolfo 71 viernes, 4 de noviembre de 2011 21:51
    jueves, 3 de noviembre de 2011 17:18
  • Gracias Guillermo.

    Estoy haciendo la prueba, y me marca el siguiente error:

    Mens. 208, Nivel 16, Estado 0, Lnea 2

    Invalid object name '#Test'.

     

    Agregue la siguiente linea al final:

     

    Select * From #Test

     

    Que me falta?

    Saludos!!

    jueves, 3 de noviembre de 2011 17:38
  • Buenas,
    Aqui te dejo un script de ejemplo que creo que reproduce tu problema. Utilizando PIVOT pero dinámicamente.
    --creacion de la tabla
    create table #ventas
    (
    	Nombre varchar(100),
    	Anyo int,
    	cantidadVenta int
    )
    
    --inserto datos de prueba
    insert into #ventas
    values
    ('Adolfo',2012,100),
    ('Adolfo',2013,250),
    ('Adolfo',2014,200),
    ('Adolfo',2015,300),
    ('Pedro',2012,300),
    ('Pedro',2013,100)
    
    --defino año minimo y maximo
    declare @anyomax int, @anyomin int
    declare @variable varchar(max)
    declare @sql varchar(max)
    
    set @variable=''
    set @sql=''
    
    --establezco el año minimo y maximo
    select @anyomax=Max(Anyo), @anyoMin=Min(Anyo) from #ventas
    
    --genero dinamicamente los años
    while(@anyomin<=@anyomax)
    begin
    
    	if @anyomin=@anyomax
    	begin
    		set @variable=@variable+'['+cast(@anyomin as varchar(10))+']'
    	end
    	else
    	begin
    		set @variable=@variable+'['+cast(@anyomin as varchar(10))+'],'
    	end
    
    	set @anyomin=@anyomin+1
    end
    
    --ejecuto la sentencia con PIVOT
    set @sql='
    select Nombre,'+@variable+'
    from
    (
    	select Nombre, Anyo,CantidadVenta
    	from #ventas
    ) as p
    pivot
    (
    	SUM(CantidadVenta)
    	for Anyo in ('+@variable+')
    ) as Pvt'
    
    exec(@sql)
    
    drop table #ventas
    

    El resultado que obtenemos de este script es el siguiente:
    Si modificamos los datos de la tablacomo sigue:
    insert into #ventas
    values
    ('Adolfo',2012,100),
    ('Adolfo',2013,250),
    ('Adolfo',2014,200),
    ('Adolfo',2015,300),
    ('Pedro',2012,300),
    ('Pedro',2013,100),
    ('Pedro',2018,300),
    ('Adolfo',2019,100)
    

    Ahora el resultado cambia al siguiente:
    Como ves, se han añadido los años dinámicamente.
    Espero que te sirva de ayuda :)
    Un Saludo.

    Entre todos aprendemos mas :) Blog personal: http://blogs.solidq.com/EnriquePuigNouselles/Home.aspx El rincon del DBA: http://blogs.solidq.com/ElRinconDelDBA/Home.aspx La Biblioteca de PowerPivot http://blogs.solidq.com/powerpivot/Home.aspx
    jueves, 3 de noviembre de 2011 18:07
  • Si ejecutaste el string con EXECUTE (@strSQL); GO; no debería faltarte nada...

    Saludos,

     

     


    Guillermo Taylor F.

    IT Pro & Xbox gamer

    My blog

    jueves, 3 de noviembre de 2011 18:17
  • Que tal Enrique.

    Me quedo perfecta la conulta, solo que ocupo meterle otra columna similar a la del Anio, y supuse agregar otro pivot, asi tengo el codigo:

    set @sql=

    'select Nombre,'+@variable+' from ( select Nombre, Anyo,CantidadVenta from #ventas ) as p

    pivot ( SUM(CantidadVenta) for Anyo in ('+@variable+') ) as Pvt

    Inner Join ( select Nombre, Anyo,CantidadVenta from #ventas2 ) as p2

    pivot ( SUM(CantidadVenta) for Anyo in (') ) as Pvt2 ON Pvt.Nombre=Pvt2.Nombre AND Pvt.Anyo=Pvt2.Anyo'+@variable2+'

    Pero me marca error, que 'Invalid column name Anio'

    Asi es la sintaxis? o que estara mal? muchas gracias!!!

    viernes, 11 de noviembre de 2011 21:35
  • set @sql=

    'select Nombre,'+@variable+' from ( select Nombre, Anyo,CantidadVenta from #ventas ) as p

    pivot ( SUM(CantidadVenta) for Anyo in (') ) as Pvt+@variable2+'

    Inner Join ( select Nombre, Anyo,CantidadVenta from #ventas2 ) as p2

    pivot ( SUM(CantidadVenta) for Anyo in (') ) as Pvt2 ON Pvt.Nombre=Pvt2.Nombre AND Pvt.Anyo=Pvt2.Anyo'

    viernes, 11 de noviembre de 2011 21:38