Discussion :

[blaise4714]

Salut!
une petite question: j aimerai ecrire une requete: Pour un numero de client, il existe 3 lignes, ces lignes different juste sur le nom du produit que le client utilise. J aimerais donc maintenant regrouper ces lignes en une seule ligne. C est à dire:
Voici le tableau
T1
n° client Produit
123645 P1
123645 P2
123645 P3
123646 P1

Voici ce que ma requete doit donner comme resultat:
n° client P1 P2 P3
123645 oui oui oui
123646 oui non non
Auriez vous une idee pour ecrire la requete SQL?
Merci d avance.

[skuatamad]

Recherche PIVOT sur le forum.
Si le nombre de produit est fixe et petit, c'est simple, sinon (et je pense que c'est ton cas) c'est complexe et inutile côté BDD.
Dans ce cas gère ton pivot côté applicatif.

[blaise4714]

Salut skuatamad,
merci pour ta reponse, mais je ne comprends pas. Peux me donner un exemple?
Merci

[fatsora]

autrement dit si tes données sont juste ceux que tu as présentées cad quelques lignes , alors voici une reponse possible

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SELECT client_id,
MAX(DECODE(operation, 'P1', 'OUI', 'NON')) P1,
MAX(DECODE(operation, 'P2', 'OUI', 'NON')) P2,
MAX(DECODE(operation, 'P3', 'OUI', 'NON')) P3
FROM cli_1
GROUP BY client_id
/

sinon .... ca va etre difficile

autre chose quelle version ORACLE tu as ?
(en 11G tu as PIVOT...par exemple)

[s.a.dev]

Peut-etre avec la fonction PIVOT, et l'enregistrement avec contrainte, arriver à créer une deuxième table:

N_client P1 P2 P3
1 oui oui oui
2 non oui non
3 non non non

et faire enfin un select * from table2;

[salim11]

Un post sur otn (posté par le guru Frank Kulash)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
 
 
Another approach is to use String Aggregation to combine all the counts and sums into one humongeous VARCHAR2 column, padded so that the numbers appear to be in diofferent columns. 
AskTom.oracle.com shows several different ways to do string aggregation.
 
Example of dynamic pivot:
 
/*
How to Pivot a Table with a Dynamic Number of Columns
 
This works in any version of Oracle
The "SELECT ... PIVOT" feature introduced in Oracle 11
is much better for producing XML output.
 
Say you want to make a cross-tab output of
the scott.emp table.
Each row will represent a department.
There will be a separate column for each job.
Each cell will contain the number of employees in
    a specific department having a specific job.
The exact same solution must work with any number
of departments and columns.
(Within reason: there's no guarantee this will work if you 
want 2000 columns.)
 
Case 0 "Basic Pivot" shows how you might hard-code three
job types, which is exactly what you DON'T want to do.
Case 1 "Dynamic Pivot" shows how get the right results
dynamically, using SQL*Plus.  
(This can be easily adapted to PL/SQL or other tools.)
*/
 
 
PROMPT     ==========  0. Basic Pivot  ==========
 
SELECT     deptno
,   COUNT (CASE WHEN job = 'ANALYST' THEN 1 END)  AS analyst_cnt
,   COUNT (CASE WHEN job = 'CLERK'   THEN 1 END)  AS clerk_cnt
,   COUNT (CASE WHEN job = 'MANAGER' THEN 1 END)  AS manager_cnt
FROM       scott.emp
WHERE      job     IN ('ANALYST', 'CLERK', 'MANAGER')
GROUP BY   deptno
ORDER BY   deptno
;
 
 
PROMPT     ==========  1. Dynamic Pivot  ==========
 
--  *****  Start of dynamic_pivot.sql  *****
 
-- Suppress SQL*Plus features that interfere with raw output
SET FEEDBACK       OFF
SET PAGESIZE       0
 
SPOOL      p:\sql\cookbook\dynamic_pivot_subscript.sql
 
SELECT     DISTINCT
    ',      COUNT (CASE WHEN job = '''
||  job
||  ''' '   AS txt1
,   'THEN 1 END)   AS '
||  job
||  '_CNT'  AS txt2
FROM       scott.emp
ORDER BY   txt1;
 
SPOOL      OFF
 
-- Restore SQL*Plus features suppressed earlier
SET FEEDBACK       ON
SET PAGESIZE       50
 
SPOOL      p:\sql\cookbook\dynamic_pivot.lst
 
SELECT     deptno
@@dynamic_pivot_subscript
FROM       scott.emp
GROUP BY   deptno
ORDER BY   deptno
;
 
SPOOL      OFF
 
--  *****  End of dynamic_pivot.sql  *****
 
/*
EXPLANATION:
The basic pivot assumes you know the number of distinct jobs,
and the name of each one.  If you do, then writing a pivot query
is simply a matter of writing the correct number of ", COUNT ... AS ..."\
lines, with the name entered in two places on each one.  That is easily
done by a preliminary query, which uses SPOOL to write a sub-script 
(called dynamic_pivot_subscript.sql in this example).
 
The main script invokes this sub-script at the proper point.
In practice, .SQL scripts usually contain one or more complete
statements, but there's nothing that says they have to.
This one contains just a fragment from the middle of a SELECT statement.
 
Before creating the sub-script, turn off SQL*Plus features that are 
designed to help humans read the output (such as headings and 
feedback messages like "7 rows selected.", since we do not want these 
to appear in the sub-script.
Turn these features on again before running the main query.
 
*/