using a pivot where the number of columns changes

Question

I am using SQL Server 2012 & I have two tables.

 tblStocks                     tblFunds

 fundCode nvarchar             fundCode nvarchar
 stockID  nvarchar             fundType nvarchar
 shares int

I will show an example of the data below before explaining as its probably easier to see an example first.

 tblStocks
 fundCode    stockID   shares
 abcd        m33       20
 abcd        b22       10
 abcd        c33       5
 abcd        p99       6
 xyzu        m33       10
 xyzu        b22       8
 xyzu        w88       12

 tblFunds
 fundCode   fundType
 abcd       EQ
 xyzu       EQ

So please note the funds in tblStocks can have numerous stocks but each will be unique in that fund, i.e. abcd will not hold two m33 stocks.

Also please note abcd and xyzu can hold the same stocks, so both can hold m33.

Below is the result I would like to see. So I have a unique list of stocks and then for each fund I have the number of shares that belong to that fund. So I know that I need to use a pivot here (well its the only way I know). However the added complication (for me) is the number of different funds will not always be two so I don't know how to code this in sql server?

stockID    abcd    xyzu
m33        20      10
b22        10      8
c33        5       0
p99        6       0
w88        0       12

You need to use dynamic SQL when you don't know the output columns. — Tim Lehner, Sep 17 '15 at 15:13
possible duplicate of [SQL Server dynamic PIVOT query?](http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Kritner, Sep 17 '15 at 15:14
sorry I'm a sql novice. I don't follow what you mean by use dynamic SQL. I n my head I thought I could do a distinct query on the tblFunds to get the list of funds and then make use of this list in my pivot? — mHelpMe, Sep 17 '15 at 15:15
Please see this article I worte: http://www.codeproject.com/Articles/796651/Client-Side-Multi-Column-Dynamic-Pivot. It tells you all you need to know about pivot. — Jesús López, Sep 17 '15 at 15:17

Stefan Steiger · Accepted Answer · 2015-09-18T07:04:39.017

You need to use dynamic SQL to create your pivot statement.

Something like:

DECLARE @SQL varchar(MAX)
DECLARE @columns varchar(MAX)

SET @columns = 'column1, column2, column3' -- etc.

SET @SQL  = 'SELECT * FROM () PIVOT (
' + @columns + '
)'

EXECUTE(@SQL)

So you'll want to get the columns (make sure there are no NULL values)

DECLARE @columns varchar(MAX) 


SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(fundCode)
FROM tblFunds 
WHERE fundCode IS NOT NULL 

PRINT @columns

And then complete the query like this:

DECLARE @SQL nvarchar(MAX) 
DECLARE @columns nvarchar(MAX) 
DECLARE @columnsOrder nvarchar(MAX) 


SELECT @columns = COALESCE(@columns + N', ', N'') + QUOTENAME(fundCode) 
FROM tblFunds


SELECT @columnsOrder = COALESCE(@columnsOrder + N', ', N'') + QUOTENAME(fundCode) + N' DESC'  
FROM tblFunds


SET @SQL = N'
SELECT * FROM  
(
    SELECT 
         stockid 
        ,fundCode 
        ,shares 
    FROM tblStocks 
) AS dataToPivot 
PIVOT 
(
   SUM(shares)
   FOR fundCode IN (' + @columns + N')
) AS p 

ORDER BY ' + @columnsOrder + N' 
' 


-- PRINT @columns
-- PRINT @columnsOrder
-- PRINT @SQL 
EXECUTE(@SQL)

You should still catch the case of there being no entries in tblFunds (at the very beginning, the query will run fine, but not return any values and no empty table either)...

I would also create 2 more test entries, one with apostrophe, another with square brackets []

INSERT INTO tblFunds (fundCode, fundType) VALUES (N'ACME [US]', N'EQ');
INSERT INTO tblFunds (fundCode, fundType) VALUES (N'Goa''uld Charity Fund', N'EQ');

And you'll see it works just fine when you run QUOTENAME on the column names.

using a pivot where the number of columns changes

1 Answers1