T-SQL How to Pivot on the Values of a Table? How do you pivot a table on the value in the field? I have to admit that while I can write some pretty nifty queries, have my CTEs down, and enjoy a wide knowledge of T-SQL programming, Pivots get into a wonky area that I am uncomfortable and unfamiliar with.

Let me describe my issue, and see if you can help.

I have the following tables:

CREATE TABLE [dbo].[Itinerary]
(
   [ItineraryID] [int] IDENTITY(1,1) NOT NULL primary key,
   [RoadTripID] [int] not NULL,
   [ItineraryDate] [date] not NULL,
   EstimatedMiles int not null,
   Destination varchar(64) not null,
   DestinationState varchar(2) not null,
   DayTypeID int not null
)

Create Table dbo.DayType 
(
   DayTypeID int primary key identity,
   DayTypeCode varchar(2) not null, 
   DayTypeDescription varchar(32) not null 
)


DayType is a helper table with nine values:

Insert into DayType
values 
  ('XX', 'Planning/Prep'),
  ('SE', 'Short Event'),
  ('LE', 'Long Event'),
  ('WD', 'Writing Day'),
  ('TD', 'Tech Day'),
  ('HD', 'Hiking Day'),
  ('PD', 'Photography Day'),
  ('LD', 'Layover Day'),
  ('LH', 'Long Haul')


When I enter a row into Itinerary it looks like this:

insert into itinerary 
values(1, '11/27/2014', 361, 'Amarillo', 'TX', 1)


The last piece of the puzzle (and the question itself) is that I want to report out on how many of each type of day I've planned.

I am currently using the tried-and-true-(and awful) method:

Select 
   s.ItineraryID, R.ShortName as RoadTripName, 
   sum(EstimatedMiles) as TotalMiles, 
   count(itineraryid) as TotalDays, 
   (select count() from Itinerary i1 with (nolock) where daytypeid = 1 and i1.itineraryid = s.itineraryid) as 'Planning/Prep',
   (select count() from Itinerary i2 with (nolock) where daytypeid = 2 and i2.itineraryid = s.itineraryid) as 'Short Event',
   (select count() from Itinerary i3 with (nolock) where daytypeid = 3 and i3.itineraryid = s.itineraryid) as 'Long Event',
   (select count() from Itinerary i4 with (nolock) where daytypeid = 4 and i4.itineraryid = s.itineraryid) as 'Writing Day',
   (select count() from Itinerary i5 with (nolock) where daytypeid = 5 and i5.itineraryid = s.itineraryid) as 'Tech Day',
   (select count() from Itinerary i6 with (nolock) where daytypeid = 6 and i6.itineraryid = s.itineraryid) as 'Hiking Day',
   (select count() from Itinerary i7 with (nolock) where daytypeid = 7 and i7.itineraryid = s.itineraryid) as 'Photography Day',
   (select count() from Itinerary i8 with (nolock) where daytypeid = 8 and i8.itineraryid = s.itineraryid) as 'Layover Day',
   (select count(*) from Itinerary i9 with (nolock) where daytypeid = 9 and i9.itineraryid = s.itineraryid) as 'Long Haul'
from 
   RoadTrip r 
inner join 
   Itinerary s on r.roadtripid = s.roadtripid
group by 
   s.itineraryid, r.shortname


which returns:



Obviously (hopefully!) there needs to be a better way. How can I pivot those nine values, and their total count each into this query?
Try this Query;

Select s.ItineraryID, R.ShortName as RoadTripName,  sum(EstimatedMiles) as TotalMiles, count(itineraryid) as TotalDays,
SUM(case DayType.DayTypeCode when 'XX' then 1 else 0 END ) as 'Planning/Prep',
SUM(case DayType.DayTypeCode when 'SE' then 1 else 0 END ) as 'Short Event',
SUM(case DayType.DayTypeCode when 'LE' then 1 else 0 END ) as 'Long Event',
SUM(case DayType.DayTypeCode when 'WD' then 1 else 0 END ) as 'Writing Day',
SUM(case DayType.DayTypeCode when 'TD' then 1 else 0 END ) as 'Tech Day',
SUM(case DayType.DayTypeCode when 'HD' then 1 else 0 END ) as 'Hiking Day',
SUM(case DayType.DayTypeCode when 'PD' then 1 else 0 END ) as 'Photography Day',
SUM(case DayType.DayTypeCode when 'LD' then 1 else 0 END ) as 'Layover Day' 
from  RoadTrip r inner join Itinerary s on r.roadtripid = s.roadtripid
  inner join DayType on s.DayTypeId = DayType.DayTypeId
group by s.ItineraryID,  R.ShortName 


Or another way using Pivot

Select ItineraryID, RoadTripName, EstimatedMiles,[XX] as 'Planning/Prep',[SE] as 'Short Event',[LE]  as 'Long Event',[WD] as 'Writing Day',[TD] as 'Tech Day',[HD] as 'Hiking Day',[PD] as 'Photography Day',[LD] as 'Layover Day' 
FROM 
(SELECT i.ItineraryID, r.RoadTripName,  dt.DayTypeCode, EstimatedMiles, 1 as daycount
    FROM RoadTrip r 
    inner join Itinerary i on r.roadtripid = i.roadtripid
     --Itinerary i 
    inner join DayType dt on dt.DayTypeId = i.DayTypeId
) ps
PIVOT
(
COUNT(dayCount)
FOR DayTypeCode IN ( [XX],[SE],[LE],[WD],[TD],[HD],[PD],[LD])
) as Pvt