Scribd
Upload
4 views1 page

SQL Fiddle Pivot Query Example

This SQL code dynamically generates a pivot query to average rental values by period for different descriptions. It uses STUFF and FOR XML PATH to build a comma-separated list of periods to use as columns in the pivot, constructs a query string concatenating the column list, and executes the pivot query to return the averaged rental values by period for each description in columns.

Uploaded by

appus20056083
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
4 views1 page

SQL Fiddle Pivot Query Example

This SQL code dynamically generates a pivot query to average rental values by period for different descriptions. It uses STUFF and FOR XML PATH to build a comma-separated list of periods to use as columns in the pivot, constructs a query string concatenating the column list, and executes the pivot query to return the averaged rental values by period for each description in columns.

Uploaded by

appus20056083
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

DECLARE @cols AS NVARCHAR(MAX);

DECLARE @query AS NVARCHAR(MAX);


SELECT @cols = STUFF((SELECT distinct
',' +
QUOTENAME(Period)
FROM temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'');

SET @query = ' SELECT Description, ' + @cols + '


FROM
(
SELECT
CASE
WHEN sumUnits > 0
THEN SumAvgRent / sumUnits ELSE 0
END AS Expr1,
Description,
Period
FROM temp
)t
PIVOT
(
AVG(Expr1)
FOR Period IN( ' + @cols + ')
) p ';

Execute(@query);

Updated SQL Fiddle Demo

This should give you the same result:


| DESCRIPTION | PERIOD1 | PERIOD2 | PERIOD3 |
--------------------------------------------|
D1 |
10 |
0|
20 |
|
D2 |
100 | 1000 |
0|
|
D3 |
50 |
10 |
2|

You might also like