Please Help In This Formula

HELLO FRIEND,

I have accounting app and i have some problem in crystal report formula to get statement of account

for example bank statement .


Dr Cr Bal

0 100 100

0 150 350

50 0 300

20 0 280

0 140 420

PLEASE IF U KNOW ANY FORMULA TO SOLVE THIS PROBLE SEN IT

THANKStry this formula in detail section

whileprintingrecords;
numbervar a;
if dr then
a=a+dr
else
a=a-cr


if u need to reset this formula to zero , make another formula and use it in group section

whileprintingrecords;
numbervar a;
a:=0;

Please help

I have a certain problem that I'm going to try and demonstrate using the northwind
database as an example.

I'm trying to write a stored procedure in SQL Server 2000, I am using the products table and
the order details table.

The first procedure I wrote uses a left outer join to show all the products and if the product
doesn't show up in the order details table it will show up with a zero no problem the stored procedure
is as follows and works great.

ALTER PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName

Now I change the stored procedure to filter the second table by an orderid and now the only products
that show up are the products that exist in the second table.

My question is how do I show all the products like in the first stored procedure
and filter the stored procedures second table and still show all the products
including the ones that don't show up.

I wrote the second stored procedure and like I said it only shows the products that show up in the second table.

ALTER PROCEDURE dbo.StoredProcedure2
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]

FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID

GROUP BY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

HAVING (dbo.[Order Details].OrderID = 10248)

I would really appreciate if somebody codes demonstrate to me how to do this
thanks in advance.This is how it hsould be:

SELECT dbo.Products.ProductID, dbo.Products.ProductName,COUNT(dbo.[Order Details].ProductID)AS[in orders]
FROM dbo.ProductsLEFTOUTERJOIN
dbo.[Order Details]ON dbo.Products.ProductID= dbo.[Order Details].ProductID
WHERE dbo.[Order Details].OrderID= 10248
GROUPBY dbo.Products.ProductID, dbo.Products.ProductName, dbo.[Order Details].OrderID

|||Thank you very much for your quick response ndinakar,
but the procedure you wrote gives the same result as the second procedure
I wrote.

What I wanted to do was show all the products that exist in the products table
like in the first procedure which shows all the products and products that don't show up
in the second table show up with a zero I would like to do the same thing but when I filter
the second table by the orderId the only result I get is the products that show up in the second table not all the products.

How can I show all the products from the products table
and still filter the productID in the second table|||Of course it only shows products from the second table. Your WHERE clause tells it to only show specific products from the second table.

Your question makes little sense...|||I showed have explained what I was trying to do in the first place,

I've written a program in ASP.NEV VB I have a drop-down list in a data grid
the drop-down list contains instructors names and the value contains their ID

The drop-down list data source is the instructors table each time and instructor makes a dive
it goes into the orders table by the instructors ID.

What I was trying to do is get a list of all the instructors and their ID and see how many times they show up
in the orders table for certain dates and arrange the drop-down list according to the amount of dive's
the instructor made during that date.

----------Instructors table----------

Inst_Idint40
Inst_Full_Namenvarchar500
Activebit10

-------Orders Table --------------

Order_Idint40
Activity_Idint40
Inst_Idint41
Client_Namenvarchar500
Telnvarchar301
Paymentint40
Voucher_Nonvarchar501
Agent_Idint41
Reservation_Noint41
Worker_Idint41
Order_Date_Timedatetime80
Equipmentnvarchar2|||Try something like:

SELECT dbo.Products.ProductID, dbo.Products.ProductName, ISNULL(COUNT(dbo.[Order Details].ProductID),0 AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
dbo.[Order Details] ON dbo.Products.ProductID = dbo.[Order Details].ProductID
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)

This will give you a 0 value for products with no corresponding orders.

Does this help, or am I still misunderstanding the requirements?|||OK, I see what I was missing. That OrderDate requirement.

No problem. Just do your OUTER JOIN to an inline table (or subquery).

LEFT OUTER JOIN
 (SELECT ProductID, DetailID
 FROM [Order Details]
 WHERE OrderDate = @.OrderDate) AS OrderDetails

Now just treat your virtual OrderDetails table the same as you would your real OrderDetails table. The rest of the query should be the same.|||I didn't exactly understand how to do it I am quite new to SQL Server
I tried copying your script like so

CREATE PROCEDURE dbo.StoredProcedure1
AS
SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(dbo.[Order Details].ProductID) AS [in orders]
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails

GO

But it did not work i probably don't understand how to make an inline table
if you could just give me a little more help thanks a lot|||SELECT dbo.Products.ProductID, dbo.Products.ProductName, COUNT(OrderDetails.DetailID) AS InOrders
FROM dbo.Products LEFT OUTER JOIN
(SELECT ProductID, DetailID
FROM [Order Details]
WHERE OrderDate = @.OrderDate) AS OrderDetails
GROUP BY dbo.Products.ProductID, dbo.Products.ProductName
ORDER BY (3)|||I tried using the procedure you wrote and then tried it with my actual table
and each time I get the same error incorrect Syntex neither the keywords 'group'
do you have any suggestions but I've also attached my actual procedure.

---------------------------

ALTER PROCEDURE dbo.GetInstructors
(@.Id int)
AS

SELECT dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name, COUNT(dbo.Orders_Sub.Inst_Id) AS Expr1
FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
GROUP BY dbo.Instructors.Inst_Id, dbo.Instructors.Inst_Full_Name
ORDER BY (3)

---------------------------

I really appreciate the help you giving me thank you in advance again.|||Sorry. I left out the join condition:

FROM dbo.Instructors LEFT OUTER JOIN
(SELECT dbo.Orders_Sub.Inst_Id
FROM dbo.Orders_Sub
WHERE dbo.Orders_Sub.Order_Id = @.Id) AS Orders_Sub
ON dbo.Instructors.Inst_Id = Orders_Sub.Inst_Id

|||Thank you very much for your help it works like a dream.

please give any example for using UpdateQueryColumns Property

please help me...

what i required is, When a table is updated i want to get the column names
that affected , I think that UpdateQueryColumns Propertey may help me..

If what i am thinking is correct,please specify how to use that propertey,

otherwise specify how i can satisfy my requirement..

Thanks in advance........

Quote:

Originally Posted by ramesh1210

please help me...

what i required is, When a table is updated i want to get the column names
that affected , I think that UpdateQueryColumns Propertey may help me..

If what i am thinking is correct,please specify how to use that propertey,

otherwise specify how i can satisfy my requirement..

Thanks in advance........

create a trigger. use the UPDATE(field) function

please correct me if I'm wrong about relational dbase setup

http://www.wowcentral.com/random/relations_example.gif
(I whipped up this little example in access really quick so I could kind of
illustrate it)
Okay, I have a dbase with info about a game and stuff.
There are races, and classes and skills etc.
Races have many classes, and classes have many skills
Now, I need to display a page about a race...and on that page, it will
display which classes are available to it.
So, you click on a class, and that will load a page about that class
describing it and what skills are available to that class.
Now, if each race only had one class, I would just make a cell on the races
table called class_id. But because each race (8 of them) will have up to 8
classes I gotta make a second table that says which classes are available to
each race.
I did this in tblRclasses.
I did the same thing for classes and skills.
Now, my friend is telling me that this is not a relational database because
the tables aren't sharring info but, like I said a second ago, if each race
only had one class, I wouldn't need the "middle man" table.
I don't see how this is NOT relational, and to me, I don't think it gets
anymore relational then this.
It is still sharing information from table to table, just not directly from
one to another because there are so many items per, I can't just add a cell
to the races table for every class that there is.
Ontop of this, I'll probably have a page were people can just check out
skills and stuff, and when they click on that skill, it will say, this skill
is available to this class...so from there, I'll be linking back to classes
and from classes to races.
So, I need you guys to set me or him straight.
Am I infact totally off and what I've described is NOT a relational dbase
structure or is my friend wrong and this IS a relational dbase structure?
To him, (if I'm understanding correctly) it would only be relational if it
was one table to another ie, races directly to classes. But because I'm
doing races to rclasses/rclasses to classes it's not relationa.
Thanks
Robso, I say one to one or one to many is relational
but if I'm hearing him correctly he's saying only one to one is relational|||Robert ,it will be very useful If you post DDL + sample data + expected
result.
"Robert Blackwell" <robbieatwowcentraldotcom> wrote in message
news:epQlBrpdDHA.3836@.tk2msftngp13.phx.gbl...
> http://www.wowcentral.com/random/relations_example.gif
> (I whipped up this little example in access really quick so I could kind
of
> illustrate it)
> Okay, I have a dbase with info about a game and stuff.
>
> There are races, and classes and skills etc.
> Races have many classes, and classes have many skills
> Now, I need to display a page about a race...and on that page, it will
> display which classes are available to it.
> So, you click on a class, and that will load a page about that class
> describing it and what skills are available to that class.
> Now, if each race only had one class, I would just make a cell on the
races
> table called class_id. But because each race (8 of them) will have up to 8
> classes I gotta make a second table that says which classes are available
to
> each race.
> I did this in tblRclasses.
> I did the same thing for classes and skills.
> Now, my friend is telling me that this is not a relational database
because
> the tables aren't sharring info but, like I said a second ago, if each
race
> only had one class, I wouldn't need the "middle man" table.
> I don't see how this is NOT relational, and to me, I don't think it gets
> anymore relational then this.
> It is still sharing information from table to table, just not directly
from
> one to another because there are so many items per, I can't just add a
cell
> to the races table for every class that there is.
> Ontop of this, I'll probably have a page were people can just check out
> skills and stuff, and when they click on that skill, it will say, this
skill
> is available to this class...so from there, I'll be linking back to
classes
> and from classes to races.
> So, I need you guys to set me or him straight.
> Am I infact totally off and what I've described is NOT a relational dbase
> structure or is my friend wrong and this IS a relational dbase structure?
> To him, (if I'm understanding correctly) it would only be relational if it
> was one table to another ie, races directly to classes. But because I'm
> doing races to rclasses/rclasses to classes it's not relationa.
> Thanks
> Rob
>|||I don't have any data etc yet because I'm trying to create the structure
properly first :(
But, as far as results go, take this website for example (it's the official
page of the fansite I'm working on)
http://www.worldofwarcraft.com/wow/townhall/
For reasons I don't understand, that entire site is static. But what I'm
planning to create on my page, is totally dynamic.
So say on that main page, you have the races and the images loaded
statically maybe, but the list of monsters is loaded dynamically and so are
the regions via repeat regions and stuff.
So, let's click on a race...Night Elves.
When you go to the NE page, all that stuff is loaded dynamically from the
dbase, the image, description, and then the the classes and races.
Now, i'm using the "inbetween" table rclasses to link races and classes
together ande that's how I'd get the info for classes. On that page, if you
click a class, it loads another dynamic page showing the information about
that class...and then on the class page, it will not only show the
informtaion about that class, but when more info is made available to the
public, I'm going to list which skills are available to each class...then
when you click the skill it loads up a skills description page etc...
And this goes for everything I'm trying to accomplish. Linking races,
classes, skills, regions and monsters together because races and monsters
come from a region and classes come from a race and skills come from classes
etc. etc. etc.
That's the best I can do to describe my intended out put I think.|||Robert
> For reasons I don't understand, that entire site is static.
If I undestand you correctly you are still planing you business logic of the
application.
Do you have database ?
If you do what is relationships between tables and what are the tables?
If you need some output report to be created by T-SQL please populate dummy
data and show us what is expected result.
"Robert Blackwell" <robbieatwowcentraldotcom> wrote in message
news:Oq0f1BqdDHA.2112@.TK2MSFTNGP10.phx.gbl...
> I don't have any data etc yet because I'm trying to create the structure
> properly first :(
>
> But, as far as results go, take this website for example (it's the
official
> page of the fansite I'm working on)
> http://www.worldofwarcraft.com/wow/townhall/
> For reasons I don't understand, that entire site is static. But what I'm
> planning to create on my page, is totally dynamic.
> So say on that main page, you have the races and the images loaded
> statically maybe, but the list of monsters is loaded dynamically and so
are
> the regions via repeat regions and stuff.
> So, let's click on a race...Night Elves.
> When you go to the NE page, all that stuff is loaded dynamically from the
> dbase, the image, description, and then the the classes and races.
> Now, i'm using the "inbetween" table rclasses to link races and classes
> together ande that's how I'd get the info for classes. On that page, if
you
> click a class, it loads another dynamic page showing the information about
> that class...and then on the class page, it will not only show the
> informtaion about that class, but when more info is made available to the
> public, I'm going to list which skills are available to each class...then
> when you click the skill it loads up a skills description page etc...
> And this goes for everything I'm trying to accomplish. Linking races,
> classes, skills, regions and monsters together because races and monsters
> come from a region and classes come from a race and skills come from
classes
> etc. etc. etc.
> That's the best I can do to describe my intended out put I think.
>|||One-to-many, one-to-one and many-to-many are all perfectly relational. It is all about how you model
the relations in the data model:
One to one: A common column in both tables which is unique (PK or UNIQUE constraint) in both tables.
One-to-many: A common column in both tables. In one table it is PK (the referenced table), in the
other it allow duplicates and is a FOREIGN KEY that references the PK of the referenced table.
Many-to-many: A table in between the two tables, where you keep the PK from both tables (as FK
referencing each of the other table's PK). This table creates your many-to-many relationships.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Robert Blackwell" <robbieatwowcentraldotcom> wrote in message
news:OuK0sspdDHA.3128@.TK2MSFTNGP09.phx.gbl...
> so, I say one to one or one to many is relational
> but if I'm hearing him correctly he's saying only one to one is relational
>

PL/SQL help - split an address

I have a single address column that i want to split.
For example I have an address with carriage returns like:

address
----------
Administration Tech Services

1234 Elm Avenue

West Building

I would like a SELECT query to split this address column into 3 like:
address address2 address3
---- ---- ----
Administration Tech Services 1234 Elm Avenue West BuildingYou may need to create a function like this:
Create Or Replace Function Get_Addr
(P_Line Number, P_Addr Varchar2)
Return Varchar2 Is
Type Addr_Typ Is Table Of Varchar2(1000);
V_Addr Addr_Typ;
V_Tmp Varchar2(1000);
I Pls_Integer;
J Pls_Integer;
K Pls_Integer;
L Pls_Integer;
Begin
V_Tmp:=Rtrim(P_Addr)||Chr(10);
For I In 1..P_Line Loop
V_Addr(I):='';
End Loop;
I:=0;
L:=Length(V_Tmp);
While (L > 0)
Loop
K:=Instr(V_Tmp,Chr(10));
If K = 0 Then
Exit;
End If;
J:= K-1;
If J > 0 Then
I:=I+1;
V_Addr(I):= Substr(V_Tmp,1,J);
V_Tmp:=Rtrim(Substr(V_Tmp||' ',K+1));
End If;
L:=Length(V_Tmp);
End Loop;
Return V_Addr(P_Line);
End;
/
And use it like:

UPDATE MyAddrTab
SET Address2=Get_Addr(2,Address)
, Address3=Get_Addr(3,Address);
COMMIT;
UPDATE MyAddrTab
SET Address=Get_Addr(1,Address);
COMMIT;
:eek:

PS: You will need to validate parameters!

PIVOT with dynamic columns names created

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @.period
OR Period = @.period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @.period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriod
A 400 3000
B 400 100
C 470 300
D 800 2500
E 1000 1900

What if the value @.period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @.period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @.period but it doesn't work.

Thanks in advance.

Kenny

This is a one drawback to the current Pivot feature. You will have to use dynamic sql for this.

Itzik has written a good article on this.

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

pivot t-sql adding a percentage column

in a cross table result is very common to add a percentage column in respect

of the sum of another column

for example

x z%

a 2 0,46 = 2/(2+5+6)

b 5 0,38 = 5/(2+5+6)

c 6 0,15 = 6/(2+5+6)

is there an easy way to do that in t-sql using the pivot command ?

This was going to be my first answer

"Not easily that I can think of because it requires knowledge of the other rows in the result set and that is where SQL is not very good. That is performing calculations at two levels one aggregate is across th

You need to find the total and then calculate the percentage. This is normally done using a derived table"

However in digging I looked into windowing. This is a new feature that can be used with the new ranking functions as well as the normal aggregates.

In your situation you can do

select x, 1.0*x / sum(x) over (parition by 1) percentage

from mytable

What this does is that it performs a sum of x over the partition of 1 (as this is the same for each row it does a sum across the whole resultset).

Pivot table with no numeric aggregation?

I'm trying to pivot the data in a table but not aggregate numeric data, just rearrange the data in columns as opposed to rows.

For example, my initial table could be represented by this structure:

ID

Label

Value

1

a

val1

1

b

val2

1

c

val3

1

d

val4

1

e

val5

2

a

val6

2

b

val7

2

c

val8

2

d

val9

2

e

val10

and I am trying to get it into the following structure:

ID

Label a

Label b

Label c

Label d

Label e

1

val1

val2

val3

val4

val5

2

val6

val7

val8

val9

val10

The number of labels is known beforehand, so we know how many columns to make. I can get a first step at pivoting it with 'case' statements, but obviously still end up with a row for each Label/Value pair since there is no aggregate function being applied. So I'm not sure how to get it flattened down like shown above?

Thanks for any replies on this,

Eric

In SQL Server 2005, you can use the PIVOT operator like:

select pt.ID, pt. as [Label a], pt. as [Label b]...

from tbl as t

pivot (min(t.value) for t.Label in ( , , ....)) as pt

In older versions, you can do below:

select t.ID

, min(case t.Label when 'a' then value end) as [Label a]

, min(case t.Label when 'b' then value end) as [Label b]

...

from tbl as t

group by t.ID

|||Thanks, works perfect! I had no idea you could use the min function on varchar.

Pivot Query Example - does one exist with Datasource available?

Hi All,
I posted an earlier question here a w ago, it was never answerd.. I'll
try to simplify it a bit.
I need to get an example of a Pivot Query, with a datasource. i've looked
at alot of them online, and They don't really make sense...either they're to
o
general, or they're without datasource. I need to see one in a real world
environment..
Can someone point me to an example? I've been fighting with the one in my
previous post, and i think i'm just digging myself deeper into a hole not
knowing what i'm adjusting and tweaking. Im using stored procedures to do
this...if that makes a differnce. It's not that I'm a unskilled in SQL
Server, I'm just totally lost with the Pivot Querys.
Thanks
Daniel Regalia
www.krushradio.com - Internet Radio for the rest of usHello Daniel,

> I posted an earlier question here a w ago, it was never answerd..
> I'll try to simplify it a bit.
> I need to get an example of a Pivot Query, with a datasource. i've
> looked at alot of them online, and They don't really make
> sense...either they're too general, or they're without datasource. I
> need to see one in a real world environment..
> Can someone point me to an example? I've been fighting with the one
> in my previous post, and i think i'm just digging myself deeper into a
> hole not knowing what i'm adjusting and tweaking. Im using stored
> procedures to do this...if that makes a differnce. It's not that I'm
> a unskilled in SQL Server, I'm just totally lost with the Pivot
> Querys.
Are you working with Reporting Services, is that what you mean by data sourc
e?
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/

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/

Pivot Example when you don't know the exact values to Pivot on

Say, I have the following temporary table (@.tbl) where the QuestionID field will change values over time

Survey QuestionID Answer
1 1 1
1 2 0
2 1 1
2 2 2

I'd like to perform a pivot on it like this: select * from @.tbl Pivot (min(Answer) for QuestionID in ([1], [2])) as PivotTable

...however, I can't just name the [1], [2] values because they're going to change.

Instead of naming the values like this:
for QuestionID in ([1], [2], [3], [4])

I tried something like this:
for QuestionID in (select distinct QuestionID from @.tbl)

but am getting a syntax error. Is it possible to set up a pivot like this:
select * from @.tbl Pivot (min(Answer) for Question_CID in (select distinct @.QuestionID from @.tbl)) as PivotTable

or does anyone know another way to do it?

You cannot use a subquery in the IN for your pivot. I wish we could. Here is a dynamic solution:

Code Snippet

Create Table #tb1 (

Survey int,

QuestionID int,

Answer int

);

--Survey QuestionID Answer

--1 1 1

--1 2 0

--2 1 1

--2 2 2

--Sample Data

Insert Into #tb1 Values('1','1', '1');

Insert Into #tb1 Values('1','2', '0');

Insert Into #tb1 Values('2','1', '1');

Insert Into #tb1 Values('2','2', '2');

Insert Into #tb1 Values('3','3', '1');

--SELECT DISTINCT QuestionID FROM #tb1

SET

NOCOUNT ON

DECLARE

@.T AS TABLE(y nvarchar(20) NOT NULL PRIMARY KEY)

INSERT

INTO @.T SELECT DISTINCT QuestionID FROM #tb1

DECLARE

@.T1 AS TABLE(num int NOT NULL PRIMARY KEY)

DECLARE @.i AS int

SET @.i=1

WHILE @.i <20

BEGIN

INSERT INTO @.T1 SELECT @.i

SET @.i=@.i+1

END

DECLARE @.cols AS nvarchar(MAX), @.cols2 AS nvarchar(MAX),@.y AS nvarchar(20)

SET @.y = (SELECT MIN(y) FROM @.T)

SET @.cols = N''

SET @.cols2 = N''

WHILE @.y IS NOT NULL

BEGIN

SET @.cols = @.cols + N',['+CAST(@.y AS nvarchar(20))+N']'

SET @.cols2 = @.cols2 + N'+ coalesce(['+CAST(@.y AS nvarchar(20))+N'],'''')'

SET @.y = (SELECT MIN(y) FROM @.T WHERE y > @.y)

END

SET @.cols = SUBSTRING(@.cols, 2, LEN(@.cols))

SET @.cols2 = SUBSTRING(@.cols2, 2, LEN(@.cols2)-1)

DECLARE @.sql AS nvarchar(MAX)

SET @.sql = N'SELECT Survey, ' +@.cols + N' FROM (SELECT Survey, QuestionID, Answer FROM #tb1) as t

PIVOT (Min(Answer) FOR QuestionID IN(' + @.cols + N')) AS pvt'

EXEC sp_executesql @.sql

print @.sql

drop table #tb1

|||

Try:

Code Snippet

use tempdb

go

create table #t1 (

survey int,

questionid int,

answer int

)

insert into #t1 values(1,1, 1)

insert into #t1 values(1,2, 0)

insert into #t1 values(2,1, 1)

insert into #t1 values(2,2, 2)

insert into #t1 values(0,0, 1)

declare @.cols nvarchar(max)

declare @.sql nvarchar(max)

set @.cols = stuff(

(

select ',' + quotename(QuestionID)

from (select distinct QuestionID from #t1) as t

order by QuestionID

for xml path('')

), 1, 1, '')

set @.sql = N'

select

Survey, ' + @.cols + N'

from

#t1

pivot

(

min(Answer)

for QuestionID in (' + @.cols + N')

) as pvt

'

exec sp_executesql @.sql

drop table #t1

go

In case you do not want to see the NULL values, take a look to this post. There I posted a similar solution.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2123214&SiteID=1

Be careful with SQL Injection.

PIVOT on Steroids

http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html

AMB