SQL Logic to pivot based on two columns

Question

I have a table 'AnimalInformation' and want to pivot the columns based on the purchase date like below. I have tried with pivot but not getting it exactly what I need. could you some one help me on that ? All the information provided below

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

Answer 1

Conditional Aggregation is One Way you can do it.

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

Then there is the method that you and a couple others are trying which MikkaRin got right but forgot columns names:

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

Or joining 2 pivots:

;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

Personally I feel conditional aggregation offers the best most flexible solution with the least amount of code for a case like this. I did see a fun solution using unpivot then pivot but that would require datatypes to be consistent.

Answer 2

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