Need Dynamic Pivot translated to SQL 2000 Compatibility... and need to create table based on results

Question

 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.

Thanks so much!

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

---

Tamara M.

Answer 1

SELECT @sql = 'SELECT DeptDesc, BU, Name, ' + char(13) + char(10)

DECLARE cur CURSOR STATIC LOCAL
   SELECT DISTINCT convert(varchar, BU)
   FROM XVR_SP_EMPLOYEES_WBUDEPT
   ORDER BY BU

OPEN cur

WHILE 1 = 1
BEGIN
   FETCH cur INTO @col
   IF @@fetch_status <> 0
      BREAK

   SELECT @sql = @sql + 'SUM (CASE WHEN BU = ' BU +
                        'THEN 1 END) AS ' + quotename(BU) +
                        ',' + char(13) + char(10)
END

SELECT @sql = substring(@sql, 1, len(@sql) - 1)

SELECT @sql = 'FROM xvr_SP_Employees_wBUDept ' + char(13) + char(10) +
              'GROUP BY DeptDesc, BU, Name'

PRINT @sql
EXEC(@sql)

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, [e-mail]