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 make a second to get the list of attibutes.
1
Jason
3/10/2006 11:15:31 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
2970 Views

Similar Articles

[PageSpeed] 52

Here is an examlpe of what I'd like to do:

Table 1

ID  UserName
1       jay
2       you

Table 2
ID   AttributeName         AttributeValue
1       Weight                        175
1       HairColor                     Brown
2       Weight                        176
2       HairColor                     Purple
2       EyeColor                     Blue

Current Outut:
ID  UserName    AttributeName         AttributeValue
1      jay              Weight                        175
1      jay              HairColor                     Brown

Desired Output:
ID  UserName    Weight       HairColor
1      jay              175          Brown
0
Jason
3/10/2006 11:58:38 PM
The procedure below should do what you want:

CREATE TABLE t1 (
  ID INTEGER,
  UserName VARCHAR(10)
);

CREATE TABLE t2 (
  ID INTEGER,
  AttributeName VARCHAR(10),
  AttributeValue VARCHAR(10)
);

INSERT INTO t1 (ID, UserName) VALUES (1, 'jay');
INSERT INTO t1 (ID, UserName) VALUES (2, 'you');

INSERT INTO t2 (ID, AttributeName, AttributeValue)
  VALUES (1, 'Weight', '175');
INSERT INTO t2 (ID, AttributeName, AttributeValue)
  VALUES (1, 'HairColor', 'Brown');
INSERT INTO t2 (ID, AttributeName, AttributeValue)
  VALUES (2, 'Weight', '176');
INSERT INTO t2 (ID, AttributeName, AttributeValue)
  VALUES (2, 'HairColor', 'Purple');
INSERT INTO t2 (ID, AttributeName, AttributeValue)
  VALUES (2, 'EyeColor', 'Blue');

CREATE PROCEDURE AttrTable(IN userid INTEGER)
BEGIN
  DECLARE attributelist VARCHAR(200);
  SELECT LIST(' ''' || AttributeValue ||
    ''' AS "' || AttributeName || '"')
    INTO attributelist
  FROM t2
  WHERE ID = userid;
  EXECUTE IMMEDIATE WITH RESULT SET ON
    STRING('SELECT ID, UserName, ', attributelist,
      ' FROM t1 WHERE ID = ', userid);
END;

CALL AttrTable(1);


  Richard


<Jason L> wrote in message news:441212ae.d94.1681692777@sybase.com...
> Here is an examlpe of what I'd like to do:
>
> Table 1
>
> ID  UserName
> 1       jay
> 2       you
>
> Table 2
> ID   AttributeName         AttributeValue
> 1       Weight                        175
> 1       HairColor                     Brown
> 2       Weight                        176
> 2       HairColor                     Purple
> 2       EyeColor                     Blue
>
> Current Outut:
> ID  UserName    AttributeName         AttributeValue
> 1      jay              Weight                        175
> 1      jay              HairColor                     Brown
>
> Desired Output:
> ID  UserName    Weight       HairColor
> 1      jay              175          Brown


0
Richard
3/11/2006 10:07:17 AM
I am not certain how multiple rows is less difficult
to use then some some of list ... but ...
 ... after guessing a lot about your schema ...

Does the LIST aggregate function suffice?

Something like

    select
        --fc.*,fd.*
        fc.code,list( fd.amount )
    from fin_code fc left outer join fin_data fd
                on fc.code=fd.code
    where fc.code ='e1'
    group by fc.code;

when applied to the asademo.db database.



<Jason L> wrote in message news:4411f8e0.2290.1681692777@sybase.com...
>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 make a second to get the list of attibutes. 


0
Nick
3/14/2006 9:08:26 PM
Reply:

Similar Artilces:

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 ...

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:- col...

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 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...

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...

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...

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 ...

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...

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...

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...

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 += &...

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 - Transposing Rows into Columns - sybase.sqlanywhere.general

Transposing instrument - Wikipedia, the free encyclopedia
A transposing instrument is a musical instrument whose music is notated at a pitch different from the pitch that actually sounds ( concert pitch ...

Transposing Helper on the App Store
Read reviews, compare customer ratings, see screenshots, and learn more about Transposing Helper. Download Transposing Helper and enjoy it on ...

Video - Transposing the Icons of Google Maps Into the Real World - The Atlantic
Urban art installations explore the growing influence of technology in our lives.&nbsp;

After Paris, Transposing Europe and America
After Paris, Transposing Europe and America

Transposing the Icons of Google Maps Into the Real World
Artist Aram Bartholl explores the growing influence of digital technology and the social web through urban installations.

Resources last updated: 2/4/2016 1:41:50 AM