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!!
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment