Home > DATA BASE, SQL Server > SQL Server 2005 [dev] – Tutorial per concetto e istruzione PIVOT

SQL Server 2005 [dev] – Tutorial per concetto e istruzione PIVOT

Istruzione PIVOT introdotta dalla versione SQL Server 2005.

  • Step-1: Creazione tabelle
  • Step-2: Inserimento dati
  • Step-3: Creazione viste e stored procedure per estrazioni PIVOT
  • NOTA: Esempio di Query SENZA istruzione PIVOT

Step-1: Creazione tabelle

/****** Object:  Table [dbo].[AnagContatti]    ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 SET ANSI_PADDING ON
 GO
 CREATE TABLE [dbo].[AnagContatti](
 [id] [int] NOT NULL,
 [Nome] [varchar](50) NOT NULL,
 [Cognome] [varchar](50) NOT NULL,
 [Note] [varchar](50) NULL,
 [DataNascita] [datetime] NULL,
 [StatoNascita] [varchar](50) NULL,
 CONSTRAINT [PK_AnagContatti] PRIMARY KEY CLUSTERED
 (
 [id] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY]
GO
 SET ANSI_PADDING OFF
/****** Object:  Table [dbo].[AnagLingue]    ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 SET ANSI_PADDING ON
 GO
 CREATE TABLE [dbo].[AnagLingue](
 [CodLingua] [char](6) NOT NULL,
 [Lingua] [varchar](50) NOT NULL,
 [Note] [varchar](50) NULL,
 CONSTRAINT [PK_AnagLingue] PRIMARY KEY CLUSTERED
 (
 [CodLingua] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY]
GO
 SET ANSI_PADDING OFF
/****** Object:  Table [dbo].[ContattiLingue_relMM]   ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 SET ANSI_PADDING ON
 GO
 CREATE TABLE [dbo].[ContattiLingue_relMM](
 [idContatto] [int] NOT NULL,
 [codLingua] [char](6) NOT NULL,
 CONSTRAINT [PK_ContattiLingue_relMM] PRIMARY KEY CLUSTERED
 (
 [idContatto] ASC,
 [codLingua] ASC
 ) ON [PRIMARY]
 ) ON [PRIMARY]
GO
 SET ANSI_PADDING OFF

Step-2: Inserimento dati

/******  Caricamento dati  in tab. AnagContatti ******/
 INSERT AnagContatti(id,Nome,Cognome,Note,DataNascita,StatoNascita) VALUES('1','Nicola','F','noteNF',convert(datetime,NULL,121),'Francia')
 INSERT AnagContatti(id,Nome,Cognome,Note,DataNascita,StatoNascita) VALUES('2','Giorgio','G','noteGG',convert(datetime,NULL,121),'Italia')
 INSERT AnagContatti(id,Nome,Cognome,Note,DataNascita,StatoNascita) VALUES('3','Giancarlo','F','noteGF',convert(datetime,NULL,121),'Italia')
/******  Caricamento dati  in tab. AnagLingue ******/
 INSERT AnagLingue(CodLingua,Lingua,Note) VALUES('ENG   ','Inglese','-')
 INSERT AnagLingue(CodLingua,Lingua,Note) VALUES('FR    ','Francese','-')
 INSERT AnagLingue(CodLingua,Lingua,Note) VALUES('GR    ','Greco','-')
 INSERT AnagLingue(CodLingua,Lingua,Note) VALUES('ITA   ','Italiano','-')
/******  Caricamento dati  in tab. ContattiLingue_relMM]******/
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('1','ENG   ')
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('1','GR    ')
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('1','ITA   ')
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('2','ENG   ')
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('2','ITA   ')
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('3','FR    ')
 INSERT ContattiLingue_relMM(idContatto,codLingua) VALUES('3','ITA   ')

Step-3: Creazione viste e stored procedure per estrazioni PIVOT

/****** Object:  View [dbo].[v_ContattiLingue]     ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE VIEW [dbo].[v_ContattiLingue]
 AS
 --
 -- Vista Dati (piatta)
 -- Utilizzo: select * from [v_ContattiLingue]
 --
 SELECT  AnagContatti.Nome,
 AnagContatti.Cognome,
 AnagContatti.Note AS NoteContatto,
 AnagLingue.CodLingua,
 ISNULL(AnagLingue.Lingua, '-n.d.-') Lingua,
 ISNULL(AnagLingue.Note, '-n.d.-') NoteLingua
 FROM    AnagContatti
 INNER JOIN ContattiLingue_relMM ON AnagContatti.id = ContattiLingue_relMM.idContatto
 LEFT JOIN AnagLingue ON ContattiLingue_relMM.CodLingua = AnagLingue.CodLingua
GO
/****** Object:  View [dbo].[v_PIVOT_ContattiLingue]    ******/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER ON
 GO
 CREATE VIEW [dbo].[v_PIVOT_ContattiLingue]
 AS
 --
 -- Vista PIVOT
 -- Utilizzo: select * from [v_PIVOT_ContattiLingue]
 --
 SELECT Nome AS Nome,
 [ITA], [ENG], [FR], [GR]
 FROM
 (SELECT Nome, CodLingua
 FROM v_ContattiLingue) AS SourceTable
 PIVOT
 (
 COUNT(CodLingua)
 FOR CodLingua IN ([ENG], [ITA], [FR], [GR])
 ) AS PivotTable
/****** Object:  StoredProcedure [dbo].[usp_PIVOTdinam_ContattiLingue]   *****/
 SET ANSI_NULLS ON
 GO
 SET QUOTED_IDENTIFIER OFF
 GO
CREATE  PROC [dbo].[usp_PIVOTdinam_ContattiLingue]
 AS
 --
 -- Vista PIVOT Dinamica
 -- Utilizzo: exec usp_PIVOTdinam_ContattiLingue
 --
declare @q nvarchar(max);
 declare @s varchar(max)
-- prepara Colonne dinamiche:
 -- Crea una stringa di valori separati da virgola
SET @s=''
 select @s = @s + RTRIM(CodLingua) + ',' from AnagLingue
 set @s = substring(@s, 1, len(@s)-1)
 --print 'stringaValori' + @s
----
 ---- -- prepara QUERY dinamica (classica)
 ---- SET @q=N'
 ---- SELECT Nome AS Nome,
 ----        '+@s+'
 ---- FROM
 ----     (SELECT Nome, CodLingua
 ----     FROM v_ContattiLingue) AS SourceTable
 ----     PIVOT
 ----     (
 ----     COUNT(CodLingua)
 ----     FOR CodLingua IN ('+@s+')
 ----     ) AS PivotTable'
-- prepara QUERY dinamica
 -- Ottimizzata con CTE (Common Table Expression)
 SET @q=N''
 SET @q=N'
 with CTE as
 (SELECT Nome, CodLingua FROM v_ContattiLingue)
SELECT Nome AS Nome,  '+@s+'
 FROM
 CTE PIVOT
 (
 COUNT(CodLingua)
 FOR CodLingua IN ('+@s+')
 ) AS PivotTable'
--print 'QUERY='+@q
EXECUTE sp_executesql @q

NOTA: Esempio di Query SENZA istruzione PIVOT

SELECT Nome,
MAX(CASE CodLingua WHEN 'ITA' THEN cnt ELSE 0 END)  AS ITA,
MAX(CASE CodLingua WHEN 'ENG' THEN cnt ELSE 0 END)  AS ENG,
MAX(CASE CodLingua WHEN 'FR'  THEN cnt ELSE 0 END)  AS FR,
MAX(CASE CodLingua WHEN 'GR'  THEN cnt ELSE 0 END)  AS GR
FROM --v_ContattiLingue
(SELECT Nome, CodLingua, COUNT(*) as cnt   
FROM v_ContattiLingue
GROUP BY Nome, CodLingua
) Origine
GROUP BY Nome
Annunci
  1. 31/05/2012 alle 08:21

    I got this web page from my friend who informed me regarding this website
    and at the moment this time I am browsing this
    web site and reading very informative content at this place.

  1. No trackbacks yet.

Rispondi

Effettua il login con uno di questi metodi per inviare il tuo commento:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: