Easier way of building pivot tables in MS SQL Server

Dear All

I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.

Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?

I would be very grateful for any help with this.

wujtehacjuszWhat version of SQL Server?

SQL Server 2005 has the PIVOT command.

On Jul 10, 5:19 am, wujtehacjusz <wujtehacj...@.gmail.comwrote:

Quote:

Originally Posted by

Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz

|||For SQL 2000, check out http://www.rac4sql.net/
--
Hope this helps.

Dan Guzman
SQL Server MVP

"wujtehacjusz" <wujtehacjusz@.gmail.comwrote in message
news:1184059198.208520.109590@.q75g2000hsh.googlegr oups.com...

Quote:

Originally Posted by

Dear All
>
I am very new to MS SQL Server and I am wondering is there some tool
which would allow me to build pivot tables in SQL more easily. At the
moment writing a query can be quite challenging and difficult.
>
Is there any software which allows you to do it more intuitively and
gives you some visual feedback about query you are building?
>
I would be very grateful for any help with this.
>
wujtehacjusz
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = "1233321"; #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...
Hi Ilya
You are not allowed to do this! If you can use temporary tables instead then
that would be a better solution. Failing that, read the following article
carefully http://www.sommarskog.se/dynamic_sql.html
John
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = "1233321"; #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>

dynamical tablenames

Hi all!
I am about to create a procedure which will create tables with
dynamically specified tablenames.
Something like this
DECLARE tname CHAR(30);
SET tname = GETDATE(); #or any string function
CREATE TABLE tname
(
#columns cpecification
);
But it doesn't works...Try this ...
DECLARE @.tname VARCHAR(30), @.sql VARCHAR(2000)
SET @.tname = CONVERT(CHAR(8),GETDATE(),112)-- remove the spaces
SELECT @.sql = 'CREATE TABLE dbo.##' + @.tname + ' (mycol CHAR(10))' -- needs
to be global or permenant
SELECT @.sql
EXECUTE (@.sql)
"Ilya Dyoshin" wrote:

> Hi all!
> I am about to create a procedure which will create tables with
> dynamically specified tablenames.
> Something like this
> DECLARE tname CHAR(30);
> SET tname = GETDATE(); #or any string function
> CREATE TABLE tname
> (
> #columns cpecification
> );
>
> But it doesn't works...
>|||Why would you need dynamically named and/or dynamically created objects? How
are you planning on using them?
This is possible, but hardly recommended. There are better options and if
you post more information we can help you design a much more efficient
solution.
But if you insist on creating SQL objects dynamically you should read this
great article on dynamic SQL by Erland Sommarskog:
http://www.sommarskog.se/dynamic_sql.html
ML
http://milambda.blogspot.com/

Dynamic WHERE Clause to Stored Procedure

Hi all!
I need to create a stored procedure with a parameter and then send a WHERE clause to that parameter (fields in the clause may vary from time to time thats why I want to make it as dynamic as possible) and use it in the query like (or something like) this:

----------------
@.crit varchar(100)

SELECT fldID, fldName FROM tblUsers
WHERE @.crit
----------------

Of course this does not work, but I don't know how it should be done, could someone please point me in the right direction on how to do this kind of queries.

cheers!
pelleU just pass a parameter @.crit into ur stored procedure and do the following with it inside:

EXEC('SELECT fldID, fldName FROM tblUsers WHERE' + @.crit );

This should help u... I hope.
Alex.|||Another example:

CREATE PROCEDURE [dbo].[Alter_Email_Users_Table]
(@.Column as nvarchar(50),
@.FieldType as nvarchar(50),
@.TableName as nvarchar(50),
@.Null as nvarchar(20),
@.Default as nvarchar(4000)
)
AS
Declare @.SQL nVarchar(4000)
Select @.SQL = 'ALTER TABLE ' + @.TableName + ' ADD ' + @.Column + ' ' + @.FieldType + ' ' + @.NULL + ' '
IF @.Default IS NOT NULL
SET @.SQL = @.SQL + ' Default ' + "'" + @.Default + "'"
exec (@.SQL)
GO

exec is the key part in both examples.

dynamic tablename issue

Dear all:
I have a storedprocedure for db loader form some temp tables.
my storedprocedure as the following:

declare c cursor
for
select * from @.tablename
open c
fetch c into ...

but I get an error for declare cursor for dynamic tablename,

did sql server has some BIF to evaluate the variable for table name?

thanks for your kindly assistance.

regards,

Stanley Huanguse the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL|||Originally posted by yoavmaimon
use the dynamic sql statments EXEC(@.sql_statement) or sp_executesql see the Holy BOL
do you mean I can write the stored procedure as:

create proc sp_dynamicTableName
(
declare @.tablename varchar(20)
)
as
declare c cursor
for
exec('select * from ' & @.tablename)
open c
fetch c into ...

regards for your reply

thanks

Stanley Huang|||what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?|||Originally posted by yoavmaimon
what u wrote wont work,
1. u must build the sql statement out of th exec
2. all the sql statements that uses the @.tablename variable must be dynamic.
3. why cursors?

Dear Sir:
Why cursor?
Because I have to parse the records and and by the columns to do some extra action.
After actions, and I will use Waitfor the pause the cursor engine to limit the resource of this stored procedure.
Do you know how to set the priority of specified account in sql server,
then I can avoid to use cursor.

regards,

Stanley Huang

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
--
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
---
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
----
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
---
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
ReaTry using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> ---
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> ---
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>

dynamic sql statement error

Hi all
I would greatly appriciate your help in resolving the following error:
In t-sql procedure I am building a simple dynamic sql statement using
parametes.
Here is the code:
===========
step 0 - declare local variables:
declare @.localParam as datetime
declare @.strSelect as varchar(300)
declare @.colName as varchar(30)
step 1 - build the select statement with parameter:
set @.colName = 'OPENING_DATE'
set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
step 2 - set return value to a local parameter:
set @.strSelect = 'set @.localParam = ('
+ @.strSelect +
')'
step 3 - execute the statement and generate error!:
exec (@.strSelect)
Yelds the following error: 'Must declare the variable @.localParam ...
Why is @.localParam not recognized?
Changing it type to varchar did not make a diference nor using
sp_executesql.
If you have some other way to build this kind of dynamic sql statements
(which return some value to a
local param ) I'd be more than thank full.
Thanks for your help
Rea
Try using sp_executesql:
DECLARE @.localParam as datetime
DECLARE @.strSelect as nvarchar(300)
DECLARE @.colName as sysname
set @.colName = 'OPENING_DATE'
SET @.strSelect = N'SELECT @.localParam =
MIN(' + @.colName + ') FROM dbo.DW_PURCHASE_U'
EXEC sp_executesql @.strSelect,
N'@.localParam datetime OUT',
@.localParam OUT
SELECT @.localParam
Hope this helps.
Dan Guzman
SQL Server MVP
"Rea Peleg" <rea_p@.afek.co.il> wrote in message
news:uxLedR0ZEHA.3112@.tk2msftngp13.phx.gbl...
> Hi all
> I would greatly appriciate your help in resolving the following error:
> In t-sql procedure I am building a simple dynamic sql statement using
> parametes.
> Here is the code:
> ===========
> step 0 - declare local variables:
> --
> declare @.localParam as datetime
> declare @.strSelect as varchar(300)
> declare @.colName as varchar(30)
> step 1 - build the select statement with parameter:
> set @.colName = 'OPENING_DATE'
> set @.strSelect = 'select min('+ @.colName+ ') from dbo.DW_PURCHASE_U'
> step 2 - set return value to a local parameter:
> ----
> set @.strSelect = 'set @.localParam = ('
> + @.strSelect +
> ')'
> step 3 - execute the statement and generate error!:
> exec (@.strSelect)
> Yelds the following error: 'Must declare the variable @.localParam ...
> Why is @.localParam not recognized?
> Changing it type to varchar did not make a diference nor using
> sp_executesql.
> If you have some other way to build this kind of dynamic sql statements
> (which return some value to a
> local param ) I'd be more than thank full.
> Thanks for your help
> Rea
>
>
>