Sql server® 2012 t-sql fundamentals

 

CHAPTER 10

 

Programmable Objects

 

 361

This code assigns the value 10248 to the input parameter, but even if you run it again with a differ-

ent value, the code string remains the same. This way, you increase the chances for reusing a previ-

ously cached plan.

Using 

PIVOT

 with dynamic SQL

This section is advanced and optional, and is intended for those readers who feel very comfortable 

with pivoting techniques and dynamic SQL. In Chapter 7, I explained how to use the PIVOT opera-

tor to pivot data. I mentioned that in a static query, you have to know ahead of time which values 

to specify in the IN clause of the PIVOT operator. Following is an example of a static query with the 

PIVOT operator.

SELECT * 

FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight 

      FROM Sales.Orders) AS D 

  PIVOT(SUM(freight) FOR orderyear IN([2006],[2007],[2008])) AS P;

This example queries the Sales.Orders table and pivots the data so that it returns shipper IDs in the 

rows, order years in the columns, and the total freight in the intersection of each shipper and order 

year. This code returns the following output.

shipperid   2006         2007          2008 

----------- ------------ ------------- ------------- 

3           4233.78      11413.35      4865.38 

1           2297.42      8681.38       5206.53 

2           3748.67      12374.04      12122.14

With the static query, you have to know ahead of time which values (order years in this case) to 

specify in the IN clause of the PIVOT operator. This means that you need to revise the code every year. 

Instead, you can query the distinct order years from the data, construct a batch of dynamic SQL code 

based on the years that you queried, and execute the dynamic SQL batch like this.

DECLARE 

  @sql       AS NVARCHAR(1000), 

  @orderyear AS INT, 

  @first     AS INT; 

 

DECLARE C CURSOR FAST_FORWARD FOR 

  SELECT DISTINCT(YEAR(orderdate)) AS orderyear 

  FROM Sales.Orders 

  ORDER BY orderyear; 

 

SET @first = 1; 

 

SET @sql = N'SELECT * 

FROM (SELECT shipperid, YEAR(orderdate) AS orderyear, freight 

      FROM Sales.Orders) AS D 

  PIVOT(SUM(freight) FOR orderyear IN('; 

 

OPEN C; 

 

www.it-ebooks.info

---

362  

Microsoft SQL Server 2012 T-SQL Fundamentals

FETCH NEXT FROM C INTO @orderyear; 

 

WHILE @@fetch_status = 0 

BEGIN 

  IF @first = 0 

    SET @sql = @sql + N',' 

  ELSE 

    SET @first = 0; 

 

  SET @sql = @sql + QUOTENAME(@orderyear); 

 

  FETCH NEXT FROM C INTO @orderyear; 

END 

 

CLOSE C; 

 

DEALLOCATE C; 

 

SET @sql = @sql + N')) AS P;'; 

 

EXEC sp_executesql @stmt = @sql;

note

  There are more efficient ways to concatenate strings than using a cursor, such as us-

ing Common Language Runtime (CLR) aggregates and the FOR XML PATH option, but they 

are more advanced and are beyond the scope of this book.