Dynamic Pivot - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic Pivot

Author

Topic

SuperJB
Starting Member

12 Posts

Posted - 2012-11-20 : 09:55:34

Hello everyone,

I need to generate a PIVOT table from a view.
The thing is, I never know how many columns I'll have.

The view returns information regarding an institution that manages IT courses. So it will have information of the customers, courses (C), modules of the courses (M), Module Name, attendance, grades, etc...

EXAMPLE:
Customer Course Module Name Grade Attendance StartDate EndDate
1 1 1 ABC 93 100 ... ...
1 1 2 DEF 86 99 ... ...
1 1 3 HIJ 72 91 ... ...
1 2 1 KLM 94 50 ... ...
2 1 1 DEF 81 100 ... ...
2 1 2 ABC 91 92 ... ...

I need to PIVOT this information to show only 1 row per customer and have 1 column for each Course/Module and do calculations with the other columns.
So, my question, how can I get this information above to be something like this:

Customer C1M1 C1M2 C1M3 C2M1 C2M1 C2M2 etc...
1 ABC DEF HIJ KLM DEF ABC

As you can see, I can't count on having the same number of Course/Module relations for each Customer. Can anyone please give me hand with this?!

Thanks in advance

SuperJB

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 10:23:50

Check out Madhivanan's blog here on dynamic pivoting: http://beyondrelational.com/modules/2/blogs/70/posts/10791/dynamic-crosstab-with-multiple-pivot-columns.aspx
You may also be able to do something like shown in the example below:

CREATE TABLE #tmp (customer INT, module VARCHAR(32));
INSERT INTO #tmp VALUES
(1,'abc'),(1,'def'),(1,'ghi'),(1,'jkl'),(1,'mno'),
(2,'xyz'),
(3,'aaa'),(3,'bbb'),(3,'ccc');

;WITH cte AS
(
	SELECT *, 
	ROW_NUMBER() OVER (PARTITION BY customer ORDER BY module) RN
	FROM #tmp
)
SELECT
	*
FROM
	cte
PIVOT
( MAX(module) FOR RN IN ([1],[2],[3],[4],[5],[6]))p

DROP TABLE #tmp;

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-11-20 : 11:13:47

If you don't know column names,then you can generate columnlist from syscolumns for that view.

SuperJB
Starting Member

12 Posts

Posted - 2012-11-20 : 11:44:17

Hi guys, thanks for the replies!

[sunitabeck]: I already have the Row_Number for each Course / Module for each Customer. The problem is, when I run the query. I'll never know the MAX nº of Courses or Modules that is returned. It will always be something different.
So, following your example, I can't just use "( MAX(module) FOR RN IN ([1],[2],[3],[4],[5],[6]))p" because I may have 7 or 9 as MAX nº of Modules.

SuperJB

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 11:51:52

One option would be to count the number of modules, then create dynamic query to pivot. If you are presenting the data (in an SSRS report, for example), you may want to consider doing the pivoting there instead.

SuperJB
Starting Member

12 Posts

Posted - 2012-11-20 : 12:03:12

Hello again Sunitabeck,

That's exactly what I'm trying to figure out doing "create dynamic query to pivot".

SuperJB

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-20 : 12:16:07

Code below is a simplified example. I think Madhivanan's blog that I suggested in my previous reply has more flexible code

CREATE TABLE #tmp (customer INT, module VARCHAR(32));
INSERT INTO #tmp VALUES
(1,'abc'),(1,'def'),(1,'ghi'),(1,'jkl'),(1,'mno'),
(2,'xyz'),
(3,'aaa'),(3,'bbb'),(3,'ccc');

DECLARE @maxcol INT;
SELECT TOP 1 @maxcol = COUNT(*) FROM #tmp GROUP BY customer ORDER BY COUNT(*) DESC ;

DECLARE @sql NVARCHAR(4000) = '';
SELECT @sql = @sql + ',['+CAST(number+1 AS NVARCHAR(32))+ ']'
FROM MASTER..spt_values WHERE  [type] = 'P'
AND  number < @maxcol ORDER BY number;

SET @sql = '
;WITH cte AS
(
	SELECT *, 
	ROW_NUMBER() OVER (PARTITION BY customer ORDER BY module) RN
	FROM #tmp
)
SELECT
	*
FROM
	cte
PIVOT
( MAX(module) FOR RN IN (' + 
STUFF(@sql,1,1,'') + '))p';

exec sp_executesql @sql;

DROP TABLE #tmp;