advice for SQL query with row values as columns

Question

What is the best way to go about returning a result set with the distinct zone values as columns from the below basic select query?

SELECT weight, base_rate, zone 
FROM Rates 
WHERE modeId = 984

Here is the table:

Rates table:

    id int
    modeId int
    base_rate decimal
    zone varchar
    weight decimal

I tried creating a pivot query to accomplish this, but so far have been unsuccessful. Any ideas? Should I just be manipulating the results in the C# code instead? The results will be put into a gridview.

This is what I would like the result set to look like

weight zone1 zone2 zone3 zone4
5       5.50 5.65  5.75  6.00
10      5.55 6.00  7.00  8.00
15      7.50 8.00  9.00  10.00

Here is my failed attempt at creating the pivot query.

select *
from
(
  SELECT weight, base_rate, zone FROM Rates where modeId=984 
) as d
pivot
(
  max(weight)
  for zone in (select distinct zone from d)
) piv;

If you've got a fixed number of zones you could join the table to itself, one join per zone... slightly messy but it should work. — CD001, Jan 31 '14 at 19:04
thanks for the comment, I should have mentioned that there is not a fixed number of zones. — flerngobot, Jan 31 '14 at 19:06
You should handle this pivot in C#. SQL is really the wrong tool when you don't have a predetermined output format. — Anon, Jan 31 '14 at 20:30

score 1 · Accepted Answer · answered Jan 31 '14 at 19:10

SQL Server does not allow using a SELECT statement to get the list of columns for a PIVOT. The columns names must be know at run-time.

If you have a limited number of values, then you would need to hard code the query:

select weight, Zone1, Zone2, Zone3, Zone4
from
(
  SELECT weight, base_rate, zone 
  FROM Rates where modeId=984 
) as d
pivot
(
  max(base_rate)
  for zone in (Zone1, Zone2, Zone3, Zone4)
) piv;

But if you are going to have an unknown number of values, then you need to look at using dynamic SQL. This will create a sql string that will then be executed at run-time. The syntax will be similar to:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(zone) 
                    FROM Rates 
                    where modeId=984 
                    group by zone
                    order by zone
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT weight,' + @cols + ' 
            from 
            (
                SELECT weight, base_rate, zone 
                FROM Rates where modeId=984 
            ) x
            pivot 
            (
                max(base_rate)
                for zone in (' + @cols + ')
            ) p '

execute sp_executesql @query;

score 1 · Answer 2 · answered Jan 31 '14 at 19:47

try dynamic query as below.

declare @pivcols as varchar(200);
select @pivcols = stuff((select distinct '],[' + zone
from Rates order by '],[' + zone
for xml path('')
), 1, 2, '') + ']';


declare @pivquery varchar(500)   
set @pivquery = 'Select '+   
@pivcols +' 
from   
(select weight, base_rate, zone from rates where modeId=984
) as d
PIVOT
(
max(weight)
for zone in ( '+ @pivcols +' )
) AS pvt '


EXECUTE(@pivquery)

advice for SQL query with row values as columns

2 Answers2