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
[quoted text, click to view] "Al Newbie" wrote:
> I don't believe you can do crosstab queries within SQL Server like you can
> in Access
> "MChrist" <MChrist@discussions.microsoft.com> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@microsoft.com...
> >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
> >
>
>
I don't believe you can do crosstab queries within SQL Server like you can
in Access
[quoted text, click to view] "MChrist" <MChrist@discussions.microsoft.com> wrote in message
news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@microsoft.com...
>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
>
