1

I developed a pivot in tsql which resembles below:

MaturityBand    AAA     AA+     AA      AA-     A+      A       A-      Grand_Total 
Less than 1 yr  NULL    NULL    NULL    NULL    NULL    NULL    7.92    7.92
1 to 5 yrs      NULL    NULL    0.44    NULL    NULL    1.95    8.96    11.35
5 to 10 yrs     NULL    NULL    1.33    NULL    NULL    4.11    4.04    9.98
10 to 20 yrs    NULL    NULL    1.14    NULL    NULL    0.74    1.07    2.95
>20 years       NULL    NULL    0.15    NULL    NULL    NULL    0.14    0.29
Total           NULL    NULL    3.06    NULL    NULL    6.8     22.13   31.99

What i would like to do is remove the null columns so that above becomes below:

MaturityBand    AA      A       A-      Grand_Total 
Less than 1 yr  NULL    NULL    7.92    7.92
1 to 5 yrs      0.44    1.95    8.96    11.35
5 to 10 yrs     1.33    4.11    4.04    9.98
10 to 20 yrs    1.14    0.74    1.07    2.95
>20 years       0.15    NULL    0.14    0.29
Total           3.06    6.8     22.13   31.99

Is this possible?

(I can post my code if necessary.)

Mahmoud Gamal
  • 78,257
  • 17
  • 139
  • 164
chrissy p
  • 823
  • 2
  • 20
  • 46

1 Answers1

1

No, filters in the WHERE or HAVING clause work on rows, not on columns. If you don't want a specific column you can only exclude it manually from the SELECT clause.

SELECT 
MaturityBand, AA, A, [A-], Grand_Total 
FROM
(
   /*put your pivot query here*/
)some_alias

Here's an answer I wrote today for dynamic pivot (like aF. suggested in the comments).

Dynamic Pivot Query

Have a try with it and let me know the result, please. Actually I would be surprised if this works.

Community
  • 1
  • 1
fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Just wanted to add that, it's possible, yes, but it would be kind of ridiculous – fancyPants Jun 27 '12 at 13:35
  • Using `dynamic sql` for instance. If the values of a column are only `NULL` then the query won't select it. – aF. Jun 27 '12 at 13:37
  • thats the thing, i don't know which columns are null until the procedure has finished. I'm trying to automate this so having no manual intervention. So are you saying that it is impossible without manually selecting the columns you know are not null? – chrissy p Jun 27 '12 at 13:38
  • so how can i add dynamic sql to the script i've already written? or is it a complete rewrite? – chrissy p Jun 27 '12 at 13:39
  • @chrissyp Have a look at my edited answer, please. Should be easy to adjust to your needs. – fancyPants Jun 27 '12 at 13:43
  • @chrissyp you'll have to update your script to check the values on it and only select the columns not null using Dynamic SQL. – aF. Jun 27 '12 at 13:47