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 transpose a table using sql statement

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()
>
> > 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!!
>
>|||This is a multi-part message in MIME format.
--020008060109080504060608
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
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:
>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()
>>
>>
>>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!!
>>
>>
--020008060109080504060608
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>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.<br>
<br>
See <a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.aspfaq.com/2462</a></tt><br>">http://www.aspfaq.com/2462">http://www.aspfaq.com/2462</a></tt><br>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
kei wrote:
<blockquote cite="mid723F76A5-9F57-42B1-B787-DF454C95361D@.microsoft.com"
type="cite">
<pre wrap="">Oh! I am using SQL2000,is there any method using SQL Server 2000?
"Greg D. Moore (Strider)" wrote:
</pre>
<blockquote type="cite">
<pre wrap="">"kei" <a class="moz-txt-link-rfc2396E" href="http://links.10026.com/?link=mailto:kei@.discussions.microsoft.com"><kei@.discussions.microsoft.com></a> wrote in message
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com">news:FDCA2943-F41E-4E4D-9BA7-823A47A69417@.microsoft.com</a>...
If you're using SQL 2005, look up pivot()
</pre>
<blockquote type="cite">
<pre wrap="">as caption, I want to use sql statement to transponse a table (row to
</pre>
</blockquote>
<pre wrap="">column
</pre>
<blockquote type="cite">
<pre wrap="">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!!
</pre>
</blockquote>
<pre wrap="">
</pre>
</blockquote>
</blockquote>
</body>
</html>
--020008060109080504060608--|||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!!

How to trace trigger execution in SQL Profiler?

When I try to trace some SQL statement executions inside a Trigger by using 2005 SQL Profiler, I found even the trigger is fired, there's no any trace about it in SQL Profiler. How can I trace trigger execution in 2005 SQL Profiler?Hi,

have a look here: http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/c87621996c73012b

HTH, Jens SUessmeyer.

http://www.sqlserver2005.de

how to sum up this thing?

hi guys.
in my select statement, i have this
CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid

the problem that i faced is, i need to sum up another time according to the year. the above statement will shoe the amount paid. i need to sum up the amount for each year. i've tried to use sum function, but it gives me error. Please help me to solve this problem. Thanks for all advise. I would appreciate it very much.

The simplified query....

(SELECT Convert(varchar,ISNULL(SUM(TC.amount),0)) FROM tb_payment AS TC
WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL) as AmountPaid

Here you can use join rather subquery, if you post the full query i may help you to tune..

|||
SELECT TOP 5 tr.year, CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid, tr.edemedpoints, tr.adjustedpoints, tr.expiredpoints

FROM tb_pointtransactions AS TA
JOIN tb_transactionsummaries tr on tr.transactionid=TA.transactionid
JOIN tb_memberships m ON TA.membershipid = m.membershipid
JOIN tb_users u ON u.UserID = m.UserID
LEFT JOIN tb_salesstatus s ON s.id = TA.salesstatus
INNER JOIN tb_saletransactions AS TB ON TA.TransactionID = TB.TransactionID AND TA.deletedby IS Null
WHERE membershipid = '1' ORDER BY year

i'm really appreciate your help, ManiD. I'm still a newbie in programming field. With your help, my learning path will be wonderful. Thank you very much.

How to sum Salary column with a condition

Hi,
I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
statement to get it? Please show me the SQL statement. Thank a lot.
EmployeeNo PayDate Salary
1 1/31/1985 $1,000.00
1 2/28/1985 $1,000.00
1 3/31/1985 $1,000.00
1 4/30/1985 $1,000.00
1 5/31/1985 $1,000.00
1 6/30/1985 $1,000.00"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>
I assume that you want to do some grouping, or else you could simply use a
WHERE clause.
select EmployeeNo,
sum(case when PayDate<'5/1/1985' then Salary else 0 end) Salary
from MyTable
group by EmployeeNo
David|||Chris,
Try....
Select Sum(Salary) as 'Total Salary', EmployeeNo
from YourTable
where PayDate < '5/1/1985'
Group By EmployeeNo
Thanks
Barry|||Thank you both for the quick help.
"chris" <yma16@.kicon.com> wrote in message
news:eYNiCgqGFHA.3916@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'd like to sum the salary for the PayDate<'5/1/1985'. Can I use one
SQL
> statement to get it? Please show me the SQL statement. Thank a lot.
> EmployeeNo PayDate Salary
> 1 1/31/1985 $1,000.00
> 1 2/28/1985 $1,000.00
> 1 3/31/1985 $1,000.00
> 1 4/30/1985 $1,000.00
> 1 5/31/1985 $1,000.00
> 1 6/30/1985 $1,000.00
>

how to store output of exec stmt in another variable?

Hi

I am trying to store the output/result of exec statement in another variable like @.b.

Code Snippet

declare @.a varchar(10)

declare @.b int

set @.a='2 * 3';

EXEC ('select ' + @.a)

in the above sample I need to store 6 in @.b, how?

Please advice

Thanks

You could use sp_executesql like:

declare @.res int /*?*/;

declare @.sql nvarchar(max);

set @.sql = N'SELECT @.res = ' + @.a; -- Protect against SQL injection by using QUOTENAME as appropriate etc...

exec sp_executesql @.sql, N'@.res int OUTPUT', @.res = @.res OUTPUT;

But why do you want to do this? What are you trying to achieve? In SQL Server 2005, you could store the formula in a table and evaluate using CLR logic. This will actually perform better for complex calculations and will not suffer from SQL Injection issues. Otherwise, you should try to avoid dynamic SQL as far as possible. It is easy to code but hard to get it right in terms of security, performance and manageability. The risks are too much if you miss anything.

|||

Thanks a lot Chandar, for your nice reply

I got my answer from your query

The reason why I want this one is

I calculated the product of some values in a Table's column using COALESCE & EXEC Statements. But I am unable to store that result in a variable.

But now I got it using exec sp_executesql etc…..

Once again Thanks

|||You don't need to use dynamic SQL to get the product of values. You can do that with simple expressions using SUM and LOG. Search in this forum for my posts on this topic - I have some links that point to sample code that you can use.