1

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.

Improve this question
1
  • What version of SQL Server, and what have you tried? Commented Nov 3, 2009 at 19:32

4 Answers 4

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

2 Comments

I will not know what my Flags are as end user has ability to add, update or delete flags and make association with any class they want (e.g. user can add a new flag say F101 or rename existing flag F1 to F01). I'm using SQL Express 2005
Here's a nasty hack: If you have the ability to generate SQL at runtime, you can query your table for all the available Flag names, then generate the SQL statement with the columns (Sum(Case When Flags...) automatically generated from code. That makes me feel dirty thinking about it, but it would work if you had code to work with.
0

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)
Improve this answer

Comments

0 
SELECT classname ,description ,CASE WHEN flags = 'F1' THEN 'T' ELSE 'F' END f1 ,CASE WHEN flags = 'F2' THEN 'T' ELSE 'F' END f2 ...
Improve this answer

1 Comment

Your answer could be improved by adding more information on what the code does and how it helps the OP.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.