can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

Can we directly execute a text file that contains all sql commands

Hi, friends,
We have our stored procedures (sp) saved in a .sql file, one for each. When
we want to create an sp, we open the corresponding .sql file using notepad o
r
sql query analyzer, and then execute it.
Do we have a way that we just need to pass those .sql files' name, including
full path, and run all those sp creation sql code in a batch, without
openning each .sql file one by one?
Thanks a lot for your help.Yes, use osql.
exec master..xp_cmdshell 'osql ...'
You can look up the syntax for osql in Books Online...
A
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.|||You can use the command line tools for that. Look up 'osql' in BOL.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:917F3B91-8A8E-454D-8782-A76CF5D59B4A@.microsoft.com...
> Hi, friends,
> We have our stored procedures (sp) saved in a .sql file, one for each.
> When
> we want to create an sp, we open the corresponding .sql file using notepad
> or
> sql query analyzer, and then execute it.
> Do we have a way that we just need to pass those .sql files' name,
> including
> full path, and run all those sp creation sql code in a batch, without
> openning each .sql file one by one?
> Thanks a lot for your help.

Can we create two servicess in a same database?

Hi,

I tried creating two services in a same queue but i couldnt see them working properly. Now i got a primary doubt that can i create two services which communicate with each other in a same database? Please help me....

here is the script which i used to create the services

/************************************Scipt *********************************/

/********************** Sender Queue and Service ***********************/

CREATE QUEUE SenderQueue WITH Status = ON;

CREATE CONTRACT SenderContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE SERVICE SenderService

ON QUEUE SenderQueue(SenderContract);

/********************** Receiver Service ***********************/

ALTER PROCEDURE pArchiveMessageHandler

AS

BEGIN

DECLARE @.V_Message AS NVARCHAR(1000);

DECLARE @.V_Handle AS UNIQUEIDENTIFIER;

INSERT INTO TestServiceBroker VALUES('Starting Message', @.V_Handle);

RECEIVE TOP(1) @.V_Message = message_body,

@.V_Handle = conversation_handle

FROM ArchivingMessageQueue

INSERT INTO TestServiceBroker VALUES(@.V_Message, @.V_Handle)

END CONVERSATION @.V_Handle

WITH cleanup

END

CREATE QUEUE ReceiverQueue

WITH ACTIVATION(

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'dbo'

);

CREATE CONTRACT RecieverContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE SERVICE ReceiverService ON QUEUE ReceiverQueue

( RecieverContract )

/********************** Sample script ***********************/

GO

DECLARE @.V_Handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.V_Handle

FROM SERVICE SenderService

TO SERVICE 'ReceiverService'

ON CONTRACT SenderContract;

SEND ON CONVERSATION @.V_Handle

MESSAGE TYPE ArchivingMessage('<XmlType/>');

select * from TestServiceBroker(nolock)

Hi:

Why do you need to create two services in the same database...in the first place. I know it works between two different databases but have not tired with same database.

Pramod

|||

Although it is possible to associate two services with the same queue, this is a very atypical usage. Messages sent to the target service as well as messages received from the target service will be delivered to the same queue.

In your script above, you are not doing this, so I'm not sure if you meant to say "same queue" or "same database".

Having both the initiator service and target service is in the same database is certainly a valid and useful configuration. You would use this typically for doing asynchronous database work.

In your script, the stored proc is RECEIVing messages from ArchivingMessageQueue (which doesn't exist) instead of ReceiverQueue. Also why are you ending the conversation with cleanup. Cleanup should not be used during normal working of an app. It is used as an admin command when things go wrong.

|||

You can have as up to ~65000 services in a database. Communication between services in the same database is identical with communication between services in different databases or different instances.

Can you specify what you mean by 'but i couldnt see them working properly' ?

A small correction to the code posted by you:

CREATE QUEUE ReceiverQueue

WITH ACTIVATION(

STATUS = ON,

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 5,

EXECUTE AS 'dbo'

);

|||

I modified the activation stored proc as well as the receiver queue based on your feedback. But still i could see the service running. When i say the services are not running means.. my query "select * from TestServiceBroker(nolock)" is not returning any rows.

I Would like to tell you people about what exactly is my requirement. We have a master table X and around 20 transaction tables say A, B, C, D, E,..etc. The transaction tables uses data available in the master table. When ever a row in the master tables is disabled(we have a flag column which holds state) we would like to disable all the trasaction tables(A, B, C, D....) data which is referring to the master X row. The process of disableing the transaction table rows can happen in an asynchronous way. To achieve this we planned to implement Fire and Forget style of service brokering. When ever a master table row is disabled we need to queue a message(using a trigger on the master table) for disabling the transaction table rows. An activation procedure needs to do the job of reading the queued request and disableing transcation table rows.

Also, can you please give me links of e-books and white papers on Service Brokers?

Regards,

Gopi

|||

Have you looked over my troubleshooting mini-guide at http://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx ? Please follow this guide to figure out where do your messages 'disappear'. If I'd have to guess in your case I'd say that the database is lacking the master key required for secure dialogs.

One thing to note is that you should not use the WITH CLEANUP clause in the END DIALOG statement. The CLEANUP clause removes the conversation endpoint (target) without sending any notification to the other conversation endpoint (initiator). See also this post here http://blogs.msdn.com/remusrusanu/archive/2006/01/27/518455.aspx

The design you're trying to achieve shouldn't be any problem if the asynchronous processing is required or desired. One thing you'll have to consider is the order of execution of these enable/disable operations. Service Broker guarantees the order of delivery only within a conversation.

HTH,
~ Remus

|||

Thanks Remus.

I'll debug the problem with your suggestions.

Regards,

Gopi

|||

Gopinath

I took the liberty of rewriting your sample script. I changed several things and also added numerous explanatory comments. You can compare your original to this to see what was wrong. Just copy/paste this into mgmt. studio and run the whole thing at once.

-Gerald Hinson

use master

go

drop database gopinath

go

create database gopinath

go

use gopinath

go

/********************** Sender Queue and Service (nothing else needed )***************/

CREATE QUEUE SenderQueue;

CREATE SERVICE SenderService

ON QUEUE SenderQueue;

go

/********************** RECEIVE MessageType, Contract, Queue and Service ****************/

CREATE MESSAGE TYPE ArchivingMessage;

CREATE CONTRACT ReceiverContract

(

ArchivingMessage SENT BY INITIATOR

);

CREATE QUEUE ReceiverQueue;

CREATE SERVICE ReceiverService ON QUEUE ReceiverQueue

(ReceiverContract)

go

/********************** Receiver Service ***********************/

create table TestServiceBroker (message_body nvarchar(max),

conversation_handle uniqueidentifier);

go

CREATE PROCEDURE pArchiveMessageHandler

AS

BEGIN

DECLARE @.V_Message AS NVARCHAR(1000);

DECLARE @.V_MessageTypeName AS NVARCHAR(256);

DECLARE @.V_Handle AS UNIQUEIDENTIFIER;

WHILE (1 = 1)

BEGIN

BEGIN TRANSACTION

WAITFOR (

RECEIVE TOP(1)

@.V_MessageTypeName = message_type_name,

@.V_Message = message_body,

@.V_Handle = conversation_handle

FROM ReceiverQueue), TIMEOUT 2000; -- wait 2 seconds before giving up

/* you shouldn't be inserting here unless you actually got a message back, so I added

the "if" below */

IF (@.@.rowcount > 0)

BEGIN

/* if you get and END DIALOG message, then you should END CONVERSATION as well */

IF (@.V_MessageTypeName = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')

BEGIN

END CONVERSATION @.V_Handle;

-- WITH cleanup -- (you shouldn't have this in your app....see explanations on the forum)

END

ELSE

BEGIN

INSERT INTO TestServiceBroker VALUES(@.V_Message, @.V_Handle);

END

END -- end if

ELSE

BEGIN

break; -- timed out waiting for messages, break from loop

END

COMMIT TRANSACTION;

END -- end loop

COMMIT TRANSACTION;

END

go

/********************** Sample script to send a message***********************/

BEGIN TRANSACTION;

DECLARE @.V_Handle UNIQUEIDENTIFIER

BEGIN DIALOG CONVERSATION @.V_Handle

FROM SERVICE SenderService

TO SERVICE 'ReceiverService'

ON CONTRACT ReceiverContract

WITH ENCRYPTION=OFF;

SEND ON CONVERSATION @.V_Handle

MESSAGE TYPE ArchivingMessage(N'<XmlType/>');

COMMIT TRANSACTION;

BEGIN TRANSACTION;

-- This will prevent you from seeing any error messages/replies.It would

-- be better to wait for the END DIALOG message from 'SenderService'

-- before doing this... Then you will know that your message actually got

-- processed....

END CONVERSATION @.V_Handle;

COMMIT TRANSACTION;

go

/********************** Sample script ***********************/

select * from ReceiverQueue;-- this will show that the messages are in the target queue

/**********************Turn on Activation on the receiver queue **********************/

ALTER QUEUE ReceiverQueue

WITH ACTIVATION(

STATUS=ON,

PROCEDURE_NAME = pArchiveMessageHandler,

MAX_QUEUE_READERS = 1,

EXECUTE AS 'dbo');

go

WAITFOR DELAY '00:00:02'; -- wait a second and then look in the activated task view to see your proc running

select * from sys.dm_broker_activated_tasks;

WAITFOR DELAY '00:00:02'; -- wait another couple of seconds and the proc should have finished running by now

go

select * from sys.dm_broker_activated_tasks;

/* look at the queue and your table again, not that the proc is done */

select * from ReceiverQueue;-- messages are gone

select * from TestServiceBroker;-- rows are now in the table

/* if the message could not be delivered for some reason, they would be in

sys.transmission_queue along with an explanation why they could not be delivered */

--select * from sys.transmission_queue

|||

Hi Gerald,

Thank you very much for you help. It's working.

Can we create shared report parametres?

Hi, all experts here,

Is it possible to create shared report parametres passing among different reports on SQL Server 2005 Reporting Services? If it is possible, and how?

Thanks a lot in advance for your advices. And I am looking forward to hearing from you.

With best regards,

Yours sincerely,

No, it's not possible to create a shared parameter, though you can pass the parameter values from one report to another either on user action or to subreports.

Shyam

Can we create Jobs in Sql Server?

Can we create Jobs in Sql Server?

Why not? Look at SQLServer Agent.

|||

Yes, you can have.

For example: a job that responsible to run a DTS (or SSIS) package on a specifc time in a day to do a task (e.g. execute stored procedure or take backup).

Note: Any scheduled thing is SQL Server requre the SqlAgent service to be started and running.

Good luck.

|||

You can create jobs in sqlserver in sqlserver agent in sqlserver 2005.

Can we create free-form report in report builder?

I only can create reports based on three predefined templates. It is very
restricted.Jun Yuan,
Don't choose the Wizard or you will be forced to use the templates.
Right click on your project. Choose Add new item... then choose Report.
You should be given a blank report and asked to specify a data source.
"Jun Yuan" wrote:
> I only can create reports based on three predefined templates. It is very
> restricted.sql

Can we create distinct values for document map on a particular level?

Hi, all experts here,

Thank you very much for your kind attention.

I am having a question about document map on SQL Server 2005 Reporting Services. I found one problem with the values retrived for the document level. That is, the values are not distinct, they are duplicate for the document level. Is it possible for us to get the distinct values for a particular document level? Hope my question is clear for your help.

Thank you very much in advance for your help and advices. I am looking forward to hearing from you.

With best regards,

Yours sincerely,

Group your data in either the table or list (whatever you are using) and use document map for the group.

Shyam

|||

Hi, Shyam,

Thanks a lot for your kind advices.

Yes, jsut found out that cause I got more than one groups in my reports. And the document map level is not the first group there, so once I run the report, the document map on that level got duplicate values. Now it is clear.

Thank you very much.

With best regards,

Yours sincerely,

Can we create composite logical key in data source view?

Hi, all,

Just found that I need to create a composite logical key consisting of a few columns in data source view in order to uniquely identify each row of record for the table in data source view, but then found that I am not able to create this kind of logical key? Is there any ways for us to create such a compoiste logical key in the data source view?

Thanks in advance and I am very much looking forward to hearing from you for your kind and helpful advices.

With best regards,

Yours sincerely,

You can create composite logical keys in the DSV, just multi-select columns, right click, and mark as logical key. However, if an existing physical key (i.e. a key reported by the relational source), then the DSV will not allow you to create any logical key.|||

Hi,

Thanks a lot.

WIth best regards,

Yours sincerely,

Can we add templates to SSRS?

Hi. Is it possible to add a custom template to SSRS for new reports? We'd like to create a template that has our logo and colors. Can you point me to a tutorial on this? Your help is much appreciated.

Yes, it is possible. For more details, check out this blog entry:

http://blogs.msdn.com/bimusings/archive/2005/12/06/500462.aspx

can we add our own te,plates in crystal reports 9

Presently i am working generation of reports from
Crystal Reports 9 by connecting this with PeopleSoft.

1)Can we create our own templates in Crystal Reports 9
? if it can be, how we can do that?
2)how we can make that template as a default one?

sir plz help me in resolving this problem b'coz my
task should be started with this.

plz reply me back what ever maybe the answer

thank uDo you mean user defined template at run time?|||Do you mean user defined template at run time?

yes userdefined templates like our company logo and backgrounds at design time or runtime|||I think you can use Logoes in Crystal Report. Use Insert image option|||I think you can use Logoes in Crystal Report. Use Insert image option

thanku

but what abt backgrounds, i have my company logo and background with company name on the whole page in shaded manner i have to create the report on that.

number3|||Yes we can add our own templates.

normal reprot can be used a template.

if u get an error it can be of the problem in CRystal Reports 9, for this u should get the service pack from the following address
this service pack will help to resolve the error for crw32.exe error also.
downlaod the service pack which is only for crystal reports 9.other service packs also there.but if ur need is only for crystal reports u can downlaod this.

http://support.businessobjects.com/downloads/updates/service_packs/default.asp#Crystal%20Reports%20Service%20Packs

bye
enjoy
manosql

Can we able to create a database user name as ‘sa’ in sql server?

Can we able to create a database user name as ‘sa’ in sql server 2k5?

sa is reserved for the sysadmin account.

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

No, database principals cannopt have the names of predefined server principals.

Jens K. Suessmeyer

http://www.sqlserver2005.de

sql

Can Users choose Rows & columns for a matrix?

Hi,
Is it possible to create a report which allows users to select the fields
for the matrix in a report. To explain in detail, can we allow the users to
select the X and Y axis for a matrix in a report?
For ex., there is a report containing a matrix which shows the total sales(
data cell) by month (X axis) and by Company( Y axis). Can we have some option
so that if the users select Year as the X axis and SalesPerson as the Y axis
then they can view the same report but by SalesPerson and Year instead od
Month and Company?
Any help is highly appreciated.
Thanks
--
pmudYes, this is possible by using dynamic field references for the grouping
expression and the matrix group header textbox expression. E.g.
=Fields(Parameters!RowGroup.Value).Value
Note: the actual field name in the expression above will be determined by
the parameter's value at runtime. You can use this for the row and the
column grouping of the matrix.
A RS 2005 sample report (which also includes InteractiveSort on the dynamic
matrix row groups) is attached. Note: you cannot load this sample in the RS
2000 report designer.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> Hi,
> Is it possible to create a report which allows users to select the fields
> for the matrix in a report. To explain in detail, can we allow the users
> to
> select the X and Y axis for a matrix in a report?
> For ex., there is a report containing a matrix which shows the total
> sales(
> data cell) by month (X axis) and by Company( Y axis). Can we have some
> option
> so that if the users select Year as the X axis and SalesPerson as the Y
> axis
> then they can view the same report but by SalesPerson and Year instead od
> Month and Company?
> Any help is highly appreciated.
> Thanks
> --
> pmud
=====================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="northwind">
<DataSourceReference>northwind</DataSourceReference>
<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="RowGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ProductName</Value>
</Values>
</DefaultValue>
<Prompt>RowGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ProductName</Value>
<Label>By Product Name</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
<ParameterValue>
<Value>CategoryID</Value>
<Label>By Category ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ColumnGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ReorderLevel</Value>
</Values>
</DefaultValue>
<Prompt>ColumnGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ReorderLevel</Value>
<Label>By Reorder Level</Label>
</ParameterValue>
<ParameterValue>
<Value>UnitsInStock</Value>
<Label>By Stock</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Textbox Name="textbox3">
<Left>0.125in</Left>
<Top>0.375in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
</Textbox>
<Textbox Name="textbox1">
<Left>0.125in</Left>
<Top>0.125in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
</Textbox>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.125in</Left>
<RowGroupings>
<RowGrouping>
<Width>2.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!RowGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>0.875in</Top>
<Width>3.125in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
</UserSort>
<Value>Sort rows</Value>
</Textbox>
</ReportItems>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products</CommandText>
<DataSourceName>northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>3.375in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||Hi Robert,
I dont have RS 2005. So the way you told ( by having
=Fields(Parameters!RowGroup.Value)) will work for RS 2000 too?
Thanks
--
pmud
"Robert Bruckner [MSFT]" wrote:
> Yes, this is possible by using dynamic field references for the grouping
> expression and the matrix group header textbox expression. E.g.
> =Fields(Parameters!RowGroup.Value).Value
> Note: the actual field name in the expression above will be determined by
> the parameter's value at runtime. You can use this for the row and the
> column grouping of the matrix.
> A RS 2005 sample report (which also includes InteractiveSort on the dynamic
> matrix row groups) is attached. Note: you cannot load this sample in the RS
> 2000 report designer.
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:B91AE70B-5CEF-402E-B9A5-30BD193CBCE0@.microsoft.com...
> > Hi,
> >
> > Is it possible to create a report which allows users to select the fields
> > for the matrix in a report. To explain in detail, can we allow the users
> > to
> > select the X and Y axis for a matrix in a report?
> >
> > For ex., there is a report containing a matrix which shows the total
> > sales(
> > data cell) by month (X axis) and by Company( Y axis). Can we have some
> > option
> > so that if the users select Year as the X axis and SalesPerson as the Y
> > axis
> > then they can view the same report but by SalesPerson and Year instead od
> > Month and Company?
> >
> > Any help is highly appreciated.
> >
> > Thanks
> > --
> > pmud
>
> =====================================================> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <DataSources>
> <DataSource Name="northwind">
> <DataSourceReference>northwind</DataSourceReference>
> <rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
> </DataSource>
> </DataSources>
> <BottomMargin>1in</BottomMargin>
> <RightMargin>1in</RightMargin>
> <ReportParameters>
> <ReportParameter Name="RowGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ProductName</Value>
> </Values>
> </DefaultValue>
> <Prompt>RowGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ProductName</Value>
> <Label>By Product Name</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>CategoryID</Value>
> <Label>By Category ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> <ReportParameter Name="ColumnGroup">
> <DataType>String</DataType>
> <DefaultValue>
> <Values>
> <Value>ReorderLevel</Value>
> </Values>
> </DefaultValue>
> <Prompt>ColumnGroup</Prompt>
> <ValidValues>
> <ParameterValues>
> <ParameterValue>
> <Value>ReorderLevel</Value>
> <Label>By Reorder Level</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>UnitsInStock</Value>
> <Label>By Stock</Label>
> </ParameterValue>
> <ParameterValue>
> <Value>SupplierID</Value>
> <Label>By Supplier ID</Label>
> </ParameterValue>
> </ParameterValues>
> </ValidValues>
> </ReportParameter>
> </ReportParameters>
> <rd:DrawGrid>true</rd:DrawGrid>
> <InteractiveWidth>8.5in</InteractiveWidth>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <Body>
> <ReportItems>
> <Textbox Name="textbox3">
> <Left>0.125in</Left>
> <Top>0.375in</Top>
> <ZIndex>2</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
> </Textbox>
> <Textbox Name="textbox1">
> <Left>0.125in</Left>
> <Top>0.125in</Top>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Width>3in</Width>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Height>0.25in</Height>
> <Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
> </Textbox>
> <Matrix Name="matrix1">
> <MatrixColumns>
> <MatrixColumn>
> <Width>1in</Width>
> </MatrixColumn>
> </MatrixColumns>
> <Left>0.125in</Left>
> <RowGroupings>
> <RowGrouping>
> <Width>2.125in</Width>
> <DynamicRows>
> <ReportItems>
> <Textbox Name="CategoryID">
> <rd:DefaultName>CategoryID</rd:DefaultName>
> <ZIndex>1</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!RowGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Grouping Name="matrix1_RowGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicRows>
> </RowGrouping>
> </RowGroupings>
> <ColumnGroupings>
> <ColumnGrouping>
> <DynamicColumns>
> <ReportItems>
> <Textbox Name="ReorderLevel">
> <rd:DefaultName>ReorderLevel</rd:DefaultName>
> <ZIndex>2</ZIndex>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
> </Textbox>
> </ReportItems>
> <Sorting>
> <SortBy>
> <SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
> <Direction>Ascending</Direction>
> </SortBy>
> </Sorting>
> <Grouping Name="matrix1_ColumnGroup">
> <GroupExpressions>
> <GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </DynamicColumns>
> <Height>0.25in</Height>
> </ColumnGrouping>
> </ColumnGroupings>
> <DataSetName>DataSet1</DataSetName>
> <Top>0.875in</Top>
> <Width>3.125in</Width>
> <Corner>
> <ReportItems>
> <Textbox Name="textbox4">
> <rd:DefaultName>textbox4</rd:DefaultName>
> <ZIndex>3</ZIndex>
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <UserSort>
> <SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
> <SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
> </UserSort>
> <Value>Sort rows</Value>
> </Textbox>
> </ReportItems>
> </Corner>
> <Height>0.5in</Height>
> <MatrixRows>
> <MatrixRow>
> <Height>0.25in</Height>
> <MatrixCells>
> <MatrixCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <rd:DefaultName>ProductID</rd:DefaultName>
> <Style>
> <TextAlign>Right</TextAlign>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingRight>2pt</PaddingRight>
> <PaddingTop>2pt</PaddingTop>
> </Style>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!ProductID.Value)</Value>
> </Textbox>
> </ReportItems>
> </MatrixCell>
> </MatrixCells>
> </MatrixRow>
> </MatrixRows>
> </Matrix>
> </ReportItems>
> <Height>2in</Height>
> </Body>
> <rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
> <LeftMargin>1in</LeftMargin>
> <DataSets>
> <DataSet Name="DataSet1">
> <Query>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> <CommandText>select * from products</CommandText>
> <DataSourceName>northwind</DataSourceName>
> </Query>
> <Fields>
> <Field Name="ProductID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>ProductID</DataField>
> </Field>
> <Field Name="ProductName">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>ProductName</DataField>
> </Field>
> <Field Name="SupplierID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>SupplierID</DataField>
> </Field>
> <Field Name="CategoryID">
> <rd:TypeName>System.Int32</rd:TypeName>
> <DataField>CategoryID</DataField>
> </Field>
> <Field Name="QuantityPerUnit">
> <rd:TypeName>System.String</rd:TypeName>
> <DataField>QuantityPerUnit</DataField>
> </Field>
> <Field Name="UnitPrice">
> <rd:TypeName>System.Decimal</rd:TypeName>
> <DataField>UnitPrice</DataField>
> </Field>
> <Field Name="UnitsInStock">
> <rd:TypeName>System.Int16</rd:TypeName>
> <DataField>UnitsInStock</DataField>
> </Field>
> <Field Name="UnitsOnOrder">
> <rd:TypeName>System.Int16</rd:TypeName>|||Robert -
I have been trying something very similar to this except that it takes
it one step further. My matrix has multiple RowGroups and multiple
ColumnGroups. I'd like the user to be able to turn some off, but I
cannot keep the RowGroup fields from always appearing in the matrix.
I use expressions to turn the RowGrouping on/off. When the RowGroup is
not to appear, the expression evaluates to "".
I also use expressions to set the RowGroup visibility to false.
Is there any way to do this? Maybe I am doing something wrong?
Thanks.

can use Where in Dts query?

i create a model mining to know percent of product, which is like more by customer is have percent biger than other products that is don't like by customer.

i use DTS to get result to sql server.

select flattened

predict([modelName].[tablePredict],Include_statistic)

from

[modelName]

prediction join

.....

i have result with a table A in sql server:

productid $support ... $adjustedprobability

1 0.0 ...

1 0.0 ...

1 63.3 .....

how can i write in Dts so that table A not have record with value $support=0.0

- second: percent people like that product is knowned by $adjustedprobability or $support and value small or big

1:

SELECT FLATTENED
(SELECT * FROM Predict([modelName.tablePredict,INCLUDE_STATISTICS) WHERE $support > 0) as p
FROM [modelName]
PREDICTION JOIN ....

2: If you want descending, you can just use the column in the query - e.g.
SELECT FLATTENED Predict(model.table, 5, $AdjustedProbability) FROM ...

If you want bottom you need to use BottomCount(Predict(model.table),include_statistics), $support,5)

You should check my syntax - I just typed this from memory

Thx

|||

thank you very much.

you right when tell me check my syntax, there are some mistake.

thank you again

|||

hi, where can i put where in this query. it gives error when i try to use where clause.

i want 5 products but also their $AdjustedProbability should be > 0.36 ex.

SELECT FLATTENED

TopCount(Predict([Customer Products],

INCLUDE_STATISTICS) ,

$AdjustedProbability ,

5)

FROM [Basket Analysis]

NATURAL PREDICTION JOIN

( SELECT (

SELECT 'Product A' AS [Product]

)

AS [Customer Products] ) AS t

can use Where in Dts query?

i create a model mining to know percent of product, which is like more by customer is have percent biger than other products that is don't like by customer.

i use DTS to get result to sql server.

select flattened

predict([modelName].[tablePredict],Include_statistic)

from

[modelName]

prediction join

.....

i have result with a table A in sql server:

productid $support ... $adjustedprobability

1 0.0 ...

1 0.0 ...

1 63.3 .....

how can i write in Dts so that table A not have record with value $support=0.0

- second: percent people like that product is knowned by $adjustedprobability or $support and value small or big

1:

SELECT FLATTENED
(SELECT * FROM Predict([modelName.tablePredict,INCLUDE_STATISTICS) WHERE $support > 0) as p
FROM [modelName]
PREDICTION JOIN ....

2: If you want descending, you can just use the column in the query - e.g.
SELECT FLATTENED Predict(model.table, 5, $AdjustedProbability) FROM ...

If you want bottom you need to use BottomCount(Predict(model.table),include_statistics), $support,5)

You should check my syntax - I just typed this from memory

Thx

|||

thank you very much.

you right when tell me check my syntax, there are some mistake.

thank you again

|||

hi, where can i put where in this query. it gives error when i try to use where clause.

i want 5 products but also their $AdjustedProbability should be > 0.36 ex.

SELECTFLATTENED

TopCount(Predict([Customer Products],

INCLUDE_STATISTICS) ,

$AdjustedProbability ,

5)

FROM [Basket Analysis]

NATURALPREDICTIONJOIN

( SELECT (

SELECT 'Product A' AS [Product]

)

AS [Customer Products] ) AS t

can unique index be created if the include col makes the index unique?

create index idx_tab1_col1col2

on dbo.tab1([col1])

include([col2])

on tab1_index

My question is this. I require this index to be created. col1 doesn't have a lot of unique values but col2 is our pk and adding col2 to the index makes it unique. Can I make this a unique index then? Also, can someone explaing me a bit as to why it's possible or y not? Thank you very much for your help.

I dare say that you could have created the index as unique in less time than it took to navigate to here and write the question. But you didn't so let's try it together.

Create Table dbo.tab1 (Col1 varchar(5) null, Col2 int not null primary key)

Insert Into dbo.tab1
Select 'aaaaa', 1
Union
Select 'bbbbb', 2
Union
Select 'ccccc', 3
Union
Select 'ccccc', 4
Union
Select 'ddddd', 5

create unique index idx_tab1_col1col2
on dbo.tab1([col1])
include([col2])

Msg 1505, Level 16, State 1, Line 2
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.tab1' and index name 'idx_tab1_col1col2'. The duplicate key value is (ccccc).
The statement has been terminated.

So, no, you can't. Uniqueness must exist within the indexed fields without regard to any included columns.

|||Include column can not make index unique. Because include column is not part of index key.|||Hey Robert. Yes, you are correct but at that time, I was in middle of implememnting a checkdb process for my company, which was never in place. Well, thank you very much for your time and effort and the answer.

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in prod
uction code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-cre
ating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satish
It is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegr oups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>
|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish
sql

Can u swap the columns after creation of table

hi,
my doubt is --can u swap the columns of the table
ie..,
create table dd(f int ,e int ,g int)
table structure ;
f e g
1 2 4
now i want the columns to be swaped as : e f g
i know i can get the answer by using a select statement
select e,f,g from dd
but i want to get the table struncture as as e f g
when i write -- select * from dd
i have to get the structure as said above
then i have to insert the values can any one help me
satishIt is recommended to never do SELECT * or INSERT without column list in production code, which makes
that code independent of the order of columns in a table.
Anyhow, no, there is no way to change column order in a table without re-creating the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"satish" <satishkumar.gourabathina@.gmail.com> wrote in message
news:1141642885.735134.249180@.i39g2000cwa.googlegroups.com...
> hi,
> my doubt is --can u swap the columns of the table
> ie..,
> create table dd(f int ,e int ,g int)
> table structure ;
> f e g
> 1 2 4
> now i want the columns to be swaped as : e f g
> i know i can get the answer by using a select statement
> select e,f,g from dd
> but i want to get the table struncture as as e f g
> when i write -- select * from dd
> i have to get the structure as said above
> then i have to insert the values can any one help me
> satish
>|||thanking you for clarifyig my doubt -- i think at the design state
of the data base or table structure -more information to be collected
thanks
satish

Can TSQL query create new output column ?

How can i write a query to split a database column and shows 2 new columns. In my database column

I have 2 mixing items and need to split out to 2 columns. Normally I have to write a query and change parameter

and run another query.

For example a database column with average number and range number.

Thanks

Daniel

Can you post some DDL, sample data and expected results?

AMB

|||

Hai,

Can you try the below query, and let me know that, it relates to your requirement or not:

DECLARE @.Columns varchar(1000)

SET @.Columns = ''

-- Create a temporary table.

CREATE TABLE #TempTable(Items varchar(50))

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('A')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('B')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('C')

INSERT INTO #TempTable(Items) VALUES('D')

INSERT INTO #TempTable(Items) VALUES('D')

-- Before

SELECT * FROM #TempTable

-- Make a column list

SELECT

@.Columns = @.Columns + '[' + Items + '], '

FROM #TempTable

GROUP BY Items

-- Check the column values exits or not.

IF ( @.Columns IS NOT NULL ) AND ( @.Columns <> '' )

BEGIN

DECLARE @.Query nvarchar(1000)

SELECT @.Columns = SUBSTRING(@.Columns,1, LEN(@.Columns)-1)

SELECT @.Query = '

SELECT

*

FROM

(

SELECT

Items

FROM #TempTable

) AS Dummy

PIVOT

(

MAX(Items)

FOR Items IN (' + @.Columns + ')

)AS PvtTable'

EXEC(@.Query)

END

-- Drop the temporary table.

DROP TABLE #TempTable

Please clarify If I did any wrong.

Regards,

Kiran.Y

|||

Perhaps something like:

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
MyGroup int,
MyValue decimal(10,2)
)

INSERT INTO @.MyTable VALUES ( 1, 25 )
INSERT INTO @.MyTable VALUES ( 2, 5 )
INSERT INTO @.MyTable VALUES ( 1, 10 )
INSERT INTO @.MyTable VALUES ( 1, 15 )
INSERT INTO @.MyTable VALUES ( 1, 4 )
INSERT INTO @.MyTable VALUES ( 2, 6 )
INSERT INTO @.MyTable VALUES ( 2, 11 )
INSERT INTO @.MyTable VALUES ( 2, 0 )
INSERT INTO @.MyTable VALUES ( 1, 12 )

SELECT
Average = cast( avg( MyValue ) AS decimal(10,2)),
Range = ( cast( min( MyValue ) AS varchar(10)) + '-' +
cast( max( MyValue ) AS varchar(10)))
FROM @.MyTable
GROUP BY MyGroup

Average Range
13.20 4.00-25.00
5.50 0.00-11.00

|||

Hi Kiran

Thanks for answering my email. To clarify this below are my tables and columns and my query

Table: Item Stat_label Stat_value

column: Pack ID Stat_label_ID Stat_value_ID

Pack_Num Label ( has 2 rows Value

Ave and Range)

My query to list Pack_Num, Ave and it's value

SELECT Item.Pack_Num, Stat_label.Label, Stat_value.Value

FROM Item, Stat_label, Stat_value

WHERE Item.packID=Stat_label.Stat_label_ID AND

Stat_label.Stat_lavel_ID=Stat_value.Stat_value_ID

AND Stat_label.Label= Ave

My question: I want a query to list Pack_Num, Ave, Range and value

How can I do it?

That's mean this query need to split the Stat_label and list another

column name"Range".

Thanks
Daniel

|||

If you are using SQL 2005, look into the PIVOT function.

If you are using SQL 2000, explore using CASE.

Maybe these articles will help:

Pivot Tables -A simple way to perform crosstab operations
http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1131829,00.html

Pivot Tables - How to rotate a table in SQL Server
http://support.microsoft.com/default.aspx?scid=kb;en-us;175574

Pivot Tables -Dynamic Cross-Tabs
http://www.sqlteam.com/item.asp?ItemID=2955

Pivot Tables - Crosstab Pivot-table Workbench
http://www.simple-talk.com/sql/t-sql-programming/crosstab-pivot-table-workbench/

|||

Thanks all

I can not use "insert" because my account for this is read only and I avoid to list everything in a column and and use Excel pivot to summary.

Daniel

|||

Daniel,

If you would carefully examine the code provided, you will see that the INSERT statements are only building a sample table so that we could demonstrate a query suggestion.

You didn't bother to provide the table DDL, or sample data, so we have to waste our time creating sample data for you. and apparently, you can't read and understand example code.

|||

This may be closer to what you are hoping to find:

SELECT

i.Pack_Num,

sl.Stat_Label,
Average = cast( avg( sv.Stat_Value ) AS decimal(10,2)),
Range = ( cast( min( sv.Stat_Value ) AS varchar(10)) + '-' +
cast( max( sv.Stat_Value ) AS varchar(10)))
FROM Item i

JOIN Stat_Label sl

ON i.Pack_ID = sl.Stat_Label_ID

JOIN Stat_Value sv

ON sl.Stat_Label_ID = sv.Stat_Value_ID

WHERE sl.Label = 'Ave'
GROUP BY

i.Pack_Num,

sl.Stat_Label

|||

Thanks Anrnie but It is not working

Error at Average= cast......

Error at Range= (cast......

My Average and Range are decimal, no need cast

Do I have to declare a temp table?

Daniel

|||

Actually, it appears that the Stat_Value is most most likely a varchar().

Before we can help you any further, please post the table DDL and some sample data in the form of INSERT statements. Please refer to this link for help in preparing your material.

|||

Can SQL query create a new column or not?. DO NOT want to make a temp table.

Thanks

Daniel

|||

Can TSQL create a new column at the output?

If not I need 2 select statement but how to joint them? Can not use EXCEPT in TSQL? Tried to use UNION but

the results in one column.

It's complicated with creating a temp table since I do not know how to insert to temp table from database.

Thanks

Daniel

|||Please supply the requested information. (See my previous post.)

Can Triggers Help Me out

Dear Experts,
I am storing the rates in pricelist table and these rates are used in
several tables.
Is is possible to create a trigger that will change rates in all the tables
wherever i have used the moment someone changes the rates in pricelist?
Thanks
Manish Sawjiani
Three Cheers to Technet for the Help!
Hi Manish
Triggers can help you, but u need to write
UPDATE <TABLE> ...
...
...
and so on, for all the tables that involved the price...
best Regards,
Chandra
http://chanduas.blogspot.com/
http://www.codecomments.com/gurus/default.asp?p=4223
"Manish Sawjiani" wrote:

> Dear Experts,
> I am storing the rates in pricelist table and these rates are used in
> several tables.
> Is is possible to create a trigger that will change rates in all the tables
> wherever i have used the moment someone changes the rates in pricelist?
> Thanks
> Manish Sawjiani
> --
> Three Cheers to Technet for the Help!
|||Manish Sawjiani wrote:
> Dear Experts,
> I am storing the rates in pricelist table and these rates are used in
> several tables.
> Is is possible to create a trigger that will change rates in all the
> tables wherever i have used the moment someone changes the rates in
> pricelist?
> Thanks
> Manish Sawjiani
Why not create a relationship between the rates and these other tables
if they need to be kept in sync with the main table? If that won't work,
you can use an update trigger and the inserted table to get a list of
all rows that were updated.
David Gugick
Imceda Software
www.imceda.com