bootstrap - SQL Server-Incluir NULL utilizando UNPIVOT

tags jquery

Question

UNPIVOT no devolverá NULL, pero los necesito en una consulta de comparación. Intento evitar usar ISNULL en el siguiente ejemplo (porque en el sql real hay más de 100 campos .:

Select ID, theValue, column_name
From 
(select ID,
  ISNULL(CAST([TheColumnToCompare]  AS VarChar(1000)), '''') as TheColumnToCompare
  from MyView
  where The_Date = ''04/30/2009''
) MA
UNPIVOT
   (theValue FOR column_name IN 
   ([TheColumnToCompare])
) AS unpvt

¿Alguna alternativa?

Answer 1

En algunos contextos, encontré que la izquierda se unió al resultado de UNPIVOT a la lista completa de campos, convenientemente extraída de INFORMATION_SCHEMA, como una respuesta práctica a este problema.

-- test data
CREATE TABLE _t1(name varchar(20),object_id varchar(20),principal_id varchar(20),schema_id varchar(20),parent_object_id varchar(20),type varchar(20),type_desc varchar(20),create_date varchar(20),modify_date varchar(20),is_ms_shipped varchar(20),is_published varchar(20),is_schema_published varchar(20))
INSERT INTO _t1 SELECT ''blah1'', 3, NULL, 4, 0, ''blah2'', ''blah3'', ''20100402 16:59:23.267'', NULL, 1, 0, 0 

-- example
select c.COLUMN_NAME, Value
from INFORMATION_SCHEMA.COLUMNS c
left join (
  select * from _t1
) q1
unpivot (Value for COLUMN_NAME in (name,object_id,principal_id,schema_id,parent_object_id,type,type_desc,create_date,modify_date,is_ms_shipped,is_published,is_schema_published)
) t on t.COLUMN_NAME = c.COLUMN_NAME
where c.TABLE_NAME = ''_t1''
</pre>

la salida se ve como

+----------------------+-----------------------+ | COLUMN_NAME | Value | +----------------------+-----------------------+ | name | blah1 | | object_id | 3 | | principal_id | NULL | <====== | schema_id | 4 | | parent_object_id | 0 | | type | blah2 | | type_desc | blah3 | | create_date | 20100402 16:59:23.26 | | modify_date | NULL | <====== | is_ms_shipped | 1 | | is_published | 0 | | is_schema_published | 0 | +----------------------+-----------------------+

Answer 2

Es un verdadero dolor. ISNULL() cambiarlos antes de UNPIVOT , ya que no hay una fila producida para que ISNULL() funcione en la generación de códigos: su amigo está aquí.

Tengo el problema en PIVOT también. Las filas que faltan se convierten en NULL , que tiene que envolver en ISNULL() toda la fila si los valores faltantes son los mismos que 0.0 por ejemplo.

Answer 3

ISNULL es la mitad de la respuesta. Use NULLIF para traducir de nuevo a NULL. P.ej

DECLARE @temp TABLE(
    Foo varchar(50),
    Bar varchar(50) NULL
    );

INSERT INTO @temp( Foo,Bar )VALUES( ''licious'',NULL );

SELECT * FROM @temp;

SELECT 
    Col,
    NULLIF( Val,''0Null'' ) AS Val 
FROM(
    SELECT
        Foo,
        ISNULL( Bar,''0Null'' ) AS Bar
    FROM
        @temp
    ) AS t
UNPIVOT(
    Val FOR Col IN(
        Foo,
        Bar 
        )
    ) up;

Aquí utilizo "0Null" como mi valor intermedio. Puedes usar lo que quieras. Sin embargo, corre el riesgo de colisión con la entrada del usuario si elige algo del mundo real como "Null". La basura funciona bien "! @ # 34 ()) 0", pero puede ser más confusa para los futuros codificadores. Estoy seguro de que te haces una idea.

Answer 4

O, en SQLServer 2008 de forma más corta:

...
cross join 
(values(''col1''), (''col2''), (''col3''), (''col4'')) column_names(column_name)

Answer 5

Para conservar los valores nulos, use CROSS JOIN ... CASE:

select a.ID, b.column_name
, column_value = 
    case b.column_name
      when ''col1'' then a.col1
      when ''col2'' then a.col2
      when ''col3'' then a.col3
      when ''col4'' then a.col4
    end
from (
  select ID, col1, col2, col3, col4 
  from table1
  ) a
cross join (
  select ''col1'' union all
  select ''col2'' union all
  select ''col3'' union all
  select ''col4''
  ) b (column_name)

En lugar de:

select ID, column_name, column_value
From (
  select ID, col1, col2, col3, col4
  from from table1
  ) a
unpivot (
  column_value FOR column_name IN (
    col1, col2, col3, col4)
  ) b

Un editor de texto con modo de columna hace que estas consultas sean más fáciles de escribir. UltraEdit lo tiene, también lo tiene Emacs. En Emacs se llama edición rectangular.

Es posible que tenga que escribirlo para 100 columnas.

Answer 6

Tuve el mismo problema. El uso de CROSS APPLY (SQL Server 2005 y posterior) en lugar de Unpivot resolvió el problema. Encontré la solución basada en este artículo. Un método alternativo (¿mejor?) A UNPIVOT e hice el siguiente ejemplo para demostrar que CROSS APPLY NO Unpivot NULL como Unpivot .

create table #Orders (OrderDate datetime, product nvarchar(100), ItemsCount float, GrossAmount float, employee nvarchar(100))

 insert into #Orders
 select getutcdate(),''Windows'',10,10.32,''Me''
 union 
 select getutcdate(),''Office'',31,21.23,''you''
 union 
 select getutcdate(),''Office'',31,55.45,''me''
 union  
 select getutcdate(),''Windows'',10,null,''You''

SELECT OrderDate, product,employee,Measure,MeasureType
 from #Orders orders
 CROSS APPLY (
    VALUES (''ItemsCount'',ItemsCount),(''GrossAmount'',GrossAmount)
    ) 
    x(MeasureType, Measure) 


SELECT OrderDate, product,employee,Measure,MeasureType
from #Orders orders
UNPIVOT
   (Measure FOR MeasureType IN 
      (ItemsCount,GrossAmount)
)AS unpvt;


 drop table #Orders

Answer 7

Usando SQL dinámico y COALESCE, resolví el problema de esta manera:

DECLARE @SQL NVARCHAR(MAX)
DECLARE @cols NVARCHAR(MAX)
DECLARE @dataCols NVARCHAR(MAX)

SELECT 
    @dataCols = COALESCE(@dataCols + '', '' + ''ISNULL('' + Name + '',0) '' + Name , ''ISNULL('' + Name + '',0) '' + Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SELECT 
    @cols = COALESCE(@cols + '', '' + Name , Name )
FROM Metric WITH (NOLOCK)
ORDER BY ID

SET @SQL = ''SELECT ArchiveID, MetricDate, BoxID, GroupID, ID MetricID, MetricName, Value
            FROM 
               (SELECT ArchiveID, [Date] MetricDate, BoxID, GroupID,  '' + @dataCols + ''
                FROM MetricData WITH (NOLOCK)
                INNER JOIN Archive WITH (NOLOCK)
                    ON ArchiveID = ID
                WHERE BoxID = '' + CONVERT(VARCHAR(40), @BoxID) + ''
                AND GroupID = '' + CONVERT(VARCHAR(40), @GroupID) + '') p
            UNPIVOT
               (Value FOR MetricName IN 
                  ('' + @cols + '')
            )AS unpvt
            INNER JOIN Metric WITH (NOLOCK)
                ON MetricName  = Name
            ORDER BY MetricID, MetricDate''

EXECUTE( @SQL )