SQL Pivot table for days in fortnight

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

General SQL Server Forums

New to SQL Server Programming

SQL Pivot table for days in fortnight

Author

Topic

mnarewec
Starting Member

10 Posts

Posted - 2010-08-18 : 00:01:42

Ok Team,

I am a newbie to SQL Programming. I have following SQL table

EmpID TaskDate Quantity OutTurn Bonus
888 16/08/2010 100 10% $10.00
888 17/08/2010 200 50% $100.00
888 25/08/2010 50 5% $2.50
999 18/08/2010 100 10% $10.00
999 24/08/2010 200 50% $100.00
999 25/08/2010 50 5% $2.50

etc

My boss wants the above table to be shown as

EmpId M T W T F S S M T W T F S S
888 - - 100 200 - - - - - 50 - - - -
888 - - 10% 50% - - - - - 5% - - - -
888 - - $10 $100 - - - - - $2.50 - - - -
999 - - 100 - - - - - 200 50 - - - -
999 - - 10% - - - - - 50% 5% - - - -
999 - - $10 - - - - - $100 $2.50 - - -

My guess is to create three pivot tables and then append them together?? Or do I need to do little bid of programming in VB??

Please SQL Guru's help me achieve this.

Cheers!

Marsh Narewec

Sachin.Nand

2937 Posts

Posted - 2010-08-18 : 00:34:07

Why are the weekdays being repeated in the o/p?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 00:42:03

Hi,

My understanding is that you want to show the data for 2 weeks in a pivot.
If my understanding is correct then you can use dynamic pivot and pivot it base on date for 2 weeks. In front end you can get the day for the date and display it.

For understanding of dynamic pivot, have a look at:
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

mnarewec
Starting Member

10 Posts

Posted - 2010-08-18 : 01:50:04

quote:
Originally posted by Idera

Why are the weekdays being repeated in the o/p?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Hi PBUH,

True we have unlimited possibilities, but in this case, I can't see one.

The days are repeatitive because its a fortnight (2 weeks) report.

mnarewec
Starting Member

10 Posts

Posted - 2010-08-18 : 01:51:46

quote:
Originally posted by pk_bohra

Hi,

My understanding is that you want to show the data for 2 weeks in a pivot.
If my understanding is correct then you can use dynamic pivot and pivot it base on date for 2 weeks. In front end you can get the day for the date and display it.

For understanding of dynamic pivot, have a look at:
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Thanks Bohra, But I am using SQL 2000. Will this work??

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-08-18 : 04:33:25

Pivot operator was introduced in sql server 2005 so this will not work for sql 2000.

Let me check if i can suggest you something for 2000.

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 10:46:04

Your Boss is a moron

What value is that?

You would also need another column to describe the new row..i.e...

ColumnType
Quantity
OutTurn
Bonus

You need to post the table DDL

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-18 : 11:12:21

quote:
Originally posted by mnarewec

quote:
Originally posted by pk_bohra

Hi,

My understanding is that you want to show the data for 2 weeks in a pivot.
If my understanding is correct then you can use dynamic pivot and pivot it base on date for 2 weeks. In front end you can get the day for the date and display it.

For understanding of dynamic pivot, have a look at:
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Thanks Bohra, But I am using SQL 2000. Will this work??

For version 2000, use this
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

mnarewec
Starting Member

10 Posts

Posted - 2010-08-18 : 17:36:25

Ok Team,

The DDL for table is here.

Table DDL:
---------
CREATE TABLE [dbo].[EmpTallySheet] (
[EmpTallySheetID] [int] IDENTITY (1, 1) NOT NULL ,
[EmpRate] [float] NULL ,
[FieldDataID] [int] NULL ,
[EmpID] [char] (30) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT NULL ,
[PayWeekNumber] [int] NOT NULL ,
[TaskID] [int] NOT NULL ,
[CostCentreID] [char] (4) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT NULL ,
[EntryDate] [smalldatetime] NULL ,
[EmpTaskDate] [smalldatetime] NULL ,
[QTY] [float] NULL ,
[CropAge] [tinyint] NULL ,
[PayrollBatchID] [int] NULL ,
[DynamicsAccountCode] [char] (11) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
[PayrollProcessed] [bit] NULL ,
[FieldName] [char] (30) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
[CostCentreName] [char] (30) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT NULL ,
[TaskName] [char] (30) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NOT NULL ,
[PayWeekIndex] [int] NOT NULL ,
[TallySheetOpened] [bit] NOT NULL ,
[LockedBy] [char] (50) COLLATE SQL_Latin1_General_Pref_CP1_CI_AS NULL ,
[Valid] [bit] NULL ,
[OverRide] [bit] NULL
) ON [PRIMARY]
GO

Please NOTE:
------------
1. Based on a row information and some logic of the table above I am generating a view 'vw_TallySheet' which will have columns:
EmpId, TaskId, TaskDate,Qty, OutTurn, Bonus

2. X002548, Field TaskId can be used to as a 'Column Type' to different between different rows thus columns

3. I want to generate pivot based on view 'vw_TallySheet'

4. I can produce the actual spreadsheet of data if someone is willing to try this out.

Thanks
Marsh

mnarewec
Starting Member

10 Posts

Posted - 2010-08-18 : 17:40:22

quote:
Originally posted by madhivanan

quote:
Originally posted by mnarewec

quote:
Originally posted by pk_bohra

Hi,

My understanding is that you want to show the data for 2 weeks in a pivot.
If my understanding is correct then you can use dynamic pivot and pivot it base on date for 2 weeks. In front end you can get the day for the date and display it.

For understanding of dynamic pivot, have a look at:
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Thanks Bohra, But I am using SQL 2000. Will this work??

For version 2000, use this
http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

Madhivanan

Failing to plan is Planning to fail

Thank you so much !! I'll try this out and advise

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources