Using the PIVOT operator without aggregation

Question

Using the PIVOT operator without aggregation

Navigation

#1 by (2 votes)
#2 by (1 votes)

4

I have the FAULTS table with the following columns:

CREATE TABLE [dbo].[FALTAS](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Matricula] [int] NOT NULL,
[Dia] [date] NOT NULL,
[Situacao] [varchar](50) NOT NULL,
CONSTRAINT [PK_FALTAS] PRIMARY KEY CLUSTERED 
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,       ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Data:

INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(31,'2014-06-11','FALTA');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(31,'2014-06-13','PRESENTE');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(31,'2014-06-12','FALTA');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(140,'2014-06-12','PRESENTE');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(140,'2014-06-11','PRESENTE');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(140,'2014-06-13','CURSO');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(200,'2014-06-11','CURSO');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(200,'2014-06-12','FALTA');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(200,'2014-06-13','PRESENTE');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(217,'2014-06-12','PRESENTE');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(217,'2014-06-13','FALTA');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(217,'2014-06-11','FALTA');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(223,'2014-06-11','PRESENTE');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(223,'2014-06-12','FALTA');
INSERT INTO FALTAS (Matricula,Dia,Situacao) VALUES(223,'2014-06-13','PRESENTE');

I need to show how:

Matricula   11/06/2014  12/06/2014  13/06/2014
31          FALTA       FALTA       PRESENTE
140         PRESENTE    PRESENTE    CURSO
200         CURSO       FALTA       PRESENTE
217         FALTA       PRESENTE    FALTA
223         PRESENTE    FALTA       PRESENTE

I know that the PIVOT operator changes the position of the row information to the column, but I could not find any examples without the use of aggregator (SUM, COUNT, etc.)

sql sql-server c# pivot

asked by anonymous 21.06.2014 / 19:19

2 answers

2

If your Faults table has the identifier for this line, you can use it like this:

Table Template ( Create Table )

CREATE TABLE [dbo].[Faltas](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Nome] [nvarchar](50) NULL,
    [Data] [date] NULL,
    [Local] [nvarchar](10) NULL,
    [Situacao] [nvarchar](20) NULL,
 CONSTRAINT [PK_Faltas] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

SQL example:

SELECT Nome, 
       Local, 
       [1] as [13/06/2014],
       [2] as [14/06/2014],
       [3] as [16/06/2014]
FROM Faltas
PIVOT (MAX(Situacao) for Id  in ([1],[2],[3])) p order by 1

Example: SQLFiddle / a>

22.06.2014 / 01:25

Generate Signature Pagseguro API Help in struct C, failing to feed

score 1 · Accepted Answer

Using the MOTTA Hint:

SELECT  Matricula,
    MAX( CASE Dia WHEN '2014-06-11' THEN Situacao ELSE '' END ) Dia11,
    MAX( CASE Dia WHEN '2014-06-12' THEN Situacao ELSE '' END ) Dia12,
    MAX( CASE Dia WHEN '2014-06-13' THEN Situacao ELSE '' END ) Dia13
FROM FALTAS
group by Matricula

Using the Site Tip Thinned

declare @values as nvarchar(max)
SELECT  @values = STUFF(( SELECT DISTINCT
'],[' + CONVERT(varchar(30),Dia,103)
FROM    FALTAS
ORDER BY '],[' + CONVERT(varchar(30),Dia,103)
FOR XML PATH('')
), 1, 2, '') + ']'
declare @query as nvarchar(max)
set @query = 'Select Matricula, |@
from
(
Select Matricula,CONVERT(varchar(30),Dia,103) as Dia,Situacao from FALTAS
) PT
PIVOT
( MAX(Situacao) for Dia in ( |@ )) pvt'
set @query = REPLACE(@query,'|@',@values)
exec(@query)