Pivot Query Example - does one exist with Datasource available

Kent,
Your example was very helpfull, I understand the dynamics and components
now.. Thankyou for your example!
I have one problem with it tho.. I revamped mine to match your structure,
however It's working all the way up until the point where it pivots...
Here's the Code:
with pos(fdate, accounttag, symbol, strikeprice, typecode, quantity,
username) as
(
SELECT TOP (100) PERCENT dbo.Trades.ForwardDate,
dbo.Accounts.AccountTag,
dbo.Commodity.Symbol,
dbo.Trades.StrikePrice,
dbo.Trades.TypeCode,
dbo.Trades.Quantity,
dbo.Users.UserShortName
FROM dbo.Users INNER JOIN
dbo.Commodity INNER JOIN
dbo.Accounts INNER JOIN
dbo.Trades ON dbo.Accounts.ID = dbo.Trades.AccountCode ON
dbo.Commodity.ID = dbo.Trades.CommodityCode ON
dbo.Users.ID = dbo.Trades.Trader
WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
dbo.Commodity.Symbol LIKE N'CL-OTC%') AND
(dbo.Accounts.SheetAccount = 1) AND
(dbo.Trades.ActiveFlag = 1) AND
(dbo.Users.UserShortName = N'Tony')
GROUP BY dbo.Trades.ForwardDate,
dbo.Accounts.AccountTag,
dbo.Commodity.Symbol,
dbo.Trades.StrikePrice,
dbo.Trades.TypeCode,
dbo.Trades.Quantity,
dbo.users.usershortname
ORDER BY dbo.Trades.ForwardDate
)
Select fdate, accounttag, symbol, strikeprice, typecode, quantity, username
From pos
PIVOT
(
Sum(quantity)
For AccountTag
IN([SU185],[MN053],[SU598],[SU959],[SU89
7],[SU896],[SU797],[SU524],[SU511],[
SU397],[SU960],[SU895],[SU935],[9SU809],
[9SU885],[MN051],[BW685])
) P
Everything works up until the Pivot part.
Quantity should be summed up for for each acct (the way the subjects are in
your query) and i need it to also include the forward date, etc... Not sure
how i would add additional columns.. would I use something like:
group(fdate),
group(typecode),
sum(quantitiy)
for( etc.....)
Just more dynamics I'm working out.
Thanks for your help and examples.
www.krushradio.com - Internet Radio for the rest of us
"Kent Tegels" wrote:

> Hello Daniel,
>
> Are you working with Reporting Services, is that what you mean by data sou
rce?
> Otherwise, does this what you're looking for?
> -- Prep tables
> create table dbo.marks
> (
> studentID char(2),
> subjectID tinyint,
> score tinyint
> )
> go
> create table dbo.subjects
> (
> subjectID tinyint,
> subjectName varchar(15)
> )
> go
> create table dbo.students
> (
> studentID char(2),
> studentName varchar(15)
> )
> go
> -- Spin up data
> insert into dbo.marks values('A1','1','75')
> insert into dbo.marks values('A1','2','70')
> insert into dbo.marks values('A1','3','80')
> insert into dbo.marks values('A1','4','85')
> insert into dbo.marks values('A1','5','90')
> insert into dbo.marks values('A2','1','70')
> insert into dbo.marks values('A2','2','75')
> insert into dbo.marks values('A2','3','80')
> insert into dbo.marks values('A2','4','90')
> insert into dbo.marks values('A2','5','80')
> insert into dbo.subjects values(1,'Maths')
> insert into dbo.subjects values(2,'Science')
> insert into dbo.subjects values(3,'Social')
> insert into dbo.subjects values(4,'English')
> insert into dbo.subjects values(5,'Tamil')
> insert into dbo.students values('A1','Selva')
> insert into dbo.students values('A2','Akbar')
> go
> -- solution
> with m(studID,studName,subject,score) as
> (
> select s.studentID,s.StudentName,b.subjectName,m.score
> from dbo.students s
> join dbo.marks m on s.studentID = m.studentID
> join dbo.subjects b on m.subjectID = b.subjectID
> )
> select StudID,StudName,Maths,Science,Social,Eng
lish,Tamil
> from m
> pivot
> (
> max(score)
> for subject in ([Maths],[Science],[Social],[English],[T
amil])
> ) p
>
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>If this methodology thrumps the task continue to explore.
If it doesn't you might want to check out the RAC utility @.
www.rac4sql.net
You can enjoy an omlette without knowing how to make it
can't you?:)
"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:C56506F1-5018-4143-8D98-F10BE4101241@.microsoft.com...
> Kent,
> Your example was very helpfull, I understand the dynamics and components
> now.. Thankyou for your example!
> I have one problem with it tho.. I revamped mine to match your structure,
> however It's working all the way up until the point where it pivots...
> Here's the Code:
> with pos(fdate, accounttag, symbol, strikeprice, typecode, quantity,
> username) as
> (
> SELECT TOP (100) PERCENT dbo.Trades.ForwardDate,
> dbo.Accounts.AccountTag,
> dbo.Commodity.Symbol,
> dbo.Trades.StrikePrice,
> dbo.Trades.TypeCode,
> dbo.Trades.Quantity,
> dbo.Users.UserShortName
> FROM dbo.Users INNER JOIN
> dbo.Commodity INNER JOIN
> dbo.Accounts INNER JOIN
> dbo.Trades ON dbo.Accounts.ID = dbo.Trades.AccountCode ON
> dbo.Commodity.ID = dbo.Trades.CommodityCode ON
> dbo.Users.ID = dbo.Trades.Trader
> WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
> dbo.Commodity.Symbol LIKE N'CL-OTC%') AND
> (dbo.Accounts.SheetAccount = 1) AND
> (dbo.Trades.ActiveFlag = 1) AND
> (dbo.Users.UserShortName = N'Tony')
> GROUP BY dbo.Trades.ForwardDate,
> dbo.Accounts.AccountTag,
> dbo.Commodity.Symbol,
> dbo.Trades.StrikePrice,
> dbo.Trades.TypeCode,
> dbo.Trades.Quantity,
> dbo.users.usershortname
> ORDER BY dbo.Trades.ForwardDate
> )
> Select fdate, accounttag, symbol, strikeprice, typecode, quantity,
username
> From pos
> PIVOT
> (
> Sum(quantity)
> For AccountTag
>
IN([SU185],[MN053],[SU598],[SU959],[SU89
7],[SU896],[SU797],[SU524],[SU511],[
SU397],[SU960],[SU895],[SU935],[9SU809],
[9SU885],[MN051],[BW685])
> ) P
> Everything works up until the Pivot part.
> Quantity should be summed up for for each acct (the way the subjects are
in
> your query) and i need it to also include the forward date, etc... Not
sure
> how i would add additional columns.. would I use something like:
> group(fdate),
> group(typecode),
> sum(quantitiy)
> for( etc.....)
> Just more dynamics I'm working out.
> Thanks for your help and examples.
> --
> www.krushradio.com - Internet Radio for the rest of us
>
> "Kent Tegels" wrote:
>
source?|||I know you are marveling in your own genius...but please, don't spam my
posts. If you have something that is relevant to my post, which is regardin
g
how to use the pivot table feature in SQL2005, then by all means, add your
comments or thoughts.
If your only quest is to advertise something, then don't post. You could
very well be delaying a decent response by someone...if they're thinking tha
t
the post has been answered.
Going thru these forums, I've seen quite a few of your ads, and to be
honest, if you grasp of something as simple as html has any bearing on your
ability to code components, I would be afraid..very afraid.
Please respect the fact that I'm here looking for Answers and Ideas, not
commercials.
~Daniel Regalia
--
www.krushradio.com - Internet Radio for the rest of us
"Pike" wrote:

> If this methodology thrumps the task continue to explore.
> If it doesn't you might want to check out the RAC utility @.
> www.rac4sql.net
> You can enjoy an omlette without knowing how to make it
> can't you?:)
>
> "Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in messag
e
> news:C56506F1-5018-4143-8D98-F10BE4101241@.microsoft.com...
> username
> IN([SU185],[MN053],[SU598],[SU959],[SU89
7],[SU896],[SU797],[SU524],[SU511]
,[
> SU397],[SU960],[SU895],[SU935],[9SU809],
[9SU885],[MN051],[BW685])
> in
> sure
> source?
>
>|||"Daniel Regalia" <DanielRegalia@.discussions.microsoft.com> wrote in message
news:4D4899D0-6DAA-4CC5-90E3-44A5E390E15D@.microsoft.com...
> I know you are marveling in your own genius...
Shows insight.
> but please, don't spam my posts.
Noted.
:)|||Hello Daniel,

> Here's the Code:
> with pos(fdate, accounttag, symbol, strikeprice, typecode, quantity,
> username) as
> (
> SELECT TOP (100) PERCENT dbo.Trades.ForwardDate,
> dbo.Accounts.AccountTag,
> dbo.Commodity.Symbol,
> dbo.Trades.StrikePrice,
> dbo.Trades.TypeCode,
> dbo.Trades.Quantity,
> dbo.Users.UserShortName
> FROM dbo.Users INNER JOIN
> dbo.Commodity INNER JOIN
> dbo.Accounts INNER JOIN
> dbo.Trades ON dbo.Accounts.ID = dbo.Trades.AccountCode ON
> dbo.Commodity.ID = dbo.Trades.CommodityCode ON
> dbo.Users.ID = dbo.Trades.Trader
> WHERE (dbo.Commodity.Symbol LIKE N'CL%' OR
> dbo.Commodity.Symbol LIKE N'CL-OTC%') AND
> (dbo.Accounts.SheetAccount = 1) AND
> (dbo.Trades.ActiveFlag = 1) AND
> (dbo.Users.UserShortName = N'Tony')
> GROUP BY dbo.Trades.ForwardDate,
> dbo.Accounts.AccountTag,
> dbo.Commodity.Symbol,
> dbo.Trades.StrikePrice,
> dbo.Trades.TypeCode,
> dbo.Trades.Quantity,
> dbo.users.usershortname
> ORDER BY dbo.Trades.ForwardDate
> )
> Select fdate, accounttag, symbol, strikeprice, typecode, quantity,
> username
> From pos
> PIVOT
> (
> Sum(quantity)
> For AccountTag
> IN([SU185],[MN053],[SU598],[SU959],[SU89
7],[SU896],[SU797],[SU524],[SU
> 511],[SU397],[SU960],[SU895],[SU935],[9S
U809],[9SU885],[MN051],[BW685]
> )
> ) P
> Quantity should be summed up for for each acct (the way the subjects
> are in
> your query) and i need it to also include the forward date, etc...
How do you mean "include?" The rows coming out of the CTE already those fiel
ds,
so they should be showing up as members of the row...
Are you trying to build a cube, maybe?
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/