Re: Pivot Query or Cross Tab queries
- From: "MChrist" <MChrist@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 22 Apr 2005 14:55:02 -0700
Thanks for your response Al. After scoping books and the help for several
hours that's the conclusion I came to also. Strange how the little baby
brother can do something that big brother can't. But that's as consistent as
the use of functions and naming conventions across MS software.
Have a great weekend.
Mark
"Al Newbie" wrote:
> I don't believe you can do crosstab queries within SQL Server like you can
> in Access
> "MChrist" <MChrist@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@xxxxxxxxxxxxxxxx
> >I can't seem to figure out how to create a cross tab or pivot query on
> >data.
> > If I was writing the SQL in Access I would write it like
> >
> > TRANSFORM SUM(DTL.Amount) AS Amount
> > SELECT
> > STC.ClientID
> > , STC.Client
> > , STC.SS_ID
> > , STC.Region
> > , STC.[Sales Director]
> > , STC.[Product Consultant]
> > , STC.CCC
> > FROM (SELECT
> > STC.DW_SELLER_ID AS ClientID
> > , STC.SELLER_NAME AS Client
> > , STC.SS_ID
> > , STC.TEAM_ACQ_NAME AS Region
> > , STC.ACCOUNT_MANAGER_NAME AS [Sales Director]
> > , STC.[Product Consultant]
> > , CTC.CLIENT_CENTRIC_CATEGORY_DSCR AS CCC
> > FROM dbo.tSeller_Team_Current AS STC
> > LEFT JOIN OPENQUERY(SSOT_DWDB,
> > 'SELECT DW_CLIENT_ID, CLIENT_CENTRIC_CATEGORY_DSCR
> > FROM DW.DW_SALES_CLIENT_TEAM_CURR') AS CTC
> > ON STC.DW_SELLER_ID = CTC.DW_CLIENT_ID
> > WHERE STC.Channel='RF'
> > AND STC.SELLER_ACTIVATION_DATE Is Not Null
> > AND SELLER_CLOSED_DATE Is Null) AS STC
> > LEFT JOIN dbo.tCRC_Details AS DTL
> > ON STC.ClientID=DTL.ClientID
> > GROUP BY
> > STC.ClientID
> > , STC.Client
> > , STC.SS_ID
> > , STC.Region
> > , STC.[Sales Director]
> > , STC.[Product Consultant]
> > , STC.CCC
> > PIVOT DTL.[Description]
> >
> > How could I write the query so it works in a View?
> >
> > Thanks in advance.
> >
> > Mark
> >
>
>
>
.
- References:
- Pivot Query or Cross Tab queries
- From: MChrist
- Re: Pivot Query or Cross Tab queries
- From: Al Newbie
- Pivot Query or Cross Tab queries
- Prev by Date: Re: Pivot Query or Cross Tab queries
- Next by Date: VIEWING A TABLES STRUCTURE AND INDEXES
- Previous by thread: Re: Pivot Query or Cross Tab queries
- Next by thread: VIEWING A TABLES STRUCTURE AND INDEXES
- Index(es):
Relevant Pages
|
|
