Dynamic crosstab query in MS SQL Server 2000

Hello all!
I have a problem with creating crosstab query in MS SQL Server 2000. I
spent 8 hours on searching internet to achieve my succes but without
result. I would like to transform such data:
MRPController WK Value
C01 200505 1
C01 200505 1
C02 200505 2
C03 200506 4
C03 200506 7
C04 200505 1
C04 200507 5
into:
MRPController 200505 200506 200507
C01 2
C02 2
C03 4
C04 1 5
The data are updated once a w, that`s why I need a dynamic crosstab
query which let me receive such query in MS SQL Server 2000. I found out
that it is no so easy to create such cross tab query in MS SQL Server
2000, but I am wondering why it is so easy even in MS Access 1997 and
Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
have search newsgroups, but I didn`t find anything whcih could help me.
I found some SQL procedures but they didn`t work. I heard that in MS SQL
Server 2005 there is a special function who let do it, but I have MS SQL
Server 2000 and I need to do this in this version on SQL Server. Is it
possible to do it? Is it some correct method to do it. Please be so kind
and help, but I already don`t know what to do and it is very wanted
query in my company. I didn`t think that I stuck on such query.
Thank you in advance for your help
I really apprieciate it
Marcin from Poland
*** Sent via Developersdex http://www.examnotes.net ***Take a look at this link
[url]http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21205811.html[/ur
l]
I use the transform proc just as you described with great results. You
will have to make a few small modifications so the date is labeled to
your likeing. I have mine labled (Month Year i.e. March 2005).
GL|||Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Marcin Zmyslowski" wrote:

>
> Hello all!
> I have a problem with creating crosstab query in MS SQL Server 2000. I
> spent 8 hours on searching internet to achieve my succes but without
> result. I would like to transform such data:
> MRPController WK Value
> C01 200505 1
> C01 200505 1
> C02 200505 2
> C03 200506 4
> C03 200506 7
> C04 200505 1
> C04 200507 5
> into:
> MRPController 200505 200506 200507
> C01 2
> C02 2
> C03 4
> C04 1 5
> The data are updated once a w, that`s why I need a dynamic crosstab
> query which let me receive such query in MS SQL Server 2000. I found out
> that it is no so easy to create such cross tab query in MS SQL Server
> 2000, but I am wondering why it is so easy even in MS Access 1997 and
> Excel 1997, and it is so tough case in MS SQL Server released in 2000. I
> have search newsgroups, but I didn`t find anything whcih could help me.
> I found some SQL procedures but they didn`t work. I heard that in MS SQL
> Server 2005 there is a special function who let do it, but I have MS SQL
> Server 2000 and I need to do this in this version on SQL Server. Is it
> possible to do it? Is it some correct method to do it. Please be so kind
> and help, but I already don`t know what to do and it is very wanted
> query in my company. I didn`t think that I stuck on such query.
> Thank you in advance for your help
> I really apprieciate it
> Marcin from Poland
> *** Sent via Developersdex http://www.examnotes.net ***
>

Dyanamic stored procedures

Hi all,
I'm building a search function and I need do it using stored procedures. I'm searching a table on 'Name' 'email address' and 'key words'. (basically 3 columns). The user must be able to use it as
SELECT ......... WHERE (NAME LIKE @.name) AND/OR (Email LIKE @.email) AND/OR ..........
Can some one tell me how to handle this in a stored procedure. By the way is this approch correct. Please tell me if there is a better method to tackle this situation.
Thanks,
-VJ

I always generate a dynamic SQL statement based on what the user has entered and then execute it or use sp_executesql to run it. Some will say that dynamic SQL is bad because the execution plan doesn't get cached like normal procs, but you can get better performance with dynamic SQL sometimes because since the query criteria can change dramtically, the cached execution plan may be a bad one. Always bets to time and view the execution plan in QA, but my experience is that dynamic SQL within the stored proc works best when there are varying search parameters.

|||If it's just 3 columns you're validating, you should just use case statements instead of utilizing dynamic sql.
selelct * from table where -- normal stuff
Name Like (
CASE
WHEN @.name IS NOT NULL THEN @.name
ELSE Name
END
)

-----
A lot of times, I'll validate a string for nulls and empty strings, so I'll usually have this function in most of my databases.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION [dbo].[IsEmptyString]
(
@.String VarChar(2000)
)
RETURNS BIT AS
BEGIN
DECLARE @.ReturnVar BIT
IF (@.String IS NOT NULL AND @.String <> '') -- you can trim if you are avoiding whitespaces.
SET @.ReturnVar = 0
ELSE
SET @.ReturnVar = 1

RETURN @.ReturnVar
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Test it both ways. I've done it with case statements and gotten terrible performance compared with dynamic SQL. It all depends on the situation. Having 3 or 6 or 20 columns really isn't the issue; it's how the procedure performs when varying numbers or combinations of the criteria are entered. I've seen demos where the dynamic SQL blew away using case or isnull.|||You're right that it's completely situational in terms of performance,and many will do their best to keep the actual logic in code as much aspossible. From personal experience, iterations through decentsized databases (2 million records in each of the major tables), a casestatement will perform fairly well as compared to utilizing dynamicsql. It helps further when you require calculations to be madeper record to assist in it's selection criteria (Distance for example),and the one of the factors is based on the parameters. In caseslike this, a case statement tends to outweigh a dump of string to beexecuted in a stored procedure.
Create Procedure KraGiE.ExecuteString
@.SQL varchar (2000)
AS
EXECUTE( @.SQL )
GO
For some reason, that makes me cringe, and I'm sure many database administrators will plainly say "no."