Pivot Function in SQL 2005
-
I am new to pivot tables and SQL 2005. I am using the following T-SQL code to generate a pivot table.
USE
NWIND_2000SQL;
GO
SELECT
rptperiod, [Doe, Jane], [Adams, John]
FROM
(
SELECT rptperiod, coder, sumofcompleted
FROM
VWDBO_TBL_CODERTRAINING_CODER_CROSSTAB) p
PIVOT
(
sum
(sumofcompleted)
FOR
coder IN
(
[Doe, Jane], [Adams, John )
)
AS pvt
ORDER
BY rptperiod
The problem is that I have 192 names in addition to the [Doe, Jane] and [Adams, John]
Does anyone know of a way to include the names without having to type each of the 192 names in brackets. Also the names are subject to change so I need a generic way of doing this.
Any help will be appreciated Thanks.
-
You need to use dynamic sql. First, build your name list firts by whatever way you want, then use the list to build the query. The pesuo code is like:
SELECT @NameList=....
DECLARE @sql nvarchar(max)
SET @sql='
SELECT rptperiod, '+@NameLIst+'
FROM
(
SELECT rptperiod, coder, sumofcompleted
FROM
VWDBO_TBL_CODERTRAINING_CODER_CROSSTAB) p
PIVOT
(
sum
(sumofcompleted)
FOR
coder IN
(
)
AS pvt
ORDER
BY rptperiod'
EXEC (@SQL)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply