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
0
Kaye
11/25/1998 5:06:48 PM
sybase.infomaker.general 4461 articles. 0 followers. Follow

3 Replies
1785 Views

Similar Articles

[PageSpeed] 29

This is essentially a crosstab report, with the rows = key and column =
book_title and values = book_title.

--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.
Kaye Hendry wrote in message <365C3928.7866@healthinsight.org>...
>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


0
Terry
11/25/1998 7:19:35 PM
I will try the crosstabs, but I am wondering if you can
write SQL  code so you could use the "rows save as" option 
to write out to a spreadsheet or use the code to pipeline 
to a table or view. Thanks in advance.

Terry Dykstra wrote:

> 
> This is essentially a crosstab report, with the rows = key and column =
> book_title and values = book_title.
> 
> --
> Terry Dykstra (TeamPS)
> Canadian Forest Oil Ltd.
> Kaye Hendry wrote in message <365C3928.7866@healthinsight.org>...
> >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

-- 
	Kaye Hendry
    HealthInsight
	email:kaye.hendry@healthinsight.org
1
Kaye
11/25/1998 8:57:29 PM
If you use SQLAnywhere, you might be able to use the list() function.
Otherwise you will have to write some kind of stored procedure to get the
desired result.
If you can make the crosstab static, you can use the 'rows save as'.

--
Terry Dykstra (TeamPS)
Canadian Forest Oil Ltd.
Kaye Hendry wrote in message <365C6F39.31B2@healthinsight.org>...
>I will try the crosstabs, but I am wondering if you can
>write SQL  code so you could use the "rows save as" option
>to write out to a spreadsheet or use the code to pipeline
>to a table or view. Thanks in advance.
>
>Terry Dykstra wrote:
>
>>
>> This is essentially a crosstab report, with the rows = key and column =
>> book_title and values = book_title.
>>
>> --
>> Terry Dykstra (TeamPS)
>> Canadian Forest Oil Ltd.
>> Kaye Hendry wrote in message <365C3928.7866@healthinsight.org>...
>> >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
>
>--
> Kaye Hendry
>    HealthInsight
> email:kaye.hendry@healthinsight.org


0
Terry
11/26/1998 3:56:45 PM
Reply:

Similar Artilces:

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

Gridview
Hi. What I would like to do is to have a layout like this ID Description SomeOthercolumn ----------------------------------------------------------------------------- ID Short description Text Some other value Long description Text that will span multiple columns ID #2 Short description Text #2 Some other value #2 Long description Text that will span multiple columns #2 Get the picture. This is not a problem with repeaters, because you ha...

Accessing one column in one row
Every thing I have researched on accessing data is to put it into a multi row and multi column control.  If you are already using a sqldatasource to fill a listbox, how can you use the same sqldatasource to obtain a single column out of a single row in the same database?  The rows contain an identy column and only one row in the database has a specific identy.  The row has three columns.  The first column is "nvchar" and I want to put its contents into a textbox.text.  I tried setting the select parameter to the desired identy ...

select all row one more than one column
   I add checkbox control in header template to select all row. if one column have select all propert there is no problem. However, when add second column and add select all another column there occur a problem. When ı select first column select all checkbox, second colum checkbox checked. How can ı solve this problem.          Hi,Following links will help you out ...http://programming.top54u.com/post/ASP-Net-GridView-Checkbox-Select-All-using-C-sharp.aspxhttp://aspnet.4guysfromrolla.com/articles/053106-1.aspxhttp://aspnet.4guy...

SQL,Make Multiple values for multiple columns into one column separated by comma
Hi, I'm getting the result as this Name       Class       Subject------------------------------------------Anju         10             MathsAnju         10             Physics How to write the query to get the result like this Name       Class       Subjects------------...

Refering to a table with one column and one row
Hi all, I wish to set a reference date for age calculations in various places. My thought was to have a table with one column and one row, with the date, say 2007/04/01(yyyy/mm/dd) Then I would like to calculate ages as on that date with a computed column. How does one link the tables? I tried it with a cross join on a simple test table. I suppose a cross join is OK as there is only one row? John, In this case, the cross join is fine. Another possibility would be to use a subselec in the places where you want to use the valuet, i. e. to use (select "date" fro...

Move data from one column to another column and row
I need some help converting a table which has one row for each year.  Displaying the data so the each row will contain all of the information for the title and all of the years displayed in separate columns. I have a SQL Querry which returns the data looking like this (the querry is grouping by Title and by year, then adding up the quantity of rows): Basic Information needed for Life Insurance 25 2005 Basic Information needed for Life Insurance 45 2006 Ea Chairmans Conference 10 2005 EA Chairmans Conference Press Release 33 2005 EA Chairmans Conferenc...

Concatenate multiple rows into one column?
Newbie question here. I have two tables that have a one to many relationship. I want to create a query that takes the info from the child table (possibly multiple rows) and concatenates it into a single column in the parent table. The tables are:TableParent (ASSIGNNUM (PK), DESC, STARTDATE)TableChild (ASSIGNNUM (FK), EMPLOYEENUM)There could be multiple employees for each assignment. Sample data:TableParent1....First Assignment....05/01/20082....Second Assignment...05/03/20083....Third Assignment....05/07/2008TableChild1....553422....334562....523433....352253....451213....11553I would like t...

Display multiple columns in Gridview from One Column of SQL table
Hi I have a table in sql with 2 columns and I want to display that in multiple columns in Gridview. SQL Table No.            Type 1               A1 1               B2 2              A2 2               B2 3        &...

(DATAGRID)How to put 1st column in one row and the rest in the row below?
For example, I want to display a FIELD1,FIELD2,FIELD3,FIELD4 from a table in a datagrid this way: ---------------------------------------------- FIELD1("colspanning it") ---------------------------------------------- FIELD2 | FIELD3 | FIELD4 ---------------------------------------------- FIELD1("colspanning it") ---------------------------------------------- FIELD2 | FIELD3 | FIELD4...

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

Binding Multiple Rows to One DataGrid Column
Hi, I need to display the content of database columns a, b, c, d in one DataGrid row by inserting break line between them. Columns b, c and d are somethimes empty and I dont want to insert break line if they are empty. Can anyone please help me how to do this... I have something like this so far..... Dim Col1 As New BoundColumn Col1.DataField = ("a") & ("b") ---> it doesnt work... MyDataGrid.Columns.Add(Col1)   Thanks  Zumbul wrote: I need to display the content of database columns a, b, c, d in one DataGrid row by inserting break line between them Can...

Displaying two rows of database column data in one row of datagrid
Hello, I am trying to display two rows of an access column on one row of datagrid. Is that possible? Example of my intended datagrid: courseNo of course1courseNo of course2 courseNo of course3courseNo of course4 here's the HTML view of my aspx file: <Asp:datagrid id="DataGrid1" style="Z-INDEX: 101; LEFT: 104px; POSITION: absolute; TOP: 40px"    runat="server" AutoGenerateColumns="False" Height="320px" Width="488px">    <Columns>     <asp:TemplateColumn>      &...

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

Web resources about - SQL: mutiple rows one column to multiple columns one row - sybase.infomaker.general

Multiple sequence alignment - Wikipedia, the free encyclopedia
A multiple sequence alignment (MSA) is a sequence alignment of three or more biological sequences , generally protein , DNA , or RNA . In many ...

Man stabbed multiple times in street fight
A 23-year-old man suffers multiple stab wounds in a wild street brawl involving up to 100 people.

Man charged with murder of 21yo found on road in Tewantin with multiple stab wounds
Police charge a man with the murder of a 21-year-old man found lying on a road with multiple stab wounds to his chest.

Multiple shots fired at Atlanta mall - abc13.com
Multiple shots were fired Saturday evening in a suburban Atlanta mall, prompting a frantic rush by shoppers to evacuate.

Rockies trade rumors: Carlos Gonzalez could fit on multiple teams
The oft-injured slugger has two years left on his deal. There are three types of teams in the NL West. Subdividing five teams into three categories ...

Gov. Jerry Brown pardoned Robert Downey Jr. for his multiple crimes in the ’90s
I’m old enough to remember when Robert Downey Jr. was an utter mess. Back in the ‘90s, I lost count of how many times he was arrested, how many ...

Gas tank explodes at plant in Nigeria; multiple fatalities reported
CNN Gas tank explodes at plant in Nigeria; multiple fatalities reported CNN Kano, Nigeria (CNN) An industrial gas tank exploded in southern ...

Draymond explains how multiple Warriors lead in their own way
... Search 42° Navigation Home Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Draymond explains how multiple ...

Big Pharma bro arrested by federal agents in overnight raid, faces multiple investigations
Big Pharma bro Martin Shkreli gained international notoriety when his company bought the rights to Daraprim—a drug used to treat parasitic infections, ...

Showtime Previewing ‘Shameless’ & ‘Billions’ Premieres On Multiple Platforms
Demonstrating the increasing importance of the network’s original programming in bringing in new customers, Showtime will be providing subscribers ...

Resources last updated: 12/29/2015 4:48:31 AM