Need Dynamic Pivot translated to SQL 2000 Compatibility... and need to create table based on results
Friday, September 21, 2012 7:50 PM
I need some help translating the query below into SQL Server 2000 (i.e. without the pivot) due to compatibility issues in my production environment. Also, if anyone had any advice on inserting the RESULTS of this query into a new table that would be helpful too.
use maindb go declare @columns varchar(8000) select @columns= coalesce ( @columns+ ',[' + cast(BU as varchar) + ']', '[' + cast(BU as varchar) + ']' ) FROM XVR_SP_EMPLOYEES_WBUDEPT group by BU order by BU DECLARE @query VARCHAR(8000) SET @query = ' SELECT * INTO MYNEWTABLE FROM (SELECT * FROM ( SELECT DeptDesc, BU, Name FROM xvr_SP_Employees_wBUDept ) PIV PIVOT ( count(Name) FOR BU in (' + @columns + ') )) AS chld' EXECUTE (@query) GO
Friday, September 21, 2012 10:08 PM
SELECT @sql = 'SELECT DeptDesc, BU, Name, ' + char(13) + char(10)
DECLARE cur CURSOR STATIC LOCAL
SELECT DISTINCT convert(varchar, BU)
ORDER BY BU
WHILE 1 = 1
FETCH cur INTO @col
IF @@fetch_status <> 0
SELECT @sql = @sql + 'SUM (CASE WHEN BU = ' BU +
'THEN 1 END) AS ' + quotename(BU) +
',' + char(13) + char(10)
SELECT @sql = substring(@sql, 1, len(@sql) - 1)
SELECT @sql = 'FROM xvr_SP_Employees_wBUDept ' + char(13) + char(10) +
'GROUP BY DeptDesc, BU, Name'
I will have to admit that I don't fully understand the original query, as I have never bothered to learn the PIVOT keyword. The above achieves a pivot with standard SQL constructs, and is much easier to understand - save that the dynamic SQL occludes it.
Storing the result of a pivot in a table is fairly useless. Since you don't know the name of the columns, the table is difficult to work with.
Erland Sommarskog, SQL Server MVP, firstname.lastname@example.org