No Crosstab Like Access?

I'm trying to port a dymanically renderred form from a .mdb to a .adp. In the Access .mdb. The form is a representation of a crosstab query with unknown column headings (hence, the need for the form to be dynamically rendered at runtime).

I tried to port the crosstab from the .mdb to a stored procedure, but SQL Server doesn't like TRANSFORM, and perhaps the PIVOT as well. How do you do this in sql server?:

TRANSFORM Count(d.CAR_INIT) AS CountOfCAR_INIT
SELECT b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
FROM ((TSA_HS_MPCT_CNT a INNER JOIN TSA_HS_COMB2 b ON a.RECC_COMB_ID = b.COMB_ID)
INNER JOIN TSA_HS_WKLD c ON b.WKLD_ID = c.WKLD_ID) INNER JOIN TSA_HS_OBJ_TRN d ON c.WKLD_ID = d.WKLD_ID
GROUP BY b.WKLD_SEQ, c.TRN_ID, c.TRK_NBR
PIVOT d.LST_HMP_DTM + d.OBJ_DEP_TRN

Thanks,
CarlNot in SQL 2000 (or SQL 7.0). PIVOT supposedly works in SQL 2005, but I have not seen it/worked with it.

Look at AGS Crosstab or RAC for SQL. Both are 3rd party add-ons for SQL.

Regards,

hmscott

Newbie: XML as datasource

Hi everyone,
I have a Web Form with data fields. What I want to do is to save these
fields into an XML file (dynamically), then hook this XML (as a data source)
to a SQL Report so that I don't have to save these data into a SQL Server
database. Is it possible? If it is then can someone show me how.
Any suggestion is greatly appreciated.
Many thanks in advance.There is currently no support for accessing an XML data source. You either
have to write your own custom XML data source extension or look on the market
for an XML OLEDB provider. Or search MSDN for sample code that implements an
XML data source extension. My current understanding is that it will be
included with the SQL Server 2005 release of Reporting Services.
HTH
Charles Kangai, MCT, MCDBA
"Calvin KD" wrote:
> Hi everyone,
> I have a Web Form with data fields. What I want to do is to save these
> fields into an XML file (dynamically), then hook this XML (as a data source)
> to a SQL Report so that I don't have to save these data into a SQL Server
> database. Is it possible? If it is then can someone show me how.
> Any suggestion is greatly appreciated.
> Many thanks in advance.|||Hi
I used the "Microsoft OLE DB Simple Provider"
1.) Create new Shared Data Source
General: "Provider=MSDAOSP.1;Data Source=MSXML2.DSOControl.2.6"
Credentials: "Use Windows Authentication (Integrated Security)"
2.) Create new Report using this Shared Data Source
Query: path to the xml-file "c:\temp\test.xml"
Kind Regards
Daniel
"Charles Kangai" <CharlesKangai@.discussions.microsoft.com> wrote in message
news:4A0288C1-9C16-4CDF-981A-88B89475616C@.microsoft.com...
> There is currently no support for accessing an XML data source. You either
> have to write your own custom XML data source extension or look on the
> market
> for an XML OLEDB provider. Or search MSDN for sample code that implements
> an
> XML data source extension. My current understanding is that it will be
> included with the SQL Server 2005 release of Reporting Services.
> HTH
> Charles Kangai, MCT, MCDBA
> "Calvin KD" wrote:
>> Hi everyone,
>> I have a Web Form with data fields. What I want to do is to save these
>> fields into an XML file (dynamically), then hook this XML (as a data
>> source)
>> to a SQL Report so that I don't have to save these data into a SQL Server
>> database. Is it possible? If it is then can someone show me how.
>> Any suggestion is greatly appreciated.
>> Many thanks in advance.