Bon me revoila pour une deuxième question qui va venir completer ma première
question.
Le But du Quarter que vous avez si gentillement calculer est de fair un
"pivot" sur la Table.
Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais rien du
tout sur des varchar (et moi bien sur je veux utiliser des varchar !!)
donc voici l'aide du T-SQL :
****************************************************************************
**********************
Cross-Tab Reports
Sometimes it is necessary to rotate results so that columns are presented
horizontally and rows are presented vertically. This is known as creating a
PivotTable®, creating a cross-tab report, or rotating data.
Assume there is a table Pivot that has one row per quarter. A SELECT of
Pivot reports the quarters vertically:
A report must be produced with a table that contains one row for each year,
with the values for each quarter appearing in a separate column, such as:
Year
Q1
Q2
Q3
Q4
1990
1.1
1.2
1.3
1.4
1991
2.1
2.2
2.3
2.4
These are the statements used to create the Pivot table and populate it with
the data from the first table:
USE Northwind
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
This is the SELECT statement used to create the rotated results:
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
This SELECT statement also handles a table in which there are multiple rows
for each quarter. The GROUP BY combines all rows in Pivot for a given year
into a single row in the output. When the grouping operation is being
performed, the CASE functions in the SUM aggregates are applied in such a
way that the Amount values for each quarter are added into the proper column
in the result set and 0 is added to the result set columns for the other
quarters.
If the results of this SELECT statement are used as input to a spreadsheet,
it is easy for the spreadsheet to calculate a total for each year. When the
SELECT is used from an application it may be easier to enhance the SELECT
statement to calculate the yearly total. For example:
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of
information as shown in the example, but in a slightly different format.
Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit :
USE Northwind
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount varchar(10) )
GO
INSERT INTO Pivot VALUES (1990, 1, '1.1')
INSERT INTO Pivot VALUES (1990, 2, '1.2')
INSERT INTO Pivot VALUES (1990, 3, '1.3')
INSERT INTO Pivot VALUES (1990, 4, '1.4')
INSERT INTO Pivot VALUES (1991, 1, '2.1')
INSERT INTO Pivot VALUES (1991, 2, '2.2')
INSERT INTO Pivot VALUES (1991, 3, '2.3')
INSERT INTO Pivot VALUES (1991, 4, '2.4')
GO
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
Mais là, le problème c'est que avec le SUM il m'oblige a mettre un numeric
(normal) et quand je met :
SELECT Year,
CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1,
CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2,
CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3,
CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
Il me vire pour un problème d'aggregate function ....
Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide
.....
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
Christophe BAVIERE
Bonjour,
Avec la requete (l'astuce) suivante, ça devrait fonctionner même avec des chaines de caractères
SELECT Year, MAX(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, MAX(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, MAX(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, MAX(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year
@+ Christophe BAVIERE
"Jay Mathieu" a écrit dans le message de news: bhsm2o$m6f$
Bonjour !
Bon me revoila pour une deuxième question qui va venir completer ma
première
question. Le But du Quarter que vous avez si gentillement calculer est de fair un "pivot" sur la Table. Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais rien
du
tout sur des varchar (et moi bien sur je veux utiliser des varchar !!)
********************** Cross-Tab Reports Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating
a
PivotTable®, creating a cross-tab report, or rotating data.
Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:
A report must be produced with a table that contains one row for each
year,
with the values for each quarter appearing in a separate column, such as:
Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
These are the statements used to create the Pivot table and populate it
with
the data from the first table:
USE Northwind GO
CREATE TABLE Pivot ( Year SMALLINT, Quarter TINYINT, Amount DECIMAL(2,1) ) GO INSERT INTO Pivot VALUES (1990, 1, 1.1) INSERT INTO Pivot VALUES (1990, 2, 1.2) INSERT INTO Pivot VALUES (1990, 3, 1.3) INSERT INTO Pivot VALUES (1990, 4, 1.4) INSERT INTO Pivot VALUES (1991, 1, 2.1) INSERT INTO Pivot VALUES (1991, 2, 2.2) INSERT INTO Pivot VALUES (1991, 3, 2.3) INSERT INTO Pivot VALUES (1991, 4, 2.4) GO
This is the SELECT statement used to create the rotated results:
SELECT Year, SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1, SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2, SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3, SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year GO
This SELECT statement also handles a table in which there are multiple
rows
for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper
column
in the result set and 0 is added to the result set columns for the other quarters.
If the results of this SELECT statement are used as input to a
spreadsheet,
it is easy for the spreadsheet to calculate a total for each year. When
the
SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total. For example:
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal FROM (SELECT Year, SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1, SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2, SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3, SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4 FROM Pivot AS P GROUP BY P.Year) AS P1 GO
Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of information as shown in the example, but in a slightly different format.
Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit :
USE Northwind GO
CREATE TABLE Pivot ( Year SMALLINT, Quarter TINYINT, Amount varchar(10) ) GO INSERT INTO Pivot VALUES (1990, 1, '1.1') INSERT INTO Pivot VALUES (1990, 2, '1.2') INSERT INTO Pivot VALUES (1990, 3, '1.3') INSERT INTO Pivot VALUES (1990, 4, '1.4') INSERT INTO Pivot VALUES (1991, 1, '2.1') INSERT INTO Pivot VALUES (1991, 2, '2.2') INSERT INTO Pivot VALUES (1991, 3, '2.3') INSERT INTO Pivot VALUES (1991, 4, '2.4') GO
SELECT Year, SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year GO
Mais là, le problème c'est que avec le SUM il m'oblige a mettre un numeric (normal) et quand je met :
SELECT Year, CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1, CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2, CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3, CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year
Il me vire pour un problème d'aggregate function ....
Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide .....
Bonjour,
Avec la requete (l'astuce) suivante, ça devrait fonctionner même avec des
chaines de caractères
SELECT Year,
MAX(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1,
MAX(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2,
MAX(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3,
MAX(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
@+
Christophe BAVIERE
"Jay Mathieu" <mathieu.jay@laposte.net> a écrit dans le message de news:
bhsm2o$m6f$1@news-reader5.wanadoo.fr...
Bonjour !
Bon me revoila pour une deuxième question qui va venir completer ma
première
question.
Le But du Quarter que vous avez si gentillement calculer est de fair un
"pivot" sur la Table.
Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais rien
du
tout sur des varchar (et moi bien sur je veux utiliser des varchar !!)
**********************
Cross-Tab Reports
Sometimes it is necessary to rotate results so that columns are presented
horizontally and rows are presented vertically. This is known as creating
a
PivotTable®, creating a cross-tab report, or rotating data.
Assume there is a table Pivot that has one row per quarter. A SELECT of
Pivot reports the quarters vertically:
A report must be produced with a table that contains one row for each
year,
with the values for each quarter appearing in a separate column, such as:
Year
Q1
Q2
Q3
Q4
1990
1.1
1.2
1.3
1.4
1991
2.1
2.2
2.3
2.4
These are the statements used to create the Pivot table and populate it
with
the data from the first table:
USE Northwind
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount DECIMAL(2,1) )
GO
INSERT INTO Pivot VALUES (1990, 1, 1.1)
INSERT INTO Pivot VALUES (1990, 2, 1.2)
INSERT INTO Pivot VALUES (1990, 3, 1.3)
INSERT INTO Pivot VALUES (1990, 4, 1.4)
INSERT INTO Pivot VALUES (1991, 1, 2.1)
INSERT INTO Pivot VALUES (1991, 2, 2.2)
INSERT INTO Pivot VALUES (1991, 3, 2.3)
INSERT INTO Pivot VALUES (1991, 4, 2.4)
GO
This is the SELECT statement used to create the rotated results:
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
This SELECT statement also handles a table in which there are multiple
rows
for each quarter. The GROUP BY combines all rows in Pivot for a given year
into a single row in the output. When the grouping operation is being
performed, the CASE functions in the SUM aggregates are applied in such a
way that the Amount values for each quarter are added into the proper
column
in the result set and 0 is added to the result set columns for the other
quarters.
If the results of this SELECT statement are used as input to a
spreadsheet,
it is easy for the spreadsheet to calculate a total for each year. When
the
SELECT is used from an application it may be easier to enhance the SELECT
statement to calculate the yearly total. For example:
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
FROM (SELECT Year,
SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
FROM Pivot AS P
GROUP BY P.Year) AS P1
GO
Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of
information as shown in the example, but in a slightly different format.
Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit :
USE Northwind
GO
CREATE TABLE Pivot
( Year SMALLINT,
Quarter TINYINT,
Amount varchar(10) )
GO
INSERT INTO Pivot VALUES (1990, 1, '1.1')
INSERT INTO Pivot VALUES (1990, 2, '1.2')
INSERT INTO Pivot VALUES (1990, 3, '1.3')
INSERT INTO Pivot VALUES (1990, 4, '1.4')
INSERT INTO Pivot VALUES (1991, 1, '2.1')
INSERT INTO Pivot VALUES (1991, 2, '2.2')
INSERT INTO Pivot VALUES (1991, 3, '2.3')
INSERT INTO Pivot VALUES (1991, 4, '2.4')
GO
SELECT Year,
SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1,
SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2,
SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3,
SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
GO
Mais là, le problème c'est que avec le SUM il m'oblige a mettre un numeric
(normal) et quand je met :
SELECT Year,
CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1,
CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2,
CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3,
CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
Il me vire pour un problème d'aggregate function ....
Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide
.....
Avec la requete (l'astuce) suivante, ça devrait fonctionner même avec des chaines de caractères
SELECT Year, MAX(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, MAX(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, MAX(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, MAX(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year
@+ Christophe BAVIERE
"Jay Mathieu" a écrit dans le message de news: bhsm2o$m6f$
Bonjour !
Bon me revoila pour une deuxième question qui va venir completer ma
première
question. Le But du Quarter que vous avez si gentillement calculer est de fair un "pivot" sur la Table. Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais rien
du
tout sur des varchar (et moi bien sur je veux utiliser des varchar !!)
********************** Cross-Tab Reports Sometimes it is necessary to rotate results so that columns are presented horizontally and rows are presented vertically. This is known as creating
a
PivotTable®, creating a cross-tab report, or rotating data.
Assume there is a table Pivot that has one row per quarter. A SELECT of Pivot reports the quarters vertically:
A report must be produced with a table that contains one row for each
year,
with the values for each quarter appearing in a separate column, such as:
Year Q1 Q2 Q3 Q4
1990 1.1 1.2 1.3 1.4
1991 2.1 2.2 2.3 2.4
These are the statements used to create the Pivot table and populate it
with
the data from the first table:
USE Northwind GO
CREATE TABLE Pivot ( Year SMALLINT, Quarter TINYINT, Amount DECIMAL(2,1) ) GO INSERT INTO Pivot VALUES (1990, 1, 1.1) INSERT INTO Pivot VALUES (1990, 2, 1.2) INSERT INTO Pivot VALUES (1990, 3, 1.3) INSERT INTO Pivot VALUES (1990, 4, 1.4) INSERT INTO Pivot VALUES (1991, 1, 2.1) INSERT INTO Pivot VALUES (1991, 2, 2.2) INSERT INTO Pivot VALUES (1991, 3, 2.3) INSERT INTO Pivot VALUES (1991, 4, 2.4) GO
This is the SELECT statement used to create the rotated results:
SELECT Year, SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1, SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2, SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3, SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year GO
This SELECT statement also handles a table in which there are multiple
rows
for each quarter. The GROUP BY combines all rows in Pivot for a given year into a single row in the output. When the grouping operation is being performed, the CASE functions in the SUM aggregates are applied in such a way that the Amount values for each quarter are added into the proper
column
in the result set and 0 is added to the result set columns for the other quarters.
If the results of this SELECT statement are used as input to a
spreadsheet,
it is easy for the spreadsheet to calculate a total for each year. When
the
SELECT is used from an application it may be easier to enhance the SELECT statement to calculate the yearly total. For example:
SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal FROM (SELECT Year, SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1, SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2, SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3, SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4 FROM Pivot AS P GROUP BY P.Year) AS P1 GO
Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort of information as shown in the example, but in a slightly different format.
Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit :
USE Northwind GO
CREATE TABLE Pivot ( Year SMALLINT, Quarter TINYINT, Amount varchar(10) ) GO INSERT INTO Pivot VALUES (1990, 1, '1.1') INSERT INTO Pivot VALUES (1990, 2, '1.2') INSERT INTO Pivot VALUES (1990, 3, '1.3') INSERT INTO Pivot VALUES (1990, 4, '1.4') INSERT INTO Pivot VALUES (1991, 1, '2.1') INSERT INTO Pivot VALUES (1991, 2, '2.2') INSERT INTO Pivot VALUES (1991, 3, '2.3') INSERT INTO Pivot VALUES (1991, 4, '2.4') GO
SELECT Year, SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year GO
Mais là, le problème c'est que avec le SUM il m'oblige a mettre un numeric (normal) et quand je met :
SELECT Year, CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1, CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2, CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3, CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year
Il me vire pour un problème d'aggregate function ....
Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide .....
Jay Mathieu
OK c tout bon ca marche !!! Merci !
"Christophe BAVIERE" wrote in message news:
Bonjour,
Avec la requete (l'astuce) suivante, ça devrait fonctionner même avec des chaines de caractères
SELECT Year, MAX(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, MAX(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, MAX(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, MAX(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year
@+ Christophe BAVIERE
"Jay Mathieu" a écrit dans le message de news: bhsm2o$m6f$ > Bonjour ! > > Bon me revoila pour une deuxième question qui va venir completer ma première > question. > Le But du Quarter que vous avez si gentillement calculer est de fair un > "pivot" sur la Table. > Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais
rien
du > tout sur des varchar (et moi bien sur je veux utiliser des varchar !!) > > donc voici l'aide du T-SQL : >
> ********************** > Cross-Tab Reports > Sometimes it is necessary to rotate results so that columns are
presented
> horizontally and rows are presented vertically. This is known as
creating
a > PivotTable®, creating a cross-tab report, or rotating data. > > Assume there is a table Pivot that has one row per quarter. A SELECT of > Pivot reports the quarters vertically: > > Year Quarter Amount > ---- ------- ------ > 1990 1 1.1 > 1990 2 1.2 > 1990 3 1.3 > 1990 4 1.4 > 1991 1 2.1 > 1991 2 2.2 > 1991 3 2.3 > 1991 4 2.4 > > A report must be produced with a table that contains one row for each year, > with the values for each quarter appearing in a separate column, such
as:
> > Year > Q1 > Q2 > Q3 > Q4 > > 1990 > 1.1 > 1.2 > 1.3 > 1.4 > > 1991 > 2.1 > 2.2 > 2.3 > 2.4 > > > > > These are the statements used to create the Pivot table and populate it with > the data from the first table: > > USE Northwind > GO > > CREATE TABLE Pivot > ( Year SMALLINT, > Quarter TINYINT, > Amount DECIMAL(2,1) ) > GO > INSERT INTO Pivot VALUES (1990, 1, 1.1) > INSERT INTO Pivot VALUES (1990, 2, 1.2) > INSERT INTO Pivot VALUES (1990, 3, 1.3) > INSERT INTO Pivot VALUES (1990, 4, 1.4) > INSERT INTO Pivot VALUES (1991, 1, 2.1) > INSERT INTO Pivot VALUES (1991, 2, 2.2) > INSERT INTO Pivot VALUES (1991, 3, 2.3) > INSERT INTO Pivot VALUES (1991, 4, 2.4) > GO > > This is the SELECT statement used to create the rotated results: > > SELECT Year, > SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1, > SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2, > SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3, > SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4 > FROM Northwind.dbo.Pivot > GROUP BY Year > GO > > This SELECT statement also handles a table in which there are multiple rows > for each quarter. The GROUP BY combines all rows in Pivot for a given
year
> into a single row in the output. When the grouping operation is being > performed, the CASE functions in the SUM aggregates are applied in such
a
> way that the Amount values for each quarter are added into the proper column > in the result set and 0 is added to the result set columns for the other > quarters. > > If the results of this SELECT statement are used as input to a spreadsheet, > it is easy for the spreadsheet to calculate a total for each year. When the > SELECT is used from an application it may be easier to enhance the
SELECT
> statement to calculate the yearly total. For example: > > SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal > FROM (SELECT Year, > SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1, > SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2, > SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3, > SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4 > FROM Pivot AS P > GROUP BY P.Year) AS P1 > GO > > Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort
> ********************** > > Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit
:
> > USE Northwind > GO > > CREATE TABLE Pivot > ( Year SMALLINT, > Quarter TINYINT, > Amount varchar(10) ) > GO > INSERT INTO Pivot VALUES (1990, 1, '1.1') > INSERT INTO Pivot VALUES (1990, 2, '1.2') > INSERT INTO Pivot VALUES (1990, 3, '1.3') > INSERT INTO Pivot VALUES (1990, 4, '1.4') > INSERT INTO Pivot VALUES (1991, 1, '2.1') > INSERT INTO Pivot VALUES (1991, 2, '2.2') > INSERT INTO Pivot VALUES (1991, 3, '2.3') > INSERT INTO Pivot VALUES (1991, 4, '2.4') > GO > > SELECT Year, > SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, > SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, > SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, > SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 > FROM Northwind.dbo.Pivot > GROUP BY Year > GO > > Mais là, le problème c'est que avec le SUM il m'oblige a mettre un
numeric
> (normal) et quand je met : > > SELECT Year, > CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1, > CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2, > CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3, > CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4 > FROM Northwind.dbo.Pivot > GROUP BY Year > > > Il me vire pour un problème d'aggregate function .... > > Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide > ..... > > > > > > > > >
OK c tout bon ca marche !!!
Merci !
"Christophe BAVIERE" <cbaviere@logan-info.fr> wrote in message
news:eKx3ZVjZDHA.2024@TK2MSFTNGP12.phx.gbl...
Bonjour,
Avec la requete (l'astuce) suivante, ça devrait fonctionner même avec des
chaines de caractères
SELECT Year,
MAX(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1,
MAX(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2,
MAX(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3,
MAX(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4
FROM Northwind.dbo.Pivot
GROUP BY Year
@+
Christophe BAVIERE
"Jay Mathieu" <mathieu.jay@laposte.net> a écrit dans le message de news:
bhsm2o$m6f$1@news-reader5.wanadoo.fr...
> Bonjour !
>
> Bon me revoila pour une deuxième question qui va venir completer ma
première
> question.
> Le But du Quarter que vous avez si gentillement calculer est de fair un
> "pivot" sur la Table.
> Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais
rien
du
> tout sur des varchar (et moi bien sur je veux utiliser des varchar !!)
>
> donc voici l'aide du T-SQL :
>
> **********************
> Cross-Tab Reports
> Sometimes it is necessary to rotate results so that columns are
presented
> horizontally and rows are presented vertically. This is known as
creating
a
> PivotTable®, creating a cross-tab report, or rotating data.
>
> Assume there is a table Pivot that has one row per quarter. A SELECT of
> Pivot reports the quarters vertically:
>
> Year Quarter Amount
> ---- ------- ------
> 1990 1 1.1
> 1990 2 1.2
> 1990 3 1.3
> 1990 4 1.4
> 1991 1 2.1
> 1991 2 2.2
> 1991 3 2.3
> 1991 4 2.4
>
> A report must be produced with a table that contains one row for each
year,
> with the values for each quarter appearing in a separate column, such
as:
>
> Year
> Q1
> Q2
> Q3
> Q4
>
> 1990
> 1.1
> 1.2
> 1.3
> 1.4
>
> 1991
> 2.1
> 2.2
> 2.3
> 2.4
>
>
>
>
> These are the statements used to create the Pivot table and populate it
with
> the data from the first table:
>
> USE Northwind
> GO
>
> CREATE TABLE Pivot
> ( Year SMALLINT,
> Quarter TINYINT,
> Amount DECIMAL(2,1) )
> GO
> INSERT INTO Pivot VALUES (1990, 1, 1.1)
> INSERT INTO Pivot VALUES (1990, 2, 1.2)
> INSERT INTO Pivot VALUES (1990, 3, 1.3)
> INSERT INTO Pivot VALUES (1990, 4, 1.4)
> INSERT INTO Pivot VALUES (1991, 1, 2.1)
> INSERT INTO Pivot VALUES (1991, 2, 2.2)
> INSERT INTO Pivot VALUES (1991, 3, 2.3)
> INSERT INTO Pivot VALUES (1991, 4, 2.4)
> GO
>
> This is the SELECT statement used to create the rotated results:
>
> SELECT Year,
> SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1,
> SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2,
> SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3,
> SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4
> FROM Northwind.dbo.Pivot
> GROUP BY Year
> GO
>
> This SELECT statement also handles a table in which there are multiple
rows
> for each quarter. The GROUP BY combines all rows in Pivot for a given
year
> into a single row in the output. When the grouping operation is being
> performed, the CASE functions in the SUM aggregates are applied in such
a
> way that the Amount values for each quarter are added into the proper
column
> in the result set and 0 is added to the result set columns for the other
> quarters.
>
> If the results of this SELECT statement are used as input to a
spreadsheet,
> it is easy for the spreadsheet to calculate a total for each year. When
the
> SELECT is used from an application it may be easier to enhance the
SELECT
> statement to calculate the yearly total. For example:
>
> SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal
> FROM (SELECT Year,
> SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1,
> SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2,
> SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3,
> SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4
> FROM Pivot AS P
> GROUP BY P.Year) AS P1
> GO
>
> Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort
> **********************
>
> Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit
:
>
> USE Northwind
> GO
>
> CREATE TABLE Pivot
> ( Year SMALLINT,
> Quarter TINYINT,
> Amount varchar(10) )
> GO
> INSERT INTO Pivot VALUES (1990, 1, '1.1')
> INSERT INTO Pivot VALUES (1990, 2, '1.2')
> INSERT INTO Pivot VALUES (1990, 3, '1.3')
> INSERT INTO Pivot VALUES (1990, 4, '1.4')
> INSERT INTO Pivot VALUES (1991, 1, '2.1')
> INSERT INTO Pivot VALUES (1991, 2, '2.2')
> INSERT INTO Pivot VALUES (1991, 3, '2.3')
> INSERT INTO Pivot VALUES (1991, 4, '2.4')
> GO
>
> SELECT Year,
> SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1,
> SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2,
> SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3,
> SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4
> FROM Northwind.dbo.Pivot
> GROUP BY Year
> GO
>
> Mais là, le problème c'est que avec le SUM il m'oblige a mettre un
numeric
> (normal) et quand je met :
>
> SELECT Year,
> CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1,
> CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2,
> CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3,
> CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4
> FROM Northwind.dbo.Pivot
> GROUP BY Year
>
>
> Il me vire pour un problème d'aggregate function ....
>
> Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide
> .....
>
>
>
>
>
>
>
>
>
Avec la requete (l'astuce) suivante, ça devrait fonctionner même avec des chaines de caractères
SELECT Year, MAX(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, MAX(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, MAX(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, MAX(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 FROM Northwind.dbo.Pivot GROUP BY Year
@+ Christophe BAVIERE
"Jay Mathieu" a écrit dans le message de news: bhsm2o$m6f$ > Bonjour ! > > Bon me revoila pour une deuxième question qui va venir completer ma première > question. > Le But du Quarter que vous avez si gentillement calculer est de fair un > "pivot" sur la Table. > Sur l'aide de T-SQL j'ai trouver un pivot fait sur des numéric, mais
rien
du > tout sur des varchar (et moi bien sur je veux utiliser des varchar !!) > > donc voici l'aide du T-SQL : >
> ********************** > Cross-Tab Reports > Sometimes it is necessary to rotate results so that columns are
presented
> horizontally and rows are presented vertically. This is known as
creating
a > PivotTable®, creating a cross-tab report, or rotating data. > > Assume there is a table Pivot that has one row per quarter. A SELECT of > Pivot reports the quarters vertically: > > Year Quarter Amount > ---- ------- ------ > 1990 1 1.1 > 1990 2 1.2 > 1990 3 1.3 > 1990 4 1.4 > 1991 1 2.1 > 1991 2 2.2 > 1991 3 2.3 > 1991 4 2.4 > > A report must be produced with a table that contains one row for each year, > with the values for each quarter appearing in a separate column, such
as:
> > Year > Q1 > Q2 > Q3 > Q4 > > 1990 > 1.1 > 1.2 > 1.3 > 1.4 > > 1991 > 2.1 > 2.2 > 2.3 > 2.4 > > > > > These are the statements used to create the Pivot table and populate it with > the data from the first table: > > USE Northwind > GO > > CREATE TABLE Pivot > ( Year SMALLINT, > Quarter TINYINT, > Amount DECIMAL(2,1) ) > GO > INSERT INTO Pivot VALUES (1990, 1, 1.1) > INSERT INTO Pivot VALUES (1990, 2, 1.2) > INSERT INTO Pivot VALUES (1990, 3, 1.3) > INSERT INTO Pivot VALUES (1990, 4, 1.4) > INSERT INTO Pivot VALUES (1991, 1, 2.1) > INSERT INTO Pivot VALUES (1991, 2, 2.2) > INSERT INTO Pivot VALUES (1991, 3, 2.3) > INSERT INTO Pivot VALUES (1991, 4, 2.4) > GO > > This is the SELECT statement used to create the rotated results: > > SELECT Year, > SUM(CASE Quarter WHEN 1 THEN Amount ELSE 0 END) AS Q1, > SUM(CASE Quarter WHEN 2 THEN Amount ELSE 0 END) AS Q2, > SUM(CASE Quarter WHEN 3 THEN Amount ELSE 0 END) AS Q3, > SUM(CASE Quarter WHEN 4 THEN Amount ELSE 0 END) AS Q4 > FROM Northwind.dbo.Pivot > GROUP BY Year > GO > > This SELECT statement also handles a table in which there are multiple rows > for each quarter. The GROUP BY combines all rows in Pivot for a given
year
> into a single row in the output. When the grouping operation is being > performed, the CASE functions in the SUM aggregates are applied in such
a
> way that the Amount values for each quarter are added into the proper column > in the result set and 0 is added to the result set columns for the other > quarters. > > If the results of this SELECT statement are used as input to a spreadsheet, > it is easy for the spreadsheet to calculate a total for each year. When the > SELECT is used from an application it may be easier to enhance the
SELECT
> statement to calculate the yearly total. For example: > > SELECT P1.*, (P1.Q1 + P1.Q2 + P1.Q3 + P1.Q4) AS YearTotal > FROM (SELECT Year, > SUM(CASE P.Quarter WHEN 1 THEN P.Amount ELSE 0 END) AS Q1, > SUM(CASE P.Quarter WHEN 2 THEN P.Amount ELSE 0 END) AS Q2, > SUM(CASE P.Quarter WHEN 3 THEN P.Amount ELSE 0 END) AS Q3, > SUM(CASE P.Quarter WHEN 4 THEN P.Amount ELSE 0 END) AS Q4 > FROM Pivot AS P > GROUP BY P.Year) AS P1 > GO > > Both GROUP BY with CUBE and GROUP BY with ROLLUP compute the same sort
> ********************** > > Donc moi je voudrais le meme pivot avec des varchar pour le Amount soit
:
> > USE Northwind > GO > > CREATE TABLE Pivot > ( Year SMALLINT, > Quarter TINYINT, > Amount varchar(10) ) > GO > INSERT INTO Pivot VALUES (1990, 1, '1.1') > INSERT INTO Pivot VALUES (1990, 2, '1.2') > INSERT INTO Pivot VALUES (1990, 3, '1.3') > INSERT INTO Pivot VALUES (1990, 4, '1.4') > INSERT INTO Pivot VALUES (1991, 1, '2.1') > INSERT INTO Pivot VALUES (1991, 2, '2.2') > INSERT INTO Pivot VALUES (1991, 3, '2.3') > INSERT INTO Pivot VALUES (1991, 4, '2.4') > GO > > SELECT Year, > SUM(CASE Quarter WHEN 1 THEN Amount ELSE '' END) AS Q1, > SUM(CASE Quarter WHEN 2 THEN Amount ELSE '' END) AS Q2, > SUM(CASE Quarter WHEN 3 THEN Amount ELSE '' END) AS Q3, > SUM(CASE Quarter WHEN 4 THEN Amount ELSE '' END) AS Q4 > FROM Northwind.dbo.Pivot > GROUP BY Year > GO > > Mais là, le problème c'est que avec le SUM il m'oblige a mettre un
numeric
> (normal) et quand je met : > > SELECT Year, > CASE Quarter WHEN 1 THEN Amount ELSE '' END AS Q1, > CASE Quarter WHEN 2 THEN Amount ELSE '' END AS Q2, > CASE Quarter WHEN 3 THEN Amount ELSE '' END AS Q3, > CASE Quarter WHEN 4 THEN Amount ELSE '' END AS Q4 > FROM Northwind.dbo.Pivot > GROUP BY Year > > > Il me vire pour un problème d'aggregate function .... > > Bon, donc du coup si une gentille ame pouvais 'encore' venir a mon aide > ..... > > > > > > > > >
