1

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)

Improve this question
2
  • Do you wish to show this for a specific month only, or for all months/days? Should it show columns for days where there are no entries? Will this be dynamic, or for fixed columns and dates (example, you only want to see Jan 2009 values) ? You need to show some example data, and expected output. Commented Jan 14, 2010 at 6:48
  • I've shown the output. User will input year and month and the query will result the above format, i.e. Competitors in first col and rest of the columns will be dates (1 to 31) for that year and month. Commented Jan 14, 2010 at 6:57

4 Answers 4

Reset to default
2

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

0

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
Improve this answer

Comments

0 
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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.