1
votes

Application d'une fonction aux noms de colonnes dynamiques

Je voudrais traiter les résultats d'un pivot dynamique, qui se traduit par des quantités variables de colonnes de données nommées différemment. Mais ils contiennent des données liées les uns aux autres et sont du même type de données. Pour chacune des colonnes de résultats, j'aimerais appliquer une fonction ISNULL identique. Mais comme je ne connais pas les noms des colonnes, il n'est pas possible d'écrire l'opération colonne par colonne.

Voici un SQL Fiddle . Et un exemple de tableau:

 ID |  C1  |  C2  |  C3
----+------+------+-----
 0  |    0 |    0 |    0
 1  |    9 |    0 |    0
 2  |    0 |    8 |    0
 3  |    0 |    0 |   10
 4  |   12 |   61 |    0
 5  |   36 |    0 |   86
 6  |    0 |   77 |   42
 7  |   11 |   22 |   33

Un ISNULL (CN, 0) serait alors appliqué pour chacune de ces colonnes. Comment cela pourrait-il être réalisé? Si cela fait une différence, car la requête pivot est dynamique, ce traitement sera effectué dans un EXEC sp_executesql .

Le résultat attendu serait alors:

CREATE TABLE T (ID INT UNIQUE NOT NULL, C1 INT NULL, C2 INT NULL, C3 INT NULL);
INSERT INTO T VALUES
    (0, NULL, NULL, NULL),
    (1, 9, NULL, NULL),
    (2, NULL, 8, NULL),
    (3, NULL, NULL, 10),
    (4, 12, 61, NULL),
    (5, 36, NULL, 86),
    (6, NULL, 77, 42),
    (7, 11, 22, 33);

SELECT * FROM T;

 ID |  C1  |  C2  |  C3
----+------+------+-----
 0  | NULL | NULL | NULL
 1  |    9 | NULL | NULL
 2  | NULL |    8 | NULL
 3  | NULL | NULL |   10
 4  |   12 |   61 | NULL
 5  |   36 | NULL |   86
 6  | NULL |   77 |   42
 7  |   11 |   22 |   33

sql sql-server

5 commentaires

Quel est votre résultat attendu?

@Felix ne comprend pas la question! Voulez-vous éviter d'écrire ISNULL pour chaque colonne, mais cela devrait fonctionner comme ISNULL ?

@Felix l'a compris!

Vous devrez peut-être générer dynamiquement la requête avec ISNULL et exécuter cette requête. Suivez le lien Comment pouvons-nous utiliser ISNULL pour tous les noms de colonnes dans SQL Server 2008?

@Felix Posté réponse s'il vous plaît vérifier

3 Réponses :

1
votes

Vous pouvez le faire à l'aide de INFORMATION_SCHEMA , STUFF et Dynamic SQL :

-- Get the all columns names from the underlying table
SELECT COLUMN_NAME
INTO #TEMP
FROM [Database_Name].INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'T' AND COLUMN_NAME != 'ID'

DECLARE @COLUMNS  NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

-- Construct a string with ISNULL
SELECT @COLUMNS =  STUFF((SELECT DISTINCT ',ISNULL(' + QUOTENAME(COLUMN_NAME) + ',0) ' + QUOTENAME(COLUMN_NAME) 
FROM #TEMP 
ORDER BY 1 
FOR XML PATH('')), 1, 1, '')

-- and use Dynamic SQL
SELECT @sql = 'SELECT ID,'+ @COLUMNS +' FROM T'

EXEC sp_executesql @sql

p >

0 commentaires

0
votes

J'espère que cela répondra à vos exigences

CREATE TABLE #T (ID INT UNIQUE NOT NULL, C1 INT NULL, C2 INT NULL, C3 INT NULL);
INSERT INTO #T VALUES
    (0, NULL, NULL, NULL),
    (1, 9, NULL, NULL),
    (2, NULL, 8, NULL),
    (3, NULL, NULL, 10),
    (4, 12, 61, NULL),
    (5, 36, NULL, 86),
    (6, NULL, 77, 42),
    (7, 11, 22, 33);

--SELECT * FROM #T;


  Declare  @Main varchar(max)=''
--use INFORMATION_SCHEMA.COLUMNS for physical table 
        select  @Main += ',isnull('+name+',0) as '+name
         from (select name from tempdb.sys.columns where object_id =object_id('tempdb..#T')) as spt

        set @Main= 'select '+stuff(@Main ,1,1,'') + ' from #T'
        Exec(@Main)

0 commentaires

0
votes

Je n'aime pas vraiment les solutions STUFF . Et comme la requête pivot était déjà dynamique, avec l'aide de Raka et cette réponse sur le sujet, j'ai généré la liste des colonnes dynamiquement. Je trouve ce code plus facile à comprendre.

En supposant que les colonnes générées par PIVOT sont dans @Columns TABLE (Column VARCHAR) ou similaire:

DECLARE @Isnull NVARCHAR(MAX);

SELECT @Isnull = ISNULL(@Isnull + ', ', '') + 'ISNULL(' + QUOTENAME([Column]) + ', 0)'
FROM Columns

EXEC sp_executesql N'SELECT ' + @Isnull + 'FROM whatever PIVOT things'

0 commentaires