These type of queries are easier to make with an aim of GROUP BY, like this: Select case when profile.foo ~* ‘5.0.2195’ then ‘Win2k’ when profile.foo ~* ‘5.1.2600’ then ‘WinXP’ when profile.foo ~* ‘5.2.3790’ then ‘W2k3’ when (profile.foo ~* ‘6.0.6000’ or profile.foo ~* ‘6.0.6001’ or profile.foo ~* ‘6.0.6002’) then ‘Vista’ when (profile.foo ~* ‘6.1.7600’ or … Read more
I usually use dynamic sql. Something like this create table #T ( ID int, aText1 varchar(4), aText2 varchar(4), aInt1 int, aInt2 int ) insert into #T select 1, ‘ABC1’, ‘XYZ1’, 2, 20 union select 2, ‘ABC1’, ‘XYZ2’, 3, 25 union select 3, ‘ABC2’, ‘XYZ2’, 1, 30 union select 4, ‘ABC2’, ‘XYZ1’, 4, 35 declare @sql … Read more
In order to get the result that you want you will have to apply both the UNPIVOT and the PIVOT functions. The unpivot function will convert your Q1, Q2, Q3, and Q4 columns into rows. Once that is done, you will apply the pivot function. The unpivot code will be similar to this: select programid, … Read more