SQL Pivot Table Dynamic - exclude unused columns

Question

I have the following SQL code that creates a very useful pivot table:

Use [A1_20132014]

DECLARE @cols NVARCHAR (MAX)

SELECT @cols = COALESCE (@cols + ',[' + Link_ID + ']', '[' + Link_ID + ']')
               FROM    (SELECT DISTINCT Link_ID FROM A1) PV  
               ORDER BY Link_ID

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT * FROM 
             (
                 -- We will select the data that has to be shown for pivoting
                 SELECT date_1, StartHour,Cost, Link_ID
                 FROM A1
                 WHERE Section = (''1AB'')
             ) x
             PIVOT 
             (
                 -- Values in each dynamic column
                 SUM(Cost)
                 -- Select columns from @cols 
                 FOR Link_ID IN (' + @cols + ')                      
            ) p;' 

EXEC SP_EXECUTESQL @query

from these headings

       link_id       Section   date_1     StartHour   Cost    data_source
   4000000027866016A   8NB    2013-09-02    6         5871        1
   4000000027866017B   5EB    2013-10-09    9         8965        2
   4000000027856512B   4TB    2013-05-06    15        6754        1
   4000000027866015A   6HB    2013-06-08    8         5354        1
   4000000027866011A   1AB    2013-06-09    11          2         1

with these source types;

    link_Id         nvarchar(50)    
    Section         nvarchar(50)    
    Date_1          smalldatetime   
    StartHour       int 
    Cost            float    
    data_source     int

However despite WHERE clause that specifies a certain section unfortunately ALL sections still appear in the pivot table but populated with NULL values all the way down.

Is there a way of completely excluding the columns that do not meet the WHERE clause?

Thanks for any help.

Henry.

i think you should use where clause in the first query also where you are setting @cols . — KumarHarsh
you can check this link also . stackoverflow.com/questions/28961753/… — KumarHarsh
I can't get that to work, it tells me that Section is an invalid column name in the @cols part. — hc91
Posting your table structures and some sample data would be helpful in resolving the issue. — Taryn
EVERYBODY IS TELLING,post some sample data with 3 same section and one different — KumarHarsh

Unknown Unknown · Accepted Answer · 2015-03-12T12:42:45

Put the where clause in the subquery. That way you'll only get columns that apply to 1AB

SELECT @cols = COALESCE (@cols + ',[' + Link_ID + ']', '[' + Link_ID + ']')
               FROM    (SELECT DISTINCT Link_ID FROM A1 WHERE Section = '1AB') PV  
               ORDER BY Link_ID

SQL Pivot Table Dynamic - exclude unused columns

1 Answers