sql server - Sql pivot - but dont sum the values -

i have table schema looks this

create table [dbo].[discounts](       [id] [int] not null,       [productid] [varchar(50)] not null,       [lowerbounddays] [int] not null,       [upperbounddays] [int] not null,       [discount] [decimal](18, 4) not null, 

and data this

        lower       upper discount(%) product1  0         10          0 product1  10        30          1 product1  30        60          2 product1  60        90          3 product1  90        120         4 product2  0         10          0 product2  10        30          1 product2  30        60          2 product2  60        90          3 product2  90        120         4 

how can pivot query 2 rows this:

            0-10    10-30   30-60   60-90   90-120 product1    0       1       2       3       4 product2    0       1       2       3       4 

since using sql server, there several ways can convert rows of data columns.

you can use aggregate function case expression result:

select productid,   max(case when lower = 0 , upper = 10 discount end) [0-10],   max(case when lower = 10 , upper = 30 discount end) [10-30],   max(case when lower = 30 , upper = 60 discount end) [30-60],   max(case when lower = 60 , upper = 90 discount end) [60-90],   max(case when lower = 90 , upper = 120 discount end) [90-120] corporatespread group productid; 

see sql fiddle demo.

if using sql server 2005+, can use pivot function:

select productid, [0-10], [10-30], [30-60], [60-90],[90-120]  (   select productid,     discount,     cast(lower varchar(10)) + '-' + cast(upper varchar(10)) rng   corporatespread ) d pivot (   max(discount)   rng in ([0-10], [10-30], [30-60], [60-90],[90-120]) ) piv; 

see sql fiddle demo.

the above 2 version work great if have known number of values, if have unknown number of ranges, need to use dynamic sql:

declare @cols nvarchar(max),     @query  nvarchar(max)  select @cols = stuff((select distinct ',' + quotename(cast(lower varchar(10)) + '-' + cast(upper varchar(10)))                      corporatespread             xml path(''), type             ).value('.', 'nvarchar(max)')          ,1,1,'')  set @query = 'select productid, ' + @cols + '                           (                 select productid,                   discount,                   cast(lower varchar(10)) + ''-'' + cast(upper varchar(10)) rng                 corporatespread             ) x             pivot              (                 max(discount)                 rng in (' + @cols + ')             ) p '  execute sp_executesql @query; 

see sql fiddle demo. versions give result:

| productid | 0-10 | 10-30 | 30-60 | 60-90 | 90-120 | ----------------------------------------------------- |  product1 |    0 |     1 |     2 |     3 |      4 | |  product2 |    0 |     1 |     2 |     3 |      4 |