Pivot Query or Cross Tab queries

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
"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
>
|||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@.discussions.microsoft.com> wrote in message
> news:C5FE6FA4-8AB9-49DE-BA7F-1BD0F226B01A@.microsoft.com...
>
>