SQL Pivot Table

Question

I have a SQL view with following data:

ID   ClassName    Description   Flags
1    Class1         Desc1        F1
2    Class1         Desc1        F2
3    Class1         Desc1        F3
4    Class1         Desc1        F4
5    Class2         Desc2        F2
6    Class2         Desc2        F6
7    Class3         Desc3        F1
8    Class4         Desc4        F8

I want result something like this:

ClassName    Description    F1   F2  F3  F4  F6   F8
Class1         Desc1         T    T  T    T   F   F
Class2         Desc2         F    T  F    F   T   F
Class3         Desc3         T    F  F    F   F   F
Class4         Desc4         F    F  F    F   F   T

I have tried to look up for Pivot examples but all of them are either for SUM or other aggregate functions. Not sure if any of that will work as I'm not doing either.

Answer 1

select classname, description, case when flags = 'F1' then 'T' else 'F' end F1, case when flags = 'F2' then 'T' else 'F' end F2, ...

Answer 2

I believe MS SQL Server supports pivots tables, but I'm not sure exactly how to do them. When I have to pivot data in MySQL, I use Sum and Case. However, that only works when you know what your column names are going to be in advance. Here's how I might do it:

Select
X.ClassName,
X.Description,
Case When X.F1 = 1 Then 'T' Else 'F' End As `F1`,
Case When X.F2 = 1 Then 'T' Else 'F' End As `F2`
/* etc. for the rest of your Flags*/
FROM (
    Select
    ClassName,
    Description,
    Sum(Case When Flags = 'F1' Then 1 Else 0 End) As `F1`,
    Sum(Case When Flags = 'F2' Then 1 Else 0 End) As `F2`
    /* etc. for the rest of your Flags*/
    From 
    ClassTable
    Group By
    ClassTable.ClassName
    ) X

In the above code, the subquery will produce output like what you wanted, except that you'll get 1's and 0's (assuming you never repeat a flag for a class). The "main" query at the top of the statement simply turns the 1's and 0's into T's and F's.

Again, this requires you to know what your column names will be, but it's the only way I know how to do it without "PIVOT" being built in to the SQL language you are using. MS SQL might have a PIVOT built-in, so you might want to dig to find that.

Answer 3

Henry Fao is on the right track - but you have to "flatten" the rows with a group by.

select ClassName as ClassName, Description as Description 
   , coalesce(max(F1), 'F') as F1
   , coalesce(max(F2), 'F') as F2
   , etc
 from (
        select classname, description
           , case when flags = 'F1' then 'T' else null end as F1
           , case when flags = 'F2' then 'T' else null end as F2
           etc.
        from tbl
       ) as t
  group by ClassName, Description

There should only be one 'T' in the F1 column of each group. The other rows will have a null in the T1 column. The max() function ignores nulls and will return the 'T' - if there is one.

Answer 4

This is not pretty and I generally don't like dynamically created SQL, but with an unknown list of flags, I'm not really sure how else you would do it. Additionally, you're right in that the PIVOT command expects an aggregate so I just used MAX. Yes, this is kind of hackish, but it does get the job done and will scale as new flags are added. I think this should work on 2005/2008 versions of SQL-SERVER. I'm not sure about 2003 as I don't know if it has the XML PATH command.

Declare @ColumnsIn varchar(max)
Declare @ColumnsIsNull varchar(max)
Declare @sql varchar(max)

Select @ColumnsIn = Stuff((Select Distinct ',[' + Flags + ']' From Classes For XML PATH('')),1,1,'')
Select @ColumnsIsNull = Stuff((Select Distinct ', IsNull([' + Flags + '], ''F'') as [' + Flags + ']' From Classes For XML PATH('')),1,1,'')

Set @sql = '
    Select 
     ClassName,
     Description,
     ' + @ColumnsIsNull + '
    FROM
    (
     Select
      Classes.ClassName,
      Classes.Description,
      Classes.Flags,
      ''T'' as HasFlag
     From
      Classes
    ) as Sub1
    Pivot (Max(HasFlag) For Flags in (' + @ColumnsIn + ')) as Sub2'

Execute(@sql)