Tengo una tabla 'AnimalInformation' y quiero pivotar las columnas según la fecha de compra como se muestra a continuación. Lo intenté con pivot pero no obtuve exactamente lo que necesito. ¿alguien podría ayudarme con eso? Toda la información proporcionada a continuación

Result should look like 
PurchaseDate |  CatInfo| DogInfo | FishInfo |   CatDate    | DogDate    | FishDate
----------------------------------------------------------------------------------
1/1/2016     |  Good   |  Fair   |  NotGood |   10/10/2016 | 11/10/2016 | 12/10/2016
2/2/2016     |  Bad    |  Good   |  Good    |     9/9/2016 | 10/9/2016  | 11/9/2016

 ##Table##
 CREATE TABLE [dbo].[AnimalInformation](
[UniqueId] [int] IDENTITY(1,1) NOT NULL,
[PurchaseDate] [datetime] NOT NULL,
[Animal] [nvarchar](50) NOT NULL,
[HealthDate] [datetime] NOT NULL,
[AnimalCondition] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_AnimalInformation] PRIMARY KEY CLUSTERED 
(
[UniqueId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,                   
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

## Insert Statement ##
 Insert into AnimalInformation values ('1/1/2016' ,'Cat','10/10/2016','Good')
 Insert into AnimalInformation values ('1/1/2016' ,'Dog','11/10/2016','Fair')
 Insert into AnimalInformation values ('1/1/2016' ,'Fish','12/10/2016','Not Good')

Insert into AnimalInformation values ('2/2/2016' ,'Cat','9/9/2016','Bad')
Insert into AnimalInformation values ('2/2/2016' ,'Dog','10/9/2016','Good')
Insert into AnimalInformation values ('2/2/2016' ,'Fish','11/9/2016','Good')

## My Query ##
SELECT * FROM
 (
  SELECT
     PurchaseDate,
 Animal,
 AnimalCondition,
 HealthDate,
 (Animal + 'Info') AnimalHealthInfo,
 (Animal + 'Date') AnimalHealthDate
 FROM
  AnimalInformation
 ) X

 PIVOT ( MAX(AnimalCondition) FOR AnimalHealthInfo IN ([CatInfo],[DogInfo],[FishInfo] )
    ) As P1
 PIVOT ( MAX(HealthDate) FOR AnimalHealthDate IN ([CatDate],[DogDate],[FishDate])
    ) AS P2
sql
0
user3600663 25 ago. 2016 a las 18:54

2 respuestas

La mejor respuesta

La agregación condicional es una forma en que puede hacerlo.

SELECT
    PurchaseDate
    ,MAX(CASE WHEN Animal = 'Cat' THEN AnimalCondition END) as CatInfo
    ,MAX(CASE WHEN Animal = 'Dog' THEN AnimalCondition END) as DogInfo
    ,MAX(CASE WHEN Animal = 'Fish' THEN AnimalCondition END) as FishInfo
    ,MAX(CASE WHEN Animal = 'Cat' THEN HealthDate END) as CatDate
    ,MAX(CASE WHEN Animal = 'Dog' THEN HealthDate END) as DogDate
    ,MAX(CASE WHEN Animal = 'Fish' THEN HealthDate END) as FishDate
FROM
    AnimalInformation
GROUP BY
    PurchaseDate

Luego está el método que usted y un par de personas más están probando, que MikkaRin acertó pero olvidó los nombres de las columnas:

SELECT
    PurchaseDate
    ,MAX(CatI) as CatInfo
    ,MAX(DogI) as DogInfo
    ,MAX(FishI) as FishInfo
    ,MAX(CatD) as CatDate
    ,MAX(DogD) as DogDate
    ,MAX(FishD) as FishDate
FROM
       (SELECT
          PurchaseDate
          ,HealthDate
          ,AnimalCondition
          ,Animal + 'I' as AnimalInfo
          ,Animal + 'D' as AnimalDate
       FROM
          AnimalInformation) t
       PIVOT
       (
          MAX(AnimalCondition)
          FOR AnimalInfo IN (CatI,DogI,FishI)
       ) p
       PIVOT
       (
          MAX(HealthDate)
          FOR AnimalDate IN (CatD,DogD,FishD)
       ) p2
GROUP BY
    PurchaseDate

O uniendo 2 pivotes:

;WITH cteHealthInfo AS (
    SELECT PurchaseDate, Cat as CatInfo, Dog as DogInfo, Fish as FishInfo
    FROM
       (SELECT PurchaseDate, AnimalCondition, Animal
       FROM
          AnimalInformation) t
       PIVOT
       (
          MAX(AnimalCondition)
          FOR Animal IN (Cat,Dog,Fish)
       ) p
)

, cteHealthDate AS (
    SELECT PurchaseDate, Cat as CatDate, Dog as DogDate, Fish as FishDate
    FROM
       (SELECT PurchaseDate, HealthDate, Animal
       FROM
          AnimalInformation) t
       PIVOT
       (
          MAX(HealthDate)
          FOR Animal IN (Cat,Dog,Fish)
       ) p
)

SELECT
    i.*
    ,d.CatDate
    ,d.DogDate
    ,d.FishDate
FROM
    cteHealthInfo i
    INNER JOIN cteHealthDate d
    ON i.PurchaseDate = d.PurchaseDate

Personalmente, creo que la agregación condicional ofrece la mejor solución más flexible con la menor cantidad de código para un caso como este. Vi una solución divertida usando univot y luego un pivote, pero eso requeriría que los tipos de datos fueran consistentes.

1
Matt 25 ago. 2016 a las 16:28
SELECT PurchaseDate, 
max([CatInfo]),
max([DogInfo]),
max([FishInfo]),
max([CatDate]) ,
max([DogDate]) ,
max([FishDate])
 FROM
 (
  SELECT
     PurchaseDate,
 --Animal,
 AnimalCondition,
 HealthDate,
 (Animal + 'Info') AnimalHealthInfo,
 (Animal + 'Date') AnimalHealthDate
 FROM
  @AnimalInformation
 ) X

 PIVOT ( MAX(AnimalCondition) FOR AnimalHealthInfo IN ([CatInfo],[DogInfo],[FishInfo] )
    ) As P1
 PIVOT ( MAX(HealthDate) FOR AnimalHealthDate IN ([CatDate],[DogDate],[FishDate])
    ) AS P2

    group by PurchaseDate
1
MikkaRin 25 ago. 2016 a las 16:08