Logo GNT
Actualités Tests & Guides Bons Plans
GTA 6 iPhone 17 Copilot Switch 2 Temu ChatGPT Tesla
OVH Cloud OVH Cloud
Entraide Windows Windows Serveur SQL Serveur Analyse croisée

Analyse croisée

6 réponses
Avatar
Denis
Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...) mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis
Signaler un problème avec ce contenu

6 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Sylvain Lafontaine
Imprimer quelquepart le résultat de la concaténation des chaînes. Votre
erreur est peut-être due au passage d'un mauvais paramètre.

Finalement, votre préfixe sp_ peut également causer des problèmes.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC


"Denis" wrote in message
news:
Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + '
WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table
+
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN
'
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', '
FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' +
@sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par
une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai
une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...)
mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis




Avatar
Fred BROUARD
Quelques voies :


1) avant d'apeller cette sp, faites :
SET DATEFORMAT YMD

2) corrigez en majuscule les infos de schema

3) simplifiez le CASE

4) remplacerz les 2 premiers exec par un SELECT ... INTO


CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT DISTINCT ' + @pivot + ' AS pivot INTO ##pivot FROM '
+ @table + ' WHERE ' + @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE
WHEN DATA_TYPE LIKE '%char%' THEN ''
WHEN DATA_TYPE LIKE '%date%' THEN ''
ELSE ''''
END
FROM tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='##pivot' AND COLUMN_NAME='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO


Mais difficile d'en dire plus sans que vous expliquiez le contexte

A +


--
Frédéric BROUARD, MVP SQL Server. Expert SQL / spécialiste Delphi, web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
************************ www.datasapiens.com *************************


Denis a écrit:
Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...) mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis




Avatar
Michel PRIORI
Bonjour Denis,

je n'ai pas executé le code mais je ne comprends pas :
@delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) ) WHEN 0 THEN '' ELSE '''' END
Ce qui me gène c'est :
DECLARE @delim varchar(1)


C'est clair que c'est pas ça le pb puisque ça marche (mais je suis
interressé par l'explication)

Pour moi tu devrais aller voir les options implicites de connexion de
l'analyseur. Entre autre j'accorderai une attention au parametre :
concat_null_yields_null vu qu'il y a pas mal de concatenations dans ton
script.



"Denis" a écrit :

Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...) mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis




Avatar
Denis
Bonjour à tous,

Merci pour les pistes

Je suis sur une piste.

J'ai modifié mon programme pour exécuter cette SP non plus sur ma base mais
sur la base PUBS avec cette requete (données en exemple par l'auteur de la
SP):

EXECUTE crosstab 'select pub_name, count(qty) as orders, sum(qty) as total
from sales inner join titles on (sales.title_id=titles.title_id)
right join publishers on (publishers.pub_id=titles.pub_id)
group by pub_name', 'sum(qty)','type','titles'

Mon programme se déroule parfaitement.
Dans cet exemple, les données sont issues de 2 tables (Sales et Publishers)
et une 3 ème table (Titles) sert de jointure pour ne retourner que les
pub_names qui ont des ventes.

Dans mon cas, les données proviennent de 3 tables !!!!. Je pense que le
problème se situe à là. Je vais donc faire une requête intermédiaire et
stocker le résultat dans une table temporaire avant de faire mon analyse
croisée avec la 3ème table.

A bientôt pour plus d'infos.

Aloha, 2nis
"Denis" wrote:

Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...) mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis




Avatar
Denis
Je progresse,

Le problème vient de cette partie du code :

SELECT @sql=@sql + stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE '
+ @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ' as ' +
'''' + convert(varchar(100), pivot) + '''' + ', ' FROM ##pivot

qui ne se comporte pas de la même façon entre l'analyseur de requête et un
prog.


"Denis" wrote:

Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...) mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis




Avatar
Denis
Le problème provient de la variable @delim qui n'est renseignée de la même
façon entre l'analyseur et un prog

"Denis" wrote:

Bonjour à tous

Voici mon problème :
J'ai une procédure stockée qui contient tout le code nécessaire pour
effectuer une analyse croisée

voici le code (merci à l'auteur):

************************************************************

CREATE PROCEDURE sp_CROSSTAB
@select varchar(8000),
@sumfunc varchar(100),
@pivot varchar(100),
@table varchar(100)

AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE
1=2')

EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +
' WHERE '
+ @pivot + ' Is Not Null')

SELECT @sql='' , @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delimÊSE Sign( CharIndex('char', data_type)+CharIndex('date',
data_type) )
WHEN 0 THEN '' ELSE '''' END
FROM tempdb.information_schema.columns
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql
+ ' ')

EXEC (@select)

SET ANSI_WARNINGS ON
GO

************************************************************

Cette procédure est appellée (avec tous les paramètres nécessaires) par une
autre procédure stockée (disons P2).


Lorsque j'exécute P2 via l'analyseur de requête SQL, j'obtiens bien le
résultat attendu retourné par sp_CROSSTAB.
Par contre, lorsque je demande l'exécution de P2 par VB .NET ou c#, j'ai une
erreur dans sp_CROSSTAB à la ligne 18 (syntaxe incorrecte vers le mot clé
'END')

J'ai essayé en redirigant la sortie vers une table (INTO xxx FROM ...) mais
cela ne fonctionne aussi qu'avec l'analyseur, pas via un programme.


Une piste, une solution ??????

Aloha, 2nis