Transpose Row to Columns

I want to transpose rows to columns
Query goes like this

create table #a
(
col1 int,
col2 char(1)
)
go

Insert into #a values(1,"a")
Insert into #a values(1,"b")
Insert into #a values(1,"c")
Insert into #a values(1,"d")
Insert into #a values(2,"a")
Insert into #a values(2,"b")
Insert into #a values(3,"a")
Insert into #a values(3,"b")
Insert into #a values(3,"c")
Insert into #a values(3,"d")
Insert into #a values(3,"e")
go

select * from #a
go

OUTPUT:-

col1	col2
1 	a
1 	b
1 	c
1 	d
2 	a
2 	b
3 	a
3 	b
3 	c
3 	d
3 	e

Expected Output:-

col1                  col2
1                      a,b,c,d
2                      a,b
3                      a,b,c,d,e

Currently I am using Cursor for this which is taking to much
time.
I can't use Dynamic SQL because i am working on 12.0.8
Version.

Thanks
0
Ashish
11/7/2005 10:04:31 AM
sybase.ase.general 8655 articles. 0 followers. Follow

4 Replies
2302 Views

Similar Articles

[PageSpeed] 24

I hope this is acceptable to you...

set nocount on
go
create table #input1
(
col1 int,
col2 char(1)
)
Insert into #input1 values(1,"a")
Insert into #input1 values(1,"b")
Insert into #input1 values(1,"c")
Insert into #input1 values(1,"d")
Insert into #input1 values(2,"a")
Insert into #input1 values(2,"b")
Insert into #input1 values(3,"a")
Insert into #input1 values(3,"b")
Insert into #input1 values(3,"c")
Insert into #input1 values(3,"d")
Insert into #input1 values(3,"e")

create table #out1 (col1 int , col2 varchar(10))

select distinct col1 into #col1 from #input1
declare @col1 int

set rowcount 1
select @col1 = col1 from #col1
while @@rowcount > 0
begin
      insert #out1 select @col1,""
    while ( select count(*) from #input1 a, #out1 b
            where a.col1=b.col1 and
            charindex(a.col2,b.col2) = 0
          ) > 0
    begin
          update #out1 set col2=b.col2+ a.col2 from #input1
a, #out1 b
                 where a.col1=b.col1 and
                 charindex(a.col2,b.col2) = 0
    end
delete #col1
select @col1 = col1 from #col1

end
set rowcount 0
select * from #out1
go



> I want to transpose rows to columns
> Query goes like this
>
> create table #a
> (
> col1 int,
> col2 char(1)
> )
> go
>
> Insert into #a values(1,"a")
> Insert into #a values(1,"b")
> Insert into #a values(1,"c")
> Insert into #a values(1,"d")
> Insert into #a values(2,"a")
> Insert into #a values(2,"b")
> Insert into #a values(3,"a")
> Insert into #a values(3,"b")
> Insert into #a values(3,"c")
> Insert into #a values(3,"d")
> Insert into #a values(3,"e")
> go
>
> select * from #a
> go
>
> OUTPUT:-
>
> col1    col2
> 1     a
> 1     b
> 1     c
> 1     d
> 2     a
> 2     b
> 3     a
> 3     b
> 3     c
> 3     d
> 3     e
>
> Expected Output:-
>
> col1                  col2
> 1                      a,b,c,d
> 2                      a,b
> 3                      a,b,c,d,e
>
> Currently I am using Cursor for this which is taking to
> much time.
> I can't use Dynamic SQL because i am working on 12.0.8
> Version.
>
> Thanks
0
Eddie
11/8/2005 6:19:34 AM
This is a multi-part message in MIME format.
--------------010800090303030201080009
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Ashih,

Take alook on
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e

HTH,
Eugene
Ashish Khanolkar wrote:

>I want to transpose rows to columns
>Query goes like this
>
>create table #a
>(
>col1 int,
>col2 char(1)
>)
>go
>
>Insert into #a values(1,"a")
>Insert into #a values(1,"b")
>Insert into #a values(1,"c")
>Insert into #a values(1,"d")
>Insert into #a values(2,"a")
>Insert into #a values(2,"b")
>Insert into #a values(3,"a")
>Insert into #a values(3,"b")
>Insert into #a values(3,"c")
>Insert into #a values(3,"d")
>Insert into #a values(3,"e")
>go
>
>select * from #a
>go
>
>OUTPUT:-
>
>col1	col2
>1 	a
>1 	b
>1 	c
>1 	d
>2 	a
>2 	b
>3 	a
>3 	b
>3 	c
>3 	d
>3 	e
>
>Expected Output:-
>
>col1                  col2
>1                      a,b,c,d
>2                      a,b
>3                      a,b,c,d,e
>
>Currently I am using Cursor for this which is taking to much
>time.
>I can't use Dynamic SQL because i am working on 12.0.8
>Version.
>
>Thanks
>  
>


--------------010800090303030201080009
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#99ffff" text="#000099">
Ashih,<br>
<br>
Take alook on <br>
<a class="moz-txt-link-freetext" href="http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e">http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e</a><br>
<br>
HTH,<br>
Eugene<br>
Ashish Khanolkar wrote:
<blockquote cite="mid436f34bf.7404.1681692777@sybase.com" type="cite">
  <pre wrap="">I want to transpose rows to columns
Query goes like this

create table #a
(
col1 int,
col2 char(1)
)
go

Insert into #a values(1,"a")
Insert into #a values(1,"b")
Insert into #a values(1,"c")
Insert into #a values(1,"d")
Insert into #a values(2,"a")
Insert into #a values(2,"b")
Insert into #a values(3,"a")
Insert into #a values(3,"b")
Insert into #a values(3,"c")
Insert into #a values(3,"d")
Insert into #a values(3,"e")
go

select * from #a
go

OUTPUT:-

col1	col2
1 	a
1 	b
1 	c
1 	d
2 	a
2 	b
3 	a
3 	b
3 	c
3 	d
3 	e

Expected Output:-

col1                  col2
1                      a,b,c,d
2                      a,b
3                      a,b,c,d,e

Currently I am using Cursor for this which is taking to much
time.
I can't use Dynamic SQL because i am working on 12.0.8
Version.

Thanks
  </pre>
</blockquote>
<br>
</body>
</html>

--------------010800090303030201080009--
0
Eugene
11/8/2005 3:41:08 PM
Hi Eugene,

Is it possible for you to paste that code here because that
link is blocked.




> Ashih,
>
> Take alook on
>
http://groups.google.com/group/microsoft.public.sqlserver.programming/msg/2d85bf366dd9e73e
>
> HTH,
> Eugene
> Ashish Khanolkar wrote:
>
> >I want to transpose rows to columns
> >Query goes like this
> >
> >create table #a
> >(
> >col1 int,
> >col2 char(1)
> >)
> >go
> >
> >Insert into #a values(1,"a")
> >Insert into #a values(1,"b")
> >Insert into #a values(1,"c")
> >Insert into #a values(1,"d")
> >Insert into #a values(2,"a")
> >Insert into #a values(2,"b")
> >Insert into #a values(3,"a")
> >Insert into #a values(3,"b")
> >Insert into #a values(3,"c")
> >Insert into #a values(3,"d")
> >Insert into #a values(3,"e")
> >go
> >
> >select * from #a
> >go
> >
> >OUTPUT:-
> >
> >col1    col2
> >1     a
> >1     b
> >1     c
> >1     d
> >2     a
> >2     b
> >3     a
> >3     b
> >3     c
> >3     d
> >3     e
> >
> >Expected Output:-
> >
> >col1                  col2
> >1                      a,b,c,d
> >2                      a,b
> >3                      a,b,c,d,e
> >
> >Currently I am using Cursor for this which is taking to
> much >time.
> >I can't use Dynamic SQL because i am working on 12.0.8
> >Version.
> >
> >Thanks
> >
> >
>
>
0
Ashish
11/9/2005 3:55:30 AM
Hi Eddie,

Thanks for the solution its very close to what i want to do,
But i want those values in column 'col2' coma seperated.

Thanks

> I hope this is acceptable to you...
>
> set nocount on
> go
> create table #input1
> (
> col1 int,
> col2 char(1)
> )
> Insert into #input1 values(1,"a")
> Insert into #input1 values(1,"b")
> Insert into #input1 values(1,"c")
> Insert into #input1 values(1,"d")
> Insert into #input1 values(2,"a")
> Insert into #input1 values(2,"b")
> Insert into #input1 values(3,"a")
> Insert into #input1 values(3,"b")
> Insert into #input1 values(3,"c")
> Insert into #input1 values(3,"d")
> Insert into #input1 values(3,"e")
>
> create table #out1 (col1 int , col2 varchar(10))
>
> select distinct col1 into #col1 from #input1
> declare @col1 int
>
> set rowcount 1
> select @col1 = col1 from #col1
> while @@rowcount > 0
> begin
>       insert #out1 select @col1,""
>     while ( select count(*) from #input1 a, #out1 b
>             where a.col1=b.col1 and
>             charindex(a.col2,b.col2) = 0
>           ) > 0
>     begin
>           update #out1 set col2=b.col2+ a.col2 from
> #input1 a, #out1 b
>                  where a.col1=b.col1 and
>                  charindex(a.col2,b.col2) = 0
>     end
> delete #col1
> select @col1 = col1 from #col1
>
> end
> set rowcount 0
> select * from #out1
> go
>
>
>
> > I want to transpose rows to columns
> > Query goes like this
> >
> > create table #a
> > (
> > col1 int,
> > col2 char(1)
> > )
> > go
> >
> > Insert into #a values(1,"a")
> > Insert into #a values(1,"b")
> > Insert into #a values(1,"c")
> > Insert into #a values(1,"d")
> > Insert into #a values(2,"a")
> > Insert into #a values(2,"b")
> > Insert into #a values(3,"a")
> > Insert into #a values(3,"b")
> > Insert into #a values(3,"c")
> > Insert into #a values(3,"d")
> > Insert into #a values(3,"e")
> > go
> >
> > select * from #a
> > go
> >
> > OUTPUT:-
> >
> > col1    col2
> > 1     a
> > 1     b
> > 1     c
> > 1     d
> > 2     a
> > 2     b
> > 3     a
> > 3     b
> > 3     c
> > 3     d
> > 3     e
> >
> > Expected Output:-
> >
> > col1                  col2
> > 1                      a,b,c,d
> > 2                      a,b
> > 3                      a,b,c,d,e
> >
> > Currently I am using Cursor for this which is taking to
> > much time.
> > I can't use Dynamic SQL because i am working on 12.0.8
> > Version.
> >
> > Thanks
0
Ashish
11/9/2005 3:57:59 AM
Reply:

Similar Artilces:

Transpose rows to columns
I have been presented with an interesting problem. I have a table that looks like the following: year integer quarter integer volume long price decimal The user insists they be able to maintain it this way... year quarter vol1 price1 vol2 price2 vol3 price3 ... 1997 1 1000 .50 10000 .45 100000 .40 1997 2 1000 .45 10000 .40 100000 .35 I have been unable to think of an alternative format which will allow them to see the data in the same fashion (i.e. the price decreasing over time). In essence, transpose the rows to columns. This wouldn't be so bad, but they want the a...

Transposing Rows and Columns
Is there an easy way to set up a bound data grid to display each row from my table in columns on the screen ( with, of course the rows corresponding to the columns in the data). any suggestions thanks Iain Have you tried the CrossTab datawindow? -- Jim Egan [TeamPS] Dealer Solutions, LLC Houston, TX Jim Egan wrote: > > Have you tried the CrossTab datawindow? > -- > Jim Egan [TeamPS] > Dealer Solutions, LLC > Houston, TX No, I only have the Developer version....guess I'll have to upgrade or code something myself. Thanks, Iain ...

How to go through a datatable row by row and column by column
Hello, I have a dataset with a datatable and I need to insert each row from the datatable on my sql table, anyway there may be errors, so some rows may not be inserted because primary key constraints,  So I need to show the rows that are not inserted in a gridview, the hard thing to do is to go through all the datatable,  is there an wasy way to do it?MCPD ENTERPRISE APPLICATION DEVELOPER Start by looking herehttp://msdn2.microsoft.com/en-us/library/system.data.common.dataadapter.update.aspxPerform an update of your dataset, and if a row fails, you can mark it as invalid.You can ...

Transposing Rows into Columns
I have a question on how to transpose rows into columns. Suppose I have a table listing users and another table with an unknown number of user attributes, link by keys. I want to select a particular user and all the attributes, but don't want the user data repeated in every row for each attibute if I just do a simple join. Is there a way to get the result set into just 1 row with the rows from the attribute table as columns in the result set? My ultimate goal is to somehow join the two columns to make 1 database call for when enumerating the users without having to go back and ma...

Transposing rows to columns on a subreport
I have a report set up for printing labels, but the labels require more than just an address.  The labels also have the addressee's ID number and a field that holds from 1 up to 15 1-letter codes that we use for mailing purposes.  In order to retrieve these multiple codes, I set up a subreport that is linked on the addressee's ID number.  The codes are retrieved perfectly, but I need them to display horizontally rather than vertically.  i.e.: ABC rather than: A B C  How can I do this? I tried inserting the results from the subform into...

computing the difference of a column in the current row from the column of the preceding row
Hello, a user asked me if we can solve following problem: we want to compute the difference (delta) of a column in the current row from the column of the preceding row. My first solution signs where: create a computed field (small_function_result) that takes the value from the preceding row: small(value, value, cf_getrow for all); where cf_getrow is a computed field like this: if(getrow()=1,getrow(),getrow()-1) create a computed field (Diff) that computes the difference: value-small_function_result My results should look like this: Row Value cf_getrow small_func...

GridView.Rows(Column=ID_PK) to GridView.Rows(Column=DropDownListTeamplteField)
Hi Guys,I wonder how I can assign the PrimaryKey in each GridView Row to the template field in the same row. whereas the template field is filled with a dropdownlist thatshould show the values for retrieved in a different table through the GridView Row primary key (which serves as the SELECT parameter to get these values) The GridView looks like this:<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="C_ID" DataSourceID="GetC_IDs"> <Columns> <asp:BoundField D...

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

How to divide 1 GridView Row with 1 column in 2 Rows with 5 columns?
Hi; I have a GridView with 10 rows and 1 column. How can I do to this GridView show the same data, but with 2 rows and 5 columns? In classic ASP, I made it this way: While (NOT rsFotos.EOF) if i mod 5 = 0 then Response.Write "</tr><tr>" end if ... Someone can help? :)  You have to use a custom template and lay the table out the way you want, either dropping controls in the template or creating a custom ITemplate that takes care of it for you. http://professionalaspnet.com/archive/2006/06/15/Useing-ITemplate-in-the-Data-Controls.aspxChris L...

newBie How to Transpose a Data Row to column
Dear All, Can anyone suggest me how to go about transpose data in Sql/PL Sql For Example : Say T1 table having following data column1 Column2 H1 A1 H1 A2 H2 A1 H2 A3 H2 A4 H2 A5 Same data need to be represented as follows [Assume max column is fixed] Col1 Col2 Col3 Col4 Col5 H1 A1 A2 H2 A1 A3 A4 A5 Without using row processing is it possible to achieve the same? Thanks & Regards Raghav Raghav wrote: > Dear All, &g...

is it possible to turn 1 column,many rows into one row,many columns in sql ?
 Hi AllI have the following query:Select CategoryName From Category Where CategoryId In(Select SkillId From SpecialitySkills Where CopywriterId = 13) It gives me the correct results but not in the form that i want, i would like it to be in one row, many columns. its in 1 column, many rows.Any ideas?  You must be talking about displaying it on a web page? Just create a loop that runs through the rows of the data that is returned then just make html table:dim tablestring as string = "<table><tr>" for i as integer = 0 to dt.rows.count - 1 tablestring += &...

How can I display multiple rows w/8 columns (4 columns editable on each row)?
Hello, Please - Can anyone suggest the best way to display a joined dataset with 4 read-only columns, 4 editable columns and a checkbox column? The content for this display currently has < 100 rows - but could approach 1000 in the future. I currently have this setup with a Datagrid (working) - but I would like to be able to edit the 4 columns of ALL rows in the displayed list instead of one row at a time as the Datagrid allows. After editing, I use an update function to traverse the modified list/table and save all values that have the Checkbox clicked. Could a list with 10...

Calculated column from columns in same row??
Hi everyone - Is there a way to display a calculated value in a column based on values from the same row? Using a datagrid thanks tony Sure. Have a function declared for your OnItemDataBound: Sub CalculateValues(ByVal sender As Object, ByVal e As DataGridItemEventArgs) dim total as Integer = CType(e.Item.FindControl("col1"), Integer) + CType(e.Item.FindControl("col2"), Integer) e.Item.FindControl("totalCOlumn").Text = total End Sub I think it would be something like that.Sarahttp://www.ilovecode.com If yo...

Arranging data on multiple rows into a sigle row (converting rows into columns)
Hello, I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row: user1   answer1user1   answer2user1   answer3user2   answer1user2   answer2user2   answer3 For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on...

Web resources about - Transpose Row to Columns - sybase.ase.general

Conjugate transpose - Wikipedia, the free encyclopedia
where the subscripts denote the i , j -th entry, for 1 ≤ i ≤ n and 1 ≤ j ≤ m , and the overbar denotes a scalar complex conjugate . (The complex ...

Transpose focuses on business customers, starts charging for its information management platform
Transpose announced a major strategy shift on Wednesday, as the Seattle startup is about to turn its full attention to business customers. The ...

Jean-François Bouchard’s Transpose captures the personal stories of trans men
... Bouchard says, "or shocking. I wanted this to be about the personal stories" Before Jean-François Bouchard began taking photos for Transpose, ...

Transpose - Wikipedia, the free encyclopedia
, resulting in an isomorphism between the transpose and adjoint of f . The matrix of the adjoint of a map is the transposed matrix only if the ...

Transpose of a linear map - Wikipedia, the free encyclopedia
( φ ), v ] = [ φ , f ( v )] , is formally similar to the definition of the Hermitian adjoint , however, the transpose and the Hermitian adjoint ...

KustomNote rebrands as Transpose, raises $1.5M for holistic information management platform
Transpose, a new Seattle startup that bills itself as a holistic information management platform, today announced a $1.5 million funding round. ...

Resources last updated: 2/11/2016 7:24:35 AM