0

I'm working on a webapp that displays data from a Microsoft SQL Server dynamic pivot table.

Normally I'd try and figure out a way to do the dynamic pivot in c#, but in this case the pivot has to be a SQL Server stored procedure because other apps also need access to the pivot table.

Here's the SQL:

DECLARE @DynamicPivot AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

SELECT 
    @ColumnName = ISNULL(@ColumnName + ',', '') 
                  + QUOTENAME(xml_tag_name)
FROM 
    (SELECT DISTINCT xml_tag_name FROM DataEntries) AS TagValues

SET @DynamicPivot =
N'SELECT DISTINCT capture_id, ' + @ColumnName + '
FROM DataEntries
PIVOT(MAX(xml_tag_value) 
FOR xml_tag_name IN (' + @ColumnName + ')) AS PVTTable'

EXEC sp_executesql @DynamicPivot

All the articles I've gone through deal with normal export to Excel or static pivots. eg: Export Table from SQL Server to Excel 2007 using C#.

How do I go about exporting this dynamic pivot to Excel?

Community
  • 1
  • 1
Rinus
  • 140
  • 1
  • 9
  • What is the first step which you can do ? – mybirthname Apr 22 '15 at 09:13
  • Are you're asking if I can do normal export? Yes I can. – Rinus Apr 22 '15 at 09:22
  • Essentially you are trying to emulate what an Excel Pivot Table does. Why don't you use Excel's pivot table instead? It's optimized for exactly this type of work and nothing is more dynamic than the ability to change columns on the fly. – Panagiotis Kanavos Apr 22 '15 at 09:51
  • Other apps also need access to the pivot table in the db. For now this particular app is the only one that needs to export. – Rinus Apr 22 '15 at 09:56
  • I constructed a seperate C# Console Application that passed a query and a filepath through the args array, then pulled the pivot data to a datatable in C# and used the EPPlus extended library to write the datatable to a new excel file. Then I called this console app in SQL Server with xp_cmdshell, it works a treat and it's so easy to implement. – John Bell Apr 22 '15 at 12:51
  • ^Sound advice. Thanks. – Rinus Apr 23 '15 at 07:09

1 Answers1

0

heres a example for you.

It creates dynamic pivot to global temp table and then exports it to excel. If you find problems with export part - let me know.

CREATE TABLE Table1 (ColId INT,ColName VARCHAR(10))
INSERT INTO Table1 VALUES(1, 'Country')
INSERT INTO Table1 VALUES(2, 'Month')
INSERT INTO Table1 VALUES(3, 'Day')

CREATE TABLE Table2 (tID INT,ColID INT,Txt VARCHAR(10))

INSERT INTO Table2 VALUES (1,1, 'US')
INSERT INTO Table2 VALUES (1,2, 'July')
INSERT INTO Table2 VALUES (1,3, '4')
INSERT INTO Table2 VALUES (2,1, 'US')
INSERT INTO Table2 VALUES (2,2, 'Sep')
INSERT INTO Table2 VALUES (2,3, '11')
INSERT INTO Table2 VALUES (3,1, 'US')
INSERT INTO Table2 VALUES (3,2, 'Dec')
INSERT INTO Table2 VALUES (3,3, '25')


DECLARE @cols NVARCHAR(2000);
SELECT  @cols = COALESCE(@cols + ',[' + colName + ']', '[' + colName + ']')
FROM    Table1
ORDER BY colName;
IF OBJECT_ID('tempdb..##t1') IS NOT NULL
    BEGIN
        DROP TABLE ##t1;
    END;

DECLARE @query NVARCHAR(4000);
SET @query = N'SELECT tID, ' + @cols + ' into ##t1
FROM
(SELECT  t2.tID
      , t1.ColName
      , t2.Txt
FROM    Table1 AS t1
        JOIN Table2 AS t2 ON t1.ColId = t2.ColID) p
PIVOT
(
MAX([Txt])
FOR ColName IN
( ' + @cols + ' )
) AS pvt
ORDER BY tID;';


EXECUTE(@query);

SELECT  *
FROM    ##t1;


DECLARE @sql VARCHAR(MAX);
DECLARE @FileName VARCHAR(MAX) = 'C:\Test.xls';
SET @sql = 'INSERT INTO OPENROWSET(''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0;Database='
    + @FileName + ''',''SELECT * FROM [Sheet1$]'') SELECT * FROM ##T1';
EXECUTE(@sql);
VoonArt
  • 884
  • 1
  • 7
  • 21
  • My dynamic pivot can have an infinite amount of columns. `DataEntries` pivots to become the headers/column names for the pivot table. So as the client captures more info in the `DataEntries` the amount of columns will change. This is a good answer, but the problem is that it still depends on a fixed amount of columns. – Rinus Apr 23 '15 at 07:51