tow or more rows in a single row


I have one senario.

Create table Client_Table
ID int,
SUB int,
Name varchar(30),
Status varchar(10)

insert into Client_Table
select 9001,4,'Gail','Active'
Select 9001,1,'Mark','Active'
union all
select 9001,2,'Will','InActive'
union all
select 9001,4,'Gail','Active'
union all
select 9002,1,'chals','Active'
union all
select 9003,1,'ken','Active'
union all
select 9003,2,'dave','Active'
union all
select 9004,1,'peter','Active'
union all
select 9004,2,'Jack','InActive'

each ID can have any no of clients,

i need to select only the first two Active clients for each
ID in a single row

Example Output

ID Name1 Name2
9001 Mark steve
9002 chals
9003 Ken dave
9004 Peter
8/12/2008 1:02:40 PM
sybase.ase.performance+tuning 2395 articles. 0 followers. Follow

1 Replies

Similar Articles

[PageSpeed] 45

One way may be something like:

select ID, firstone = MIN(SUB)
into #t1
from Client_Table
where status = 'Active'
group by ID
create unique clustered index idx1 on #t1 (ID, firstone)
select ID, secondone = MIN(SUB)
into #t2
from Client_Table a
where status = 'Active'
and SUB != (select firstone from #t1 b where b.ID = a.ID)
group by ID
select distinct a.ID, Name1 = c.Name, Name2 = d.Name
from #t1 a, #t2 b, Client_Table c, Client_table d
where a.ID *= b.ID
and a.ID *= c.ID
and a.firstone *= c.SUB
and b.ID *= d.ID
and b.secondone *= d.SUB

Bob Densmore

On 12 Aug 2008 06:02:40 -0700, Karthikeyan.M wrote:

>I have one senario.
>Create table Client_Table
>ID int,
>SUB int,
>Name varchar(30),
>Status varchar(10)
>insert into Client_Table
>select 9001,4,'Gail','Active'
>Select 9001,1,'Mark','Active'
>union all
>select 9001,2,'Will','InActive'
>union all
>select 9001,4,'Gail','Active'
>union all
>select 9002,1,'chals','Active'
>union all
>select 9003,1,'ken','Active'
>union all
>select 9003,2,'dave','Active'
>union all
>select 9004,1,'peter','Active'
>union all
>select 9004,2,'Jack','InActive'
>each ID can have any no of clients,
>i need to select only the first two Active clients for each
>ID in a single row
>Example Output
>ID Name1 Name2
>9001 Mark steve
>9002 chals
>9003 Ken dave
>9004 Peter

8/15/2008 8:19:55 PM

Similar Artilces:

Embedding rows into a single row?
Dear All,I need to display a page that shows a data grid, that’s fine. The slight trouble is that each item (a row) on the grid may have 1 or many action items associated with it.So what I’d like to do is display the grid and the rows as normal but embed the 1 or many items in the last column of the grid in each row as needed. Just like splitting some cells into many cells horizontally in an HTML table.How can I achieve this? Any ideas? Are you familiar with using Template columns with DataGrid?  You can simply add a Template column that contains nearly anything you want in terms...

First Row, Next Row, Prior Row, Last Row
I have a SQL Anywhere 7.0 Database, have a table Product_detail which contains Column Name Datatype width Null Default batch_id char(10) No None prod_id Integer No None employee_id Integer No None quantity Integer No None price Numeric (14, 2) No None Its primary key is batch_id I have a Freeform Datawindow with this table I want to provide the first row, next row, prior row and last row Ho...

Inserting all rows of a datatable into Oracle Table in a single shot, not row by row using C#
Hi All... I have imported the data of CSV File into Datable using C#. Now i need to insert the content of DataTable to the table in Oracle Database in a single shot, without looping through the rows of the datatable. Is this Possible? Please help me out by providing the sample code. Below is sample code for Importing the CSV Data to Dataset string connString =string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Text;",Path.GetDirectoryName(filename)); string cmdString =string.Format("SELECT * FROM {0}", Path.G...

Merge two rows in to a single row
date   and tim                    EN    Time          Day       InTime     OutTime    Date        2009-01-23 07:49:12.000    195    07:49:12    Friday    07:49:12    NULL    23/01/20092009-01-23 11:32:04.000    196    11:32:04    Friday    NULL&nbs...

Showing mulitple rows to single row
Hi Friends,Need a help showing multiple rows to single row of the same ID or Name.Below is the example I am showing for your better understandingActual table display ID    Goal  Total24    4        1    24    100    9024    104    93    I need output something like this ID    Goal1   Total1 Goal2   Total2   Goal3    Total3 24      4    ...

HTML table row
Hi, I am using 3.5.Below is the code from markup area, my requirement is - I placed 2 options buttons on the page. when user click on first button the first row should be (2nd row shoud be hidden here)visible, if select 2nd button the 2nd row should be visible in the same place as the first row (1st row shoud be hidden here)- something similar we do in windows programming,IS POSSIBLE<asp:TableRow ID="TableRow34" Height=30 runat="server" style="font-size:11px;color:#0033FF"> <asp:TableCell ID="TableCell74" columnSpan=4 HorizontalAlign =...

how to display multiple rows of a table in single row
DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp2 + ',', '') + desigFROM emp SELECT eid=@emp1,ename = @emp,desig=@emp2    anitha123:DECLARE @emp VARCHAR(1024) declare @emp1 varchar(1024)declare @emp2 varchar(1024)SELECT @emp1 = COALESCE(@emp1 + ',', '') + cast(eid as varchar(10)),@emp = COALESCE(@emp + ',', '') + ename ,@emp2 = COALESCE(@emp...

convert multiple row values into single row
Hi All, I have to convert multiple row values into single row with multiple columns, can anybody help me in this regard. Thanks in advance. can u provide some information. i didn't get ur  requirement properly. Clarity is needed.Thank uBabaPlease remember to click "Mark as Answer" on this post if it helped you. Hi,   Maybe a Pivot Query is what you're looking for? Greg...

single-row subquery returns more than one row
 I want a subquery  to return more than  one value then  use those values to get more values from  the other table. But  I  am receiving the errors saying ORA-01427: single-row subquery returns more than one row. How can I avoid this and achieve  want I want? My sql and Csharp is below:dbcon.Open();            string strreg = "SELECT Amount AS TotalAmount FROM Payment WHERE studentno=(SELECT tempcode FROM  specialstudent WHERE custid='"+ AreaNO +"')";    &...

single row query rteturn more than one row

adding a blank header row /row above the header row
is it possible to add a blank row above the header row in gridview for adding some links and buttons the links will respond to thae data in the grid and the buttons are for login / regisration etc....  if yes how it can be done or how do i achieve it. Its better that u create a table with a single row with columns matching the GridView columns. Set the table width same as the GridView width. This will make the GridView Look as if there is a row above its header.Please do not forget to click Mark As Answer on the post that helped you. Regards,Iam Srikanth Reddy....

How concate n rows value into single row value
Daer Sir, I have list(5 rows) of name in a datawindow I want to concate all rows into sinle row regards Indus ---== Posted via the PFCGuide Web Newsreader ==--- Are you trying to display it in one row in the same DW which retrieves the five separate rows ? Do you need those five rows for any processing other than concatenating them ? What functionality are you trying to provide ? Understanding that may help us provide other options. There are several approaches you could take, primarily revolving around reading each va...

accessing row by row
first of all sorry if i posted this in a wrong place but i'm new to powerbuilder and i have to develop a program quite quickly ( i only worked with acces until now) i have been provided a excel spreadsheet that contains all coloms and rows from all tables wich i have to insert in a database. The difficulty is that i can't use a datapipeline because i need to use a string function to check in wich table i have to put it: TABLENAME_COLUMNNAME -> i thought about using the string function to find out the right column in the right database. however i don't know how you ...

retrieving from row # to row #.
I have over 750 000 rows to retrieve from a table using a datawindow, but my system can only handle approx 30 000 rows and I would like to start from the end of the table. Is it possible to set the row numbers to retrieve? For example, start the retrieve from row 750 000 and stop at row 720 000. There must be a way. thanks, Vern what backend are you using? <Vern> wrote in message news:6BA241541AAD7F2F0063357385256C40.0063358685256C40@webforums... > I have over 750 000 rows to retrieve from a table using a datawindow, but > my system can only handle approx 30 ...

Web resources about - tow or more rows in a single row - sybase.ase.performance+tuning

List of Highland Single Malts - Wikipedia, the free encyclopedia
Highland Single Malts are single malt Scotch whiskies produced in the Highland region of Scotland . This categorization includes the whiskies ...

10 science-backed reasons being single is good for your health
Facing the holidays without a significant other? Relax, there's an upside.

Japanese train service 'keeps running daily service for a single passenger'
Imagine you were the sole passenger of a daily train service, which ran twice a day, just for you. For one Japanese school girl it might just ...

HTC and Under Armor’s HealthBox combines smart scales, fitness and heart-rate trackers in a single $400 ...
In an effort to get customers entirely absorbed into their ecoystem in one fell swoop, HTC and Under Armor have today announced HealthBox. Although ...

Israel's best-performing tech stock hasn't sold a single product
An Israeli upstart which is trying to sell investors on a future in which physical objects materialise with the press of a buttonis attracting ...

The Dandy Warhols release new single "You Are Kiling Me" (listen), new album on Dine Alone later this ...
... Dine Alone records and will release their first album in four years later in 2016. While we wait for more details, they've released a new single, ...

Panasonic Develops Single Cable and Connector Solution for Transmission of Full-Spec 8K Video Signals ...
Panasonic Corporation today announced that it has developed single cable and connector solution that enable the transmission of uncompressed ...

Madonna: Society does not ‘encourage strong independent single working moms!’
Its possible to be an entertainer and a good mother!!! Too bad we don't live in a society where many encourage strong independent single working ...

Autonomous Single Passenger Electric Drone Unveiled At CES
... everything we know about getting from here to there on its head. Meet the mother of all disruptors — the EHang 184. [&hellip Autonomous Single ...

WATCH: Apache Gunner Wipes Out Six Terrorists With A Single, Precision Strike
WATCH: Apache Gunner Wipes Out Six Terrorists With A Single, Precision Strike

Resources last updated: 1/11/2016 1:00:12 PM