Pivoting - calendar view - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Analysis Services (2000)

Pivoting - calendar view

Author

Topic

Avinash Birnale
Starting Member

3 Posts

Posted - 2004-11-08 : 04:40:49

Hi All,

This might have been posted in some of the threads but since i couldn't find it, i am writing it here again...

See my problem is like this - i have two table with me - items and itemdates. I want to show the records of the item tables in the normal rows while the records in the itemdates in the coloumnar on each matching rows of the first table.

Tables available are -
----------------
Table1: Items
----------------
ItemID itemName
----------------------
i1 Item1
i2 Item2
i3 Item3
----------------------

----------------
Table2: Itemdates
----------------
ItemID itemdate
----------------------
i1 3-Nov
i2 1-Nov
i2 4-Nov
i3 1-Nov
i3 3-Nov
----------------------

display desired as -
------------------------------------------------------------------
ITEMID 1-Nov 2-Nov 3-Nov 4-Nov ..... 30-Nov
------------------------------------------------------------------
i1 - - Y -
i2 Y - - Y
i3 Y - Y -
.
.
.
------------------------------------------------------------------

Is this possible in the single SQL query using the pivot or any other technique?

Please help....

thanks n regards,
Avinash

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-11-08 : 08:32:03

These may help you:

http://www.sqlteam.com/searchresults.asp?SearchTerms=cross+tab

Although none of them are used for Analysis Services.

Avinash Birnale
Starting Member

3 Posts

Posted - 2004-11-08 : 09:34:59

Hi,

I have already visited this link. I could do the following as a simple workout -

SELECT [ID],
SUM(CASE c_ItemDates WHEN '2004-11-1' THEN 1 ELSE 0 END) '1',
SUM(CASE c_ItemDates WHEN '2004-11-2' THEN 1 ELSE 0 END) '2',
SUM(CASE c_ItemDates WHEN '2004-11-3' THEN 1 ELSE 0 END) '3',
SUM(CASE c_ItemDates WHEN '2004-11-4' THEN 1 ELSE 0 END) '4'
From ItemDates
Group by [ID]

This works fine. But now i have another problem in front of me...

How do i write the range in the case statements? E.g. what if i have to get one column for dates that are in a single week?

I mean, i need one column for dates 1-11 through 7-11.

Is this possible?

Regards,
Avinash B

Only dead fish follow the stream !!!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-11-08 : 12:07:13

This must be posted in the wrong forum - Analysis Services ?
In Analysis Services you would build a date dimension, and pivoting is not an issue.

To answer your SQL question:
CASE WHEN c_ItemDates BETWEEN '2004-11-1' AND '2004-11-7' THEN 1 ELSE 0 END

rockmoose

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources