Pivot in sql server

Yes I've tried the code. My requirement is that user inputs Year and Month & prices are shown date-wise in columns for that year and month, with first column as CompetitorID. I want my result like:

Competitors | day1  | day2  | day3  | day4 ..............|day31
================================================================
competitor 1| Price | Price | price |开发者_运维百科 price..............|price 
competitor 2| Price | Price | price | price..............|price
competitor 3| Price | Price | price | price..............|price 
competitor 4| Price | Price | price | price..............|price

My Table structure is:

COMPETITORDETAIL (ID, CompetitorID, oDate, Price)

---

This is a lot easier. I wrote a stored proc named pivot_query that makes PIVOT a lot easier to use for SQL Server 2005+. The source for the proc is here, some examples how to use it are here.

For your code example:

create table Competitors
   (
   CompetitorId      integer identity,
   Name              varchar(30)
   )

insert into Competitors values ('Bobs Discount Emporium')
go
insert into Competitors values ('Joes Really Cheap Crap')
go

create table CompetitorDetail
   (
   Id                integer identity,
   CompetitorId      integer,
   oDate             datetime,
   Price             decimal(12,3)
   )

insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-10, 10.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-8, 11.00)
go
insert into CompetitorDetail values (1, getdate()-6, 12.00)
go
insert into CompetitorDetail values (1, getdate()-6, 12.00)
go
insert into CompetitorDetail values (1, getdate()-6, 12.00)
go
insert into CompetitorDetail values (1, getdate()-2, 13.00)
go
insert into CompetitorDetail values (1, getdate()-2, 13.00)
go
insert into CompetitorDetail values (1, getdate()-2, 13.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-10, 14.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-8, 15.00)
go
insert into CompetitorDetail values (2, getdate()-6, 16.00)
go
insert into CompetitorDetail values (2, getdate()-6, 16.00)
go
insert into CompetitorDetail values (2, getdate()-6, 16.00)
go
insert into CompetitorDetail values (2, getdate()-2, 18.00)
go
insert into CompetitorDetail values (2, getdate()-2, 18.00)
go
insert into CompetitorDetail values (2, getdate()-2, 18.00)
go

declare @mySQL varchar(MAX)

set @mySQL = '
select
   c.Name,
   right(cast(month(cd.oDate) + 100 as varchar(3)),2) + ''_'' + right(cast(day(cd.oDate) + 100  as varchar(3)),2) mon_day,
   cd.Price
from
   Competitors c

   JOIN CompetitorDetail cd
      on (cd.CompetitorId = c.CompetitorId )
   ';

exec pivot_query @mySQL, 'Name', 'Mon_Day', 'max(Price) MaxP,min(Price) MinP'

which results in:

Name                           01_09_MaxP   01_09_MinP   01_11_MaxP   01_11_MinP   01_13_MaxP   01_13_MinP   01_17_MaxP   01_17_MinP   
------------------------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ 
Bobs Discount Emporium         10.000       10.000       11.000       11.000       12.000       12.000       13.000       13.000       
Joes Really Cheap Crap         14.000       14.000       15.000       15.000       16.000       16.000       18.000       18.000       

Hope that helps!

---

If you'r working with Microsoft SQL-Server there is a pivot function. But the columns are defined statically, so it's use is limited. (See http://technet.microsoft.com/en-us/library/ms177410.aspx)

There are solutions with dynamic SQL, but i solved this in the code eventually.

---

For SQL Server 2005, 2008

The table and some data to test

CREATE TABLE CompetitorDetail
  ( 
   ID int
  ,CompetitorID int
  ,oDate datetime
  ,Price decimal(19, 4)
  )

INSERT INTO  CompetitorDetail
      ( ID, CompetitorID, oDate, Price )
SELECT  1, 1, '2010-01-01', 100.0 UNION
SELECT  2, 1, '2010-01-02', 110.0 UNION
SELECT  3, 1, '2010-01-03', 99.0 UNION
SELECT  4, 2, '2010-01-01', 102.2 UNION
SELECT  5, 2, '2010-01-02', 112.2 UNION
SELECT  6, 2, '2010-01-03', 99.2 UNION
SELECT  7, 3, '2010-01-01', 100.3 UNION
SELECT  8, 3, '2010-01-02', 110.3 UNION
SELECT  9, 3, '2010-01-03', 99.3 ;

Start of the period and number of days

/* First day of the peroid */
DECLARE @StartDate datetime
    ,@NumberOfDays int

SET @StartDate = '2010-01-01'
SET @NumberOfDays = 31

Dynamic columns = dynamic sql

/* Table to compose dynamic query */
DECLARE @qw TABLE
  ( 
   id int IDENTITY(1, 1)
  ,txt nvarchar(500)
  )

/* Start composing dynamic query */
INSERT  INTO @qw ( txt ) VALUES  ( 'SELECT' ) 
INSERT  INTO @qw ( txt ) VALUES  ( 'CompetitorID' )

Continue composing the dynamic query

/* Helpers */
DECLARE
  @dte datetime
 ,@str varchar(10)
 ,@i int


/* Compose dynamic query */
SET @i = 0
WHILE @i < @NumberOfDays 
  BEGIN
    SET @dte = DATEADD(d, @i, @StartDate)
    SET @str = CONVERT(varchar(10), @dte, 121)  
    INSERT  INTO @qw ( txt )
            SELECT  ',MAX(CASE oDate WHEN ''' + @str + ''' THEN Price ELSE NULL END) AS [' + @str + ']'

    SET @i = @i + 1
  END

/* Finish the dynamic query */
INSERT  INTO @qw (txt) VALUES ( 'FROM  CompetitorDetail' )
INSERT  INTO @qw (txt) VALUES ( 'GROUP BY CompetitorID' )
INSERT  INTO @qw (txt) VALUES ( 'ORDER BY CompetitorID' )

Concatenate into a variable and execute

/* Create a variable with dynamic sql*/
DECLARE @exe nvarchar(4000)
SET @exe=''
SELECT  @exe = @exe + txt + ' ' FROM @qw ORDER BY id


/* execute dynamic sql */
EXEC sp_executesql @exe

Returns

CompetitorID 2010-01-01   2010-01-02   2010-01-03   2010-01-04  ...  2010-01-31 
------------ ----------   ----------   ----------   ----------  ...  ---------- 
1            100.0000     110.0000     99.0000      NULL        ...  NULL       
2            102.2000     112.2000     99.2000      NULL        ...  NULL       
3            100.3000     110.3000     99.3000      NULL        ...  NULL       

---

CASE 
WHEN SQL Server 2005 OR 2008 THEN Use Pivot 
WHEN Oracle THEN Use LIKE MAX(Decode(Day=1,Data,0) as Day1 and GROUP BY Day
WHEN SQL Server 2000 THEN Use LIKE MAX(CASE WHEN Day = 1 THEN Data ELSE 0 End) As Day1 and GROUP BY Day 
END