Newsgroup: it.comp.software.database
Titolo: SQL Server - query a campi incrociati

avrei bisogno di riprodurre su SQL Server una query a campi incrociati
che fino ad oggi era su Access.
Qualcuno mi puņ aiutare?
-- 
Posted via Mailgate.ORG Server - 
http://www.Mailgate.ORG

 Qualcuno mi puņ aiutare?
Ciao Roberto,
SQL Server non ha nessuna estensione per gestire il "pivoting"
(forse nella prossima versione...)
Nel frattempo esistono svariate tecniche che risolvono il
problema con SQL ANSI.
Nei Books Online al capitolo:
Accessing and Changing Relational Data Overview
 Advanced Query Concepts
  Transact-SQL Tips
   Cross-Tab Reports
c'e' un esempio che utilizza le sub-query, altrimenti di
seguito di faccio il cut&paste di un esempio che ho preparato
qualche tempo fa'.
Ciao,
--
Gianluca Hotz
Backoffice MVP (SQL Server) MCP SQL Server and MCP Windows
Vice-President of the Italian User Group for SQL Server
Founding Member of the Italian User Group for .NET
http://www.ghotz.com
http://www.ugiss.org
--
/* CUT HERE */
CREATE TABLE dbo.Libro (
 CodLibro int  NOT NULL,
 NomeLibro varchar(900) NOT NULL,
 CONSTRAINT pk_libro
 PRIMARY KEY (CodLibro),
 CONSTRAINT ak_libro_01
 UNIQUE  (NomeLibro)
)
GO
/*
   Nota: situazione peggiore dove il valore della chiave libro puo'
   essere assegnato a piu' codici libri, se ogni valore e' distinto
   la tabella e' differente: chiave primaria solo su ChiaveLibro
*/
CREATE TABLE dbo.ChiaviLibro (
 CodLibro int NOT NULL,
 ChiaveLibro int NOT NULL,
 CONSTRAINT pk_ChiaviLibro
 PRIMARY KEY (CodLibro, ChiaveLibro),
 CONSTRAINT fk_ChiaviLibroToLibro
 FOREIGN KEY (CodLibro)
 REFERENCES dbo.Libro (CodLibro)
)
GO
-- Dati di prova
INSERT dbo.Libro VALUES (1, 'Hobbit')
INSERT dbo.Libro VALUES (2, 'Il signore degli anelli')
INSERT dbo.Libro VALUES (3, 'Silmarillion')
INSERT dbo.ChiaviLibro VALUES (1, 3434)
INSERT dbo.ChiaviLibro VALUES (1, 2237)
INSERT dbo.ChiaviLibro VALUES (1, 9965)
INSERT dbo.ChiaviLibro VALUES (2, 3346)
INSERT dbo.ChiaviLibro VALUES (2, 1133)
INSERT dbo.ChiaviLibro VALUES (2, 7565)
INSERT dbo.ChiaviLibro VALUES (2, 3452)
INSERT dbo.ChiaviLibro VALUES (2, 2321)
INSERT dbo.ChiaviLibro VALUES (3, 2323)
INSERT dbo.ChiaviLibro VALUES (3, 1642)
GO
-- SOluzione 1: subquery
CREATE PROCEDURE dbo.sp_PivotLibro1
 @CodLibro int
AS
SELECT CodLibro,
 IDENTITY(tinyint, 1, 1) AS SeqChiave,
 ChiaveLibro
INTO #tmp
FROM dbo.ChiaviLibro
WHERE CodLibro = @CodLibro
SELECT L.CodLibro, L.NomeLibro,
 (SELECT C1.ChiaveLibro
  FROM #tmp AS C1
  WHERE C1.CodLibro = L.CodLibro AND
  C1.SeqChiave = 1
 ) AS Chiave1,
 (SELECT C2.ChiaveLibro
  FROM #tmp AS C2
  WHERE C2.CodLibro = L.CodLibro AND
  C2.SeqChiave = 2
 ) AS Chiave2,
 (SELECT C3.ChiaveLibro
  FROM #tmp AS C3
  WHERE C3.CodLibro = L.CodLibro AND
  C3.SeqChiave = 3
 ) AS Chiave3,
 (SELECT C4.ChiaveLibro
  FROM #tmp AS C4
  WHERE C4.CodLibro = L.CodLibro AND
  C4.SeqChiave = 4
 ) AS Chiave4,
 (SELECT C5.ChiaveLibro
  FROM #tmp AS C5
  WHERE C5.CodLibro = L.CodLibro AND
  C5.SeqChiave = 5
 ) AS Chiave5
FROM dbo.Libro AS L
WHERE L.CodLibro = @CodLibro
GO
EXEC dbo.sp_PivotLibro1 1
GO
-- SOluzione 2: prodotto cartesiano
CREATE PROCEDURE dbo.sp_PivotLibro2
 @CodLibro int
AS
SELECT CodLibro,
 IDENTITY(tinyint, 1, 1) AS SeqChiave,
 ChiaveLibro
INTO #tmp
FROM dbo.ChiaviLibro
WHERE CodLibro = @CodLibro
CREATE TABLE #mtx (
 MtxKey1   int NOT NULL,
 MtxKey2   int NOT NULL,
 MtxKey3   int NOT NULL,
 MtxKey4   int NOT NULL,
 MtxKey5   int NOT NULL
)
INSERT #mtx VALUES (1, 0, 0, 0, 0)
INSERT #mtx VALUES (0, 1, 0, 0, 0)
INSERT #mtx VALUES (0, 0, 1, 0, 0)
INSERT #mtx VALUES (0, 0, 0, 1, 0)
INSERT #mtx VALUES (0, 0, 0, 0, 1)
SELECT L.CodLibro, L.NomeLibro,
 MIN(CASE WHEN K.SeqChiave = 1 AND M.MtxKey1 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave1,
 MIN(CASE WHEN K.SeqChiave = 2 AND M.MtxKey2 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave2,
 MIN(CASE WHEN K.SeqChiave = 3 AND M.MtxKey3 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave3,
 MIN(CASE WHEN K.SeqChiave = 4 AND M.MtxKey4 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave4,
 MIN(CASE WHEN K.SeqChiave = 5 AND M.MtxKey5 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave5
FROM dbo.Libro AS L
 JOIN #tmp AS K
 ON L.CodLibro = K.CodLibro
 CROSS JOIN #mtx AS M
WHERE L.CodLibro = @CodLibro
GROUP BY
 L.CodLibro, L.NomeLibro
GO
EXEC dbo.sp_PivotLibro2 1
GO
/*
   Come avrai notato va tutto bene se devi estrarre solo un libro
   alla volta, se vuoi estrarli tutti non funziona perche' quando
   crei la tabella temporanea con la funzioen IDENTITY() il
   progressivo non rinizia da 1 per ogni codice libro.
   Se hai il progressivo della chiave direttamente nella tabella
   e' molto piu' semplice.
*/
DROP TABLE dbo.ChiaviLibro
GO
CREATE TABLE dbo.ChiaviLibro (
 CodLibro int NOT NULL,
 SeqChiave int NOT NULL,
 ChiaveLibro int NOT NULL,
 CONSTRAINT pk_ChiaviLibro
 PRIMARY KEY (CodLibro, SeqChiave),
 CONSTRAINT ak_ChiaviLibro_01
 UNIQUE  (CodLibro, ChiaveLibro),
 CONSTRAINT fk_ChiaviLibroToLibro
 FOREIGN KEY (CodLibro)
 REFERENCES dbo.Libro (CodLibro)
)
GO
-- Dati di prova
INSERT dbo.ChiaviLibro VALUES (1, 1, 3434)
INSERT dbo.ChiaviLibro VALUES (1, 2, 2237)
INSERT dbo.ChiaviLibro VALUES (1, 3, 9965)
INSERT dbo.ChiaviLibro VALUES (2, 1, 3346)
INSERT dbo.ChiaviLibro VALUES (2, 2, 1133)
INSERT dbo.ChiaviLibro VALUES (2, 3, 7565)
INSERT dbo.ChiaviLibro VALUES (2, 4, 3452)
INSERT dbo.ChiaviLibro VALUES (2, 5, 2321)
INSERT dbo.ChiaviLibro VALUES (3, 1, 2323)
INSERT dbo.ChiaviLibro VALUES (3, 2, 1642)
GO
-- SOluzione 1: subquery
CREATE PROCEDURE dbo.sp_PivotLibro3
AS
SELECT L.CodLibro, L.NomeLibro,
 (SELECT C1.ChiaveLibro
  FROM dbo.ChiaviLibro AS C1
  WHERE C1.CodLibro = L.CodLibro AND
  C1.SeqChiave = 1
 ) AS Chiave1,
 (SELECT C2.ChiaveLibro
  FROM dbo.ChiaviLibro AS C2
  WHERE C2.CodLibro = L.CodLibro AND
  C2.SeqChiave = 2
 ) AS Chiave2,
 (SELECT C3.ChiaveLibro
  FROM dbo.ChiaviLibro AS C3
  WHERE C3.CodLibro = L.CodLibro AND
  C3.SeqChiave = 3
 ) AS Chiave3,
 (SELECT C4.ChiaveLibro
  FROM dbo.ChiaviLibro AS C4
  WHERE C4.CodLibro = L.CodLibro AND
  C4.SeqChiave = 4
 ) AS Chiave4,
 (SELECT C5.ChiaveLibro
  FROM dbo.ChiaviLibro AS C5
  WHERE C5.CodLibro = L.CodLibro AND
  C5.SeqChiave = 5
 ) AS Chiave5
FROM dbo.Libro AS L
GO
EXEC dbo.sp_PivotLibro3
GO
-- SOluzione 2: prodotto cartesiano
CREATE PROCEDURE dbo.sp_PivotLibro4
AS
CREATE TABLE #mtx (
 SeqChiave int NOT NULL,
 MtxKey1   int NOT NULL,
 MtxKey2   int NOT NULL,
 MtxKey3   int NOT NULL,
 MtxKey4   int NOT NULL,
 MtxKey5   int NOT NULL
)
INSERT #mtx VALUES (1, 1, 0, 0, 0, 0)
INSERT #mtx VALUES (2, 0, 1, 0, 0, 0)
INSERT #mtx VALUES (3, 0, 0, 1, 0, 0)
INSERT #mtx VALUES (4, 0, 0, 0, 1, 0)
INSERT #mtx VALUES (5, 0, 0, 0, 0, 1)
SELECT L.CodLibro, L.NomeLibro,
 MIN(CASE WHEN K.SeqChiave = 1 AND M.MtxKey1 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave1,
 MIN(CASE WHEN K.SeqChiave = 2 AND M.MtxKey2 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave2,
 MIN(CASE WHEN K.SeqChiave = 3 AND M.MtxKey3 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave3,
 MIN(CASE WHEN K.SeqChiave = 4 AND M.MtxKey4 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave4,
 MIN(CASE WHEN K.SeqChiave = 5 AND M.MtxKey5 = 1 THEN K.ChiaveLibro ELSE
NULL END) AS Chiave5
FROM dbo.Libro AS L
 JOIN dbo.ChiaviLibro AS K
 ON L.CodLibro = K.CodLibro
 JOIN #mtx AS M
 ON K.SeqChiave = M.SeqChiave
GROUP BY
 L.CodLibro, L.NomeLibro
GO
EXEC dbo.sp_PivotLibro4
GO