SQL Server - Inclure NULL en utilisant UNPIVOT

UNPIVOT ne reviendra pas Nuls, mais j'en ai besoin dans une requête de comparaison. Je suis en train d'essayer d'éviter d'utiliser la fonction ISNULL l'exemple suivant (Parce que dans la vraie sql il y a plus de 100 champs.:

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

Des alternatives?

source d'informationauteur JeffO

  1. 11

    C'est une vraie douleur. Vous devez passer avant de le UNPIVOTcar il n'y a pas de ligne de produits pour ISNULL() pour fonctionner sur - la génération de code est votre ami ici.

    J'ai le problème sur PIVOT. Lignes manquantes transformer en NULLque vous avez de l'envelopper dans ISNULL() tout le chemin à travers la ligne si les valeurs manquantes sont les mêmes que 0.0 par exemple.

  2. 15

    De préserver les valeurs Null, utilisez la CROIX REJOINDRE ... CAS:

    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)

    Au lieu 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 éditeur de texte avec la colonne, le mode telles requêtes plus facile à écrire. UltraEdit a, par Emacs. Dans Emacs, il est appelé rectangulaire modifier.

    Vous pourriez avoir besoin de l'écrire pour 100 colonnes.

  3. 3

    ou, dans SQLServer 2008 de plus court chemin:

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

    Je rencontre le même problème, à l'Aide de la CROIX-APPLIQUER (Sql Server 2005 et versions Ultérieures) au Lieu de Unpivot Résolu le problème. J'ai trouvé la solution Basée sur cet Article Une Alternative (Mieux?) Méthode pour UNPIVOT
    et j'ai pris l'exemple suivant, afin de démontrer que la CROIX s'APPLIQUENT PAS Ignorer les valeurs Null comme 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(Measure, MeasureType) 


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


 drop table #Orders
  5. 2

    J'ai trouvé externe gauche rejoindre la UNPIVOT résultat pour la liste complète des champs, idéalement tiré de INFORMATION_SCHEMA, pour être une réponse pratique à ce problème dans certains contextes.

    -- 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>

    de sortie ressemble à ceci: 

    +----------------------+-----------------------+ 
| COLUMN_NAME | Valeur | 
+----------------------+-----------------------+ 
| nom | 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 | 
+----------------------+-----------------------+

  6. 1

    L'aide de SQL dynamique et FUSIONNENT, j'ai résolu le problème comme ceci:

    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 )
  7. -1

    ISNULL est la moitié de la réponse. Utilisation NULLIF de traduire NULL. E. g. 

    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;

    J'utilise ici "0Null" comme ma valeur intermédiaire. Vous pouvez l'utiliser comme vous le souhaitez. Cependant, tu risques de collision avec la saisie de l'utilisateur si vous choisissez quelque chose dans le monde réel comme "Null". Poubelle fonctionne très bien "!@#34())0" mais peut-être plus de confusion à l'avenir des codeurs. Je suis sûr que vous obtenez l'image.