how to transpose a table using sql statement

Just curious: why do you want to do this?
What kind of software environment do you work in?"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()

> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:

> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> column
>
>|||With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
[vbcol=seagreen]
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>|||as caption, I want to use sql statement to transponse a table (row to column
and column to row), is this possible and how to script it?
e.g.
1,2,3
4,5,6
(1,2,3) is column name
after transponse, the table become
1,4
2,5
3,6
(1,4) is column name
Thx!!|||"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
If you're using SQL 2005, look up pivot()

> as caption, I want to use sql statement to transponse a table (row to
column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!|||Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:

> "kei" <kei@.discussions.microsoft.com> wrote in message
> news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> If you're using SQL 2005, look up pivot()
>
> column
>
>|||With SQL Server 2000, you do it with a GROUP BY clause and a series of
<AggregateFunction>(CASE...) columns in the select list, where
AggregateFunction is SUM() or MAX() or MIN(), etc. depending on your needs.
See http://www.aspfaq.com/2462
*mike hodgson*
http://sqlnerd.blogspot.com
kei wrote:
[vbcol=seagreen]
>Oh! I am using SQL2000,is there any method using SQL Server 2000?
>"Greg D. Moore (Strider)" wrote:
>
>|||Just curious: why do you want to do this?
What kind of software environment do you work in?|||may be i can help you in this regard.
can u publish some sample data with scripts to create object?
kay
"kei" <kei@.discussions.microsoft.com> wrote in message
news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com...
> as caption, I want to use sql statement to transponse a table (row to
> column
> and column to row), is this possible and how to script it?
> e.g.
> 1,2,3
> 4,5,6
> (1,2,3) is column name
> after transponse, the table become
> 1,4
> 2,5
> 3,6
> (1,4) is column name
> Thx!!sql

how to transfer to multiple tables based on environment

How is it possible to set data to flow through development,test,and production environment?

I have created one package.Right now i am transferring data to tables in the development environment.

I want to use the same package for several diffrent distinations two based on environment.

Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..

I think Package configurations will do that for you. You can make your package 'portable' through environments by using them. Search this forum; there is a lot of info on that|||Configurations are designed to help you change the environment without alter the code in your package. You can use them to set connect strings, etc dynamically at runtime. If you right-click in an empty area of your package, the menu will contain an option for package configurations. There's good information in Books Online and this forum on how to use them.|||

Is it possible to change the connection for the lookups which i have used in the package..

|||Yes, by setting the connection string for the connection manager used in the lookup.|||sorry for not been clear ...is it possible to change connection in lookup using package configuration..|||I don't think I am understanding your question. Do you want to change the connection the lookup is using? If so, the answer is yes. If you are asking if you can use the lookup to change another connection string, the answer is no (at least not easily.) Could you describe your scenario in a little more detail?|||

sureshv wrote:

sorry for not been clear ...is it possible to change connection in lookup using package configuration..

The lookup component like the OLE DB components and other source destinations, use connection managers. The connection managers have properties. Then you can use package configurations to change those properties at run time (e.g connection strings) without having to change the package.

So, the short answer is YES.

Look in this forum and BOL for package configurations and you will get a better understanding.

|||will it cause any problem if i set the package configuration after creating the entire package..|||No, you should be able to set up configurations at any time.

how to transfer to multiple tables based on environment

How is it possible to set data to flow through development,test,and production environment?

I have created one package.Right now i am transferring data to tables in the development environment.

I want to use the same package for several diffrent distinations two based on environment.

Is there a way that the destination componnent sets itself automatically by passing destination tables through registry or is there anything i could or do i need to change manually everytime which ever i want to..

I think Package configurations will do that for you. You can make your package 'portable' through environments by using them. Search this forum; there is a lot of info on that|||Configurations are designed to help you change the environment without alter the code in your package. You can use them to set connect strings, etc dynamically at runtime. If you right-click in an empty area of your package, the menu will contain an option for package configurations. There's good information in Books Online and this forum on how to use them.|||

Is it possible to change the connection for the lookups which i have used in the package..

|||Yes, by setting the connection string for the connection manager used in the lookup.|||sorry for not been clear ...is it possible to change connection in lookup using package configuration..|||I don't think I am understanding your question. Do you want to change the connection the lookup is using? If so, the answer is yes. If you are asking if you can use the lookup to change another connection string, the answer is no (at least not easily.) Could you describe your scenario in a little more detail?|||

sureshv wrote:

sorry for not been clear ...is it possible to change connection in lookup using package configuration..

The lookup component like the OLE DB components and other source destinations, use connection managers. The connection managers have properties. Then you can use package configurations to change those properties at run time (e.g connection strings) without having to change the package.

So, the short answer is YES.

Look in this forum and BOL for package configurations and you will get a better understanding.

|||will it cause any problem if i set the package configuration after creating the entire package..|||No, you should be able to set up configurations at any time.

How to tell when data has been replicated?

Hello,

In our environment (SQL 2005) we have a database that uses Transactional Replication to sync data between two SQL 2005 servers. There is a web app that reads/writes data to the publisher server and the other server (that gets the replicated data) is used by some other internal applications.

At times, there is a need to delete some data from the publisher server...but this can ONLY happen once the data has been successfully replicated to the second server. Is there any way to determine if a row has been replicated successfully?

thanks

- will

If you are using transactional, it shouldn't matter when you fire the delete through. That's because it maintains the exact sequence of transactions that were performed against the publisher at all times such that nothing can get out of order. So, if it is valid to issue on the publisher, it will be valid to issue against the subscriber. You can tell if anything is pending being sent by looking in the Replication Monitor. In order to check a particular row, you have to hit the subscriber and check it.|||

Actually, now that I think about it, the requirement is to allow deletes on the Publisher server, but not have those get replicated to the other server (the subscriber) that is part of the transactional replication. So, in this situation, if this is running under transactional replication, every action (Insert, Update, Delete) will be run at the Publisher and then at the subscriber server...which is not what the customer wants. They want the data deleted off our server (the publisher), but want to keep it on their server (the subscriber). Hmmmm.....

|||You can change the article properties to not replicate deletes, or use custom stored procedures to delete nothing.

how to store and retrieve image files in sql server

hi like to knw the steps for storing 1000+ image files in an sql server.....
i/p: 1000+ image files

o/p: should be displayed in an vb6 environment in an pic box.....I would recommend you storing full path to picture file and not file itself then get path from a database and load picture from file into a picture box.
It is extremely slow and complicated to do it on the database side. You should be able to convert picture file into a binary stream to store it on the database and then be able to decode this binary stream back to picture.

Good Luck.