ERMAHBLERG

We have a scenario where we need a dynamic pivot table from SQL data. I’m going to try and make this generic, so if there is confusion, please let me know.

The scenario: We have a parent database and a child database. The child database has “attributes” for the parent database with values. SQL to create the base tables:

CREATE TABLE parent (
customerid int,
customername varchar(50)

) ON [PRIMARY]

CREATE TABLE child (
customerid int,
attribute varchar(50),
value int
) ON [PRIMARY]

insert into parent (customerid, customername)
values (1, 'Bob')
insert into parent (customerid, customername)
values (2, 'Casey')
insert into parent (customerid, customername)
values (3, 'Jeremy')

insert into child (customerid, attribute, value)
values (1, 'Children', 2)
insert into child (customerid, attribute, value)
values (1, 'Cars', 2)
insert into child (customerid, attribute, value)
values (2, 'Children', 0)
insert into child (customerid, attribute, value)
values (2, 'Cars', 4)
insert into child (customerid, attribute, value)
values (3, 'Children', 1)
insert into child (customerid, attribute, value)
values (3, 'Cars', 3)

The parent table contains a customer and their ID (other records that you want to surface can exist, but I am simplifying this as much as possible.) The child table has an Attribute, which has values associated with it. In this case, I want to be able to dynamically add attributes as much as possible, and not have to change my query to have the data output with a column for each attribute.

To do this, a PIVOT command is GREAT to use, but PIVOT requires you to have column names predefined. To do this, you have to do a little dynamic SQL.

The PIVOT command works as follows:
SELECT finalcolumns
from (SELECT columns, values from tables) a
PIVOT (SUM(value) FOR columnfroma IN (values)) pvt

We need to generate the columns dynamically, along with the entire statement dynamically, and execute that. I start by declaring my base variables. I need a varchar(MAX) to hold my sql statement, a table of columns, and a varchar(max) to turn the table into a comma delimited list of columns.

I then query the source of the columns, which in this case will be the distinct list of attributes in the child table, and convert it into a delimited list, and build my delimited list of values. From there I create the dynamic sql. The Final code is below:

DECLARE @sql varchar(MAX)
DECLARE @columns TABLE (COL varchar(50))
declare @columnscsv varchar(MAX)

insert into @columns
select distinct attribute from child

select @columnscsv = COALESCE(@columnscsv + '],[','') + COL from @columns
set @columnscsv = '[' + @columnscsv + ']'

SET @sql = 'SELECT customerid, customername, ' + @columnscsv + ' FROM
(SELECT p.customerid, p.customername, c.attribute, SUM(value)[value] FROM parent p JOIN child c on p.customerid = c.customerid group by p.customerid, p.customername, c.attribute) a
PIVOT (SUM(value) for attribute in (' + @columnscsv + ')) as PVT order by customerid'

EXEC (@sql)

Executing this, you should get the following pivot table:

(2 row(s) affected)
 customerid  customername Cars        Children
 ----------- ------------ ----------- -----------
 1           Bob          2           2
 2           Casey        4           0
 3           Jeremy       3           1

Now lets add a new attribute ‘Houses’, to Jeremy, and him only.

insert into child (customerid, attribute, value)
values (3, 'Houses', 1)

Re-execute the dynamic sql from above and you should get the following:

(3 row(s) affected)
 customerid  customername Cars        Children    Houses
 ----------- ------------ ----------- ----------- -----------
 1           Bob          2           2           NULL
 2           Casey        4           0           NULL
 3           Jeremy       3           1           1

You still need to handle NULLS, and I have SUM()’s instead of the actual values, as there may be multiple records in the real version of the one I developed this from, but this is extremely useful if you need dynamic columns generate for time ranges, attributes, etc.