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