J'ai une table comme montré ci-dessous avec les détails:
CREATE TABLE testrf ( cola INTEGER, colb VARCHAR(10) )
Insérer des données:
INSERT INTO testrf VALUES(1,'x') INSERT INTO testrf VALUES(1,'x') INSERT INTO testrf VALUES(2,'x') INSERT INTO testrf VALUES(3,'y') INSERT INTO testrf VALUES(4,'y') INSERT INTO testrf VALUES(5,'c') INSERT INTO testrf VALUES(6,'c') INSERT INTO testrf VALUES(7,'c') INSERT INTO testrf VALUES(8,'d') INSERT INTO testrf VALUES(3,'y') INSERT INTO testrf VALUES(12,'M1') INSERT INTO testrf VALUES(13,'L1') INSERT INTO testrf VALUES(14,'C2') INSERT INTO testrf VALUES(1,'c') INSERT INTO testrf VALUES(1,'d') INSERT INTO testrf VALUES(1,'L1') SELECT * FROM testrf; cola colb ------------ 1 x 1 x 2 x 3 y 4 y 5 c 6 c 7 c 8 d 3 y 1 c 1 d 12 M1 13 L1 14 C2 1 L1
Maintenant, je veux montrer le tableau croisé dynamic pour datatables ci-dessus pour lesquelles j'ai écrit la requête:
DECLARE @cols NVARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) SELECT @cols = STUFF ( (SELECT DISTINCT '],[' + v.colb FROM testrf AS v FOR XML PATH('')), 1, 2, '') + ']' SET @SQL = N'SELECT cola,TotalGroups,AvailableIn,'+ @cols +' FROM (SELECT v.cola,v.colb,(select count(distinct colb) from testrf) TotalGroups,c.AvailableIn FROM testrf AS v inner join (select cola,count(case when colb>=1 then 1 else 0 end) AS AvailableIn FROM testrf group by cola) c on c.cola = v.cola ) p PIVOT ( count(colb) FOR colb IN ( '+ @cols + ' ) ) AS pvt'; EXEC(@SQL)
Je vais avoir ceci:
cola TotalGroups AvailableIn c C2 d L1 M1 xy ---------------------------------------------------------- 1 7 5 1 0 1 1 0 2 0 2 7 1 0 0 0 0 0 1 0 3 7 2 0 0 0 0 0 0 2 4 7 1 0 0 0 0 0 0 1 5 7 1 1 0 0 0 0 0 0 6 7 1 1 0 0 0 0 0 0 7 7 1 1 0 0 0 0 0 0 8 7 1 0 0 1 0 0 0 0 12 7 1 0 0 0 0 1 0 0 13 7 1 0 0 0 1 0 0 0 14 7 1 0 1 0 0 0 0 0
Note : Veuillez noter les row number 1 and 3
. Dans AvailableIn
, les valeurs de colonnes sont 5 pour la 1ère ligne où les valeurs disponibles pour les colonnes sont 4 seulement c,d,L1,x
rest de zéros. Et aussi la ligne numéro 3 où AvailableIn
est 2 où les valeurs disponibles pour les colonnes est seulement 1 qui est y
. Je pense suis coincé dans aggregate function
dans la pivot query
.
SQL Fiddle -> http://sqlfiddle.com/#!3/d1acc/8
Plutôt que de countr si colb
est supérieur à 1 (toutes vos lettres sont), vous voulez une list distincte (comme vous avez dans la requête parent) de colb
Voici le seul changement que j'ai fait:
(select cola,count(distinct colb) AS AvailableIn FROM testrf
Violon
Êtes-vous sûr qu'il y a une question?
INSERT INTO testrf VALUES(1,'x'); INSERT INTO testrf VALUES(1,'x'); ... INSERT INTO testrf VALUES(1,'c'); INSERT INTO testrf VALUES(1,'d'); INSERT INTO testrf VALUES(1,'L1'); That is 5 references to ColA = 1 c C2 d L1 M1 xy 1 0 1 1 0 2 0 << adds to 5
Il y a quelques recommandations que je voudrais append à votre requête
COUNT () ajoute un pour chaque valeur non nulle, n'utilisez donc pas else 0
dans le count
DECLARE @cols NVARCHAR(MAX), @SQL NVARCHAR(MAX) SELECT @cols = STUFF ( (SELECT DISTINCT ',' + QUOTENAME(v.colb) FROM testrf AS v FOR XML PATH('')), 1, 1, '') SET @SQL = N'SELECT cola,TotalGroups,AvailableIn,'+ @cols +' FROM (SELECT v.cola,v.colb,(select count(distinct colb) from testrf) TotalGroups,c.AvailableIn FROM testrf AS v inner join (select cola,count(case when colb>=''1'' then 1 end) AS AvailableIn FROM testrf group by cola) c on c.cola = v.cola ) p PIVOT ( count(colb) FOR colb IN ( '+ @cols + ' ) ) AS pvt' EXEC(@SQL)