[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Crosstab Query

Ryan

3/28/2007 1:39:00 PM

I need help writing a cross-tab type query.
I want to show the details of each project, and a column for each funding
source (whether that project/funding source has an amount or not)
So the columns would be:
ProjectID
ProjectName
ProjectDesc
SourceAAmount
SourceBAmount
SourceCAmount

Heres the underlying data structure:

Table1 (Project Details)
ProjectID (PK)
ProjectName
ProjectDesc
etc.

Table2 (Amount)
FundingID (PK)
SourceID (FK)
ProjectID (FK)
FundingAmount

Table3 (Funding Sources)
SourceID
SourceName

If an amount doesn't exist for the particular funding source I want the
value to be 0 (IE no record in table 2 for that particular project/funding
source)
How would I accomplish this?

Thanks,
Ryan


8 Answers

xyb

3/28/2007 2:05:00 PM

0

On 3?28?, ??9?39?, "Ryan" <Tyv...@newsgroups.nospam> wrote:
> I need help writing a cross-tab type query.
> I want to show the details of each project, and a column for each funding
> source (whether that project/funding source has an amount or not)
> So the columns would be:
> ProjectID
> ProjectName
> ProjectDesc
> SourceAAmount
> SourceBAmount
> SourceCAmount
>
> Heres the underlying data structure:
>
> Table1 (Project Details)
> ProjectID (PK)
> ProjectName
> ProjectDesc
> etc.
>
> Table2 (Amount)
> FundingID (PK)
> SourceID (FK)
> ProjectID (FK)
> FundingAmount
>
> Table3 (Funding Sources)
> SourceID
> SourceName
>
> If an amount doesn't exist for the particular funding source I want the
> value to be 0 (IE no record in table 2 for that particular project/funding
> source)
> How would I accomplish this?
>
> Thanks,
> Ryan

select
t1.ProjectID,
t1. ProjectName,
t1.ProjectDesc,
SourceAAmount = isnull(sum(case when t3.SourceName = 'a' then
t2.FundingAmount else 0 end),0),
SourceBAmount = isnull(sum(case when t3.SourceName = 'b' then
t2.FundingAmount else 0 end),0),
SourceCAmount = isnull(sum(case when t3.SourceName = 'c' then
t2.FundingAmount else 0 end),0)
from t1 join t2 on t1.ProjectID = t2.ProjectID left join t3 on
t2.SourceID = t3.SourceID
group by t1.ProjectID,
t1. ProjectName,
t1.ProjectDesc,
t3.SourceName

Ryan

3/28/2007 2:44:00 PM

0

Thanks for the reply. Unfortunately this won't entirely work because there
are not a set amount of sources (SourceA, SourceB, etc). I want as many
(Source) columns as there are records in the Source table. I imagine I need
some sort of cursor.. how would I add that logic to this query?

"xyb" <xiangyuanbo@gmail.com> wrote in message
news:1175090725.325099.249030@l77g2000hsb.googlegroups.com...
On 3ÔÂ28ÈÕ, ÏÂÎç9ʱ39·Ö, "Ryan" <Tyv...@newsgroups.nospam> wrote:
> I need help writing a cross-tab type query.
> I want to show the details of each project, and a column for each funding
> source (whether that project/funding source has an amount or not)
> So the columns would be:
> ProjectID
> ProjectName
> ProjectDesc
> SourceAAmount
> SourceBAmount
> SourceCAmount
>
> Heres the underlying data structure:
>
> Table1 (Project Details)
> ProjectID (PK)
> ProjectName
> ProjectDesc
> etc.
>
> Table2 (Amount)
> FundingID (PK)
> SourceID (FK)
> ProjectID (FK)
> FundingAmount
>
> Table3 (Funding Sources)
> SourceID
> SourceName
>
> If an amount doesn't exist for the particular funding source I want the
> value to be 0 (IE no record in table 2 for that particular project/funding
> source)
> How would I accomplish this?
>
> Thanks,
> Ryan

select
t1.ProjectID,
t1. ProjectName,
t1.ProjectDesc,
SourceAAmount = isnull(sum(case when t3.SourceName = 'a' then
t2.FundingAmount else 0 end),0),
SourceBAmount = isnull(sum(case when t3.SourceName = 'b' then
t2.FundingAmount else 0 end),0),
SourceCAmount = isnull(sum(case when t3.SourceName = 'c' then
t2.FundingAmount else 0 end),0)
from t1 join t2 on t1.ProjectID = t2.ProjectID left join t3 on
t2.SourceID = t3.SourceID
group by t1.ProjectID,
t1. ProjectName,
t1.ProjectDesc,
t3.SourceName

TheSQLGuru

3/28/2007 3:20:00 PM

0

Check for an article by Itzik Ben-Gan in SQL Mag recently on dynamic
PIVOTing.

You cannot 'add' a cursor to the CASE statement code given. Also, you will
somehow HAVE to get the DISTINCT list of values you want to cross-tab by.
Gonna be quite inefficient I am betting!

--
TheSQLGuru
President
Indicium Resources, Inc.

"Ryan" <Tyveil@newsgroups.nospam> wrote in message
news:%23zPYKeUcHHA.4312@TK2MSFTNGP05.phx.gbl...
> Thanks for the reply. Unfortunately this won't entirely work because
> there are not a set amount of sources (SourceA, SourceB, etc). I want as
> many (Source) columns as there are records in the Source table. I imagine
> I need some sort of cursor.. how would I add that logic to this query?
>
> "xyb" <xiangyuanbo@gmail.com> wrote in message
> news:1175090725.325099.249030@l77g2000hsb.googlegroups.com...
> On 3ÔÂ28ÈÕ, ÏÂÎç9ʱ39·Ö, "Ryan" <Tyv...@newsgroups.nospam> wrote:
>> I need help writing a cross-tab type query.
>> I want to show the details of each project, and a column for each funding
>> source (whether that project/funding source has an amount or not)
>> So the columns would be:
>> ProjectID
>> ProjectName
>> ProjectDesc
>> SourceAAmount
>> SourceBAmount
>> SourceCAmount
>>
>> Heres the underlying data structure:
>>
>> Table1 (Project Details)
>> ProjectID (PK)
>> ProjectName
>> ProjectDesc
>> etc.
>>
>> Table2 (Amount)
>> FundingID (PK)
>> SourceID (FK)
>> ProjectID (FK)
>> FundingAmount
>>
>> Table3 (Funding Sources)
>> SourceID
>> SourceName
>>
>> If an amount doesn't exist for the particular funding source I want the
>> value to be 0 (IE no record in table 2 for that particular
>> project/funding
>> source)
>> How would I accomplish this?
>>
>> Thanks,
>> Ryan
>
> select
> t1.ProjectID,
> t1. ProjectName,
> t1.ProjectDesc,
> SourceAAmount = isnull(sum(case when t3.SourceName = 'a' then
> t2.FundingAmount else 0 end),0),
> SourceBAmount = isnull(sum(case when t3.SourceName = 'b' then
> t2.FundingAmount else 0 end),0),
> SourceCAmount = isnull(sum(case when t3.SourceName = 'c' then
> t2.FundingAmount else 0 end),0)
> from t1 join t2 on t1.ProjectID = t2.ProjectID left join t3 on
> t2.SourceID = t3.SourceID
> group by t1.ProjectID,
> t1. ProjectName,
> t1.ProjectDesc,
> t3.SourceName
>
>


Rafael Lenartowicz

3/28/2007 4:12:00 PM

0

check this one out : http://weblogs.sqlteam.com/jeffs/articles...
I used it once after slight modifications and it worked quite well for me.
r


"Ryan" <Tyveil@newsgroups.nospam> wrote in message
news:uFaVK6TcHHA.2068@TK2MSFTNGP06.phx.gbl...
>I need help writing a cross-tab type query.
> I want to show the details of each project, and a column for each funding
> source (whether that project/funding source has an amount or not)
> So the columns would be:
> ProjectID
> ProjectName
> ProjectDesc
> SourceAAmount
> SourceBAmount
> SourceCAmount
>
> Heres the underlying data structure:
>
> Table1 (Project Details)
> ProjectID (PK)
> ProjectName
> ProjectDesc
> etc.
>
> Table2 (Amount)
> FundingID (PK)
> SourceID (FK)
> ProjectID (FK)
> FundingAmount
>
> Table3 (Funding Sources)
> SourceID
> SourceName
>
> If an amount doesn't exist for the particular funding source I want the
> value to be 0 (IE no record in table 2 for that particular project/funding
> source)
> How would I accomplish this?
>
> Thanks,
> Ryan
>


Steve Dassin

3/28/2007 6:38:00 PM

0

Mr. President. just as there is no reason for a woman to suffer from PMS in
this day and age there is no reason a user should suffer from it (Pivot
Madness Syndrome) either.
The medication, RAC, requires no prescription. And like the Porsche there is
no substitute.

www.rac4sql.net

TheSQLGuru

3/28/2007 9:40:00 PM

0

1) My wife is an OB/GYN doctor, so I know for a fact that there ARE reasons
women HAVE to suffer from PMS and in some cases there isn't a thing you can
do about it.

2) Pivot Madness Syndrome?? PuhLEASE :)

3) I have owned a Porsche (came with the wife), and found it one of the
worst cars I have ever had. Ridiculously expensive to purchase AND
maintain. Also INCREDIBLY uncomfortable for people like me without an
excess of body fat. I will agree that it was a BLAST to drive for short
distances however!

4) Congrats for apparently creating a GUI for stuff that people can code
themselves. I hope you sell lots of it. I ALSO hope you keep your
solicitations off of the official Microsoft forums.


--
TheSQLGuru
President
Indicium Resources, Inc.

"Steve Dassin" <steve@nospamrac4sql.net> wrote in message
news:%23s9EhhWcHHA.4872@TK2MSFTNGP03.phx.gbl...
> Mr. President. just as there is no reason for a woman to suffer from PMS
> in this day and age there is no reason a user should suffer from it (Pivot
> Madness Syndrome) either.
> The medication, RAC, requires no prescription. And like the Porsche there
> is no substitute.
>
> www.rac4sql.net
>
>


TheSQLGuru

3/28/2007 9:44:00 PM

0

Rafael, there is one bad thing about the CrossTab2 sproc listed in the blog.
SET ANSI_WARNINGS OFF at the top forces an immediate recompile of the sproc.
Typically the only thing you should have is SET NOCOUNT ON. I had a client
CRUSHING their servers with recompiles due to inappropriate SET statements
in their sprocs.



--
TheSQLGuru
President
Indicium Resources, Inc.

"Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message
news:%237ZFcPVcHHA.4684@TK2MSFTNGP06.phx.gbl...
> check this one out : http://weblogs.sqlteam.com/jeffs/articles...
> I used it once after slight modifications and it worked quite well for me.
> r
>
>
> "Ryan" <Tyveil@newsgroups.nospam> wrote in message
> news:uFaVK6TcHHA.2068@TK2MSFTNGP06.phx.gbl...
>>I need help writing a cross-tab type query.
>> I want to show the details of each project, and a column for each funding
>> source (whether that project/funding source has an amount or not)
>> So the columns would be:
>> ProjectID
>> ProjectName
>> ProjectDesc
>> SourceAAmount
>> SourceBAmount
>> SourceCAmount
>>
>> Heres the underlying data structure:
>>
>> Table1 (Project Details)
>> ProjectID (PK)
>> ProjectName
>> ProjectDesc
>> etc.
>>
>> Table2 (Amount)
>> FundingID (PK)
>> SourceID (FK)
>> ProjectID (FK)
>> FundingAmount
>>
>> Table3 (Funding Sources)
>> SourceID
>> SourceName
>>
>> If an amount doesn't exist for the particular funding source I want the
>> value to be 0 (IE no record in table 2 for that particular
>> project/funding source)
>> How would I accomplish this?
>>
>> Thanks,
>> Ryan
>>
>
>


John Robertson

2/19/2011 10:45:00 PM

0

BrianJ wrote:
> john, thanks for replying! hope to pick yourbrain here. the game
> worked fine prior to having its problems now so its not a previously
> unknown condition game. itseems to try to boot and i get flickers on
> the four led indicators. most of the time no boot at all sometimes
> led 1,3,4 light solid. replaced the 2101's and the 5101 withno
> change. allvoltages are strong and steady tested at theboards under
> load. best guess right now is rom failure?
>

Hi Brian,

Well - maybe a ROM issue...maybe not. Could be a bad 74LS244 as well,
or, well, something else - over the years I've found most of the chips
can go bad given half a chance.

See I plug these boards into my Fluke 9010A and exercise the BUS and run
some tests to figure out which part has failed. Almost anything else is
'shotgunning' and may cause more damage than it solves - damaged traces,
defective parts installed (how do you verify your replacement
parts?)...it is surprisingly easy to turn a board worth $300 into junk.

This is why I charge to fix boards - it isn't quick and easy for the
most part, except for people like me who have a bit (over 45 years in my
case) experience and the proper test gear.

John :-#)#

--
(Please post followups or tech enquiries to the newsgroup)
John's Jukes Ltd. 2343 Main St., Vancouver, BC, Canada V5T 3C9
Call (604)872-5757 or Fax 872-2010 (Pinballs, Jukes, Video Games)
www.flippers.com
"Old pinballers never die, they just flip out."