user3600663 user3600663 - 3 months ago 8
SQL Question

SQL Logic to pivot based on two columns

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

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 cast like this. I did see a fun solution using unpivot then pivot but that would require datatypes to be consistent.

Comments