×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to make data from a column into multiple rows?

How to make data from a column into multiple rows?

How to make data from a column into multiple rows?

(OP)
hi, i come into a situation like this. My table is something similiar to below

table
id Name Status Datetime
1 Jack success 2006-06-06
2 Jack fail 2006-06-06
3 Jack success 2006-06-06
4 Jack success 2006-06-06
5 Mal fail 2006-06-06
6 Mal fail 2006-06-06

And i need to get the result from a specified column "Status", and the result will be something like below, which will COUNT() the no. of record. The success, fail is something calculated from "Status"

Result
Name Sucess Fail
Jack 3 1
Mal 0 2

Anybody have idea bout it?

Thanks

RE: How to make data from a column into multiple rows?

What tools do you have available? Access, Crystal and Fox could all do a CrossTab on that

Geoff Franklin
www.alvechurchdata.co.uk

RE: How to make data from a column into multiple rows?

(OP)
Oh, i m using mysql, will it be possible?

RE: How to make data from a column into multiple rows?

Quote:

Oh, i m using mysql, will it be possible

I've never used mySQL so I'm sorry I can't help. You might do better in a specific mySQL forum.

Geoff Franklin
www.alvechurchdata.co.uk

RE: How to make data from a column into multiple rows?

I am thinking you use count on the two fields and use order by group by on the name.  A lot depends on how many records there are.  I could do this in Excel as long as the maximum number of rows is not violated.  You can use a date range to limit the records.  Hopefully there are not too many records.  

I use Unidata DB and use a Uniquerry querry tool and it exports directly to Excel.  I dont know if MySQL can do that or not.  Does it come with a Querry type tool?  Basically you build a querry.  I dont mess with MySQL at all.

With other products you could build a view and use ODBC Drives to import the view based on a querry into an Excel Spreadsheet (or maybe some other product).  Excel Prof has an Auto Filter Setting and some other tools like Pivot Table Reports.  Lets you put the name rows on the vertical and the fail success fields accross the top.

If you do not like my post feel free to point out your opinion or my errors.

RE: How to make data from a column into multiple rows?


I would create a temporary table to do this:

create table #temp
   (
    id      int,
    Name    varchar(30),
    success int,
    fail    int
   )
insert into #temp
  select id,name,count(*),min(0)
   from MyTable
   where status = 'success'
   group by id,name
insert into #temp
  select id,name,min(0),count(*)
   from MyTable
   where status = 'fail'
   group by id,name
select Name,sum(success) as Success,sum(fail) as Fail
  from #temp
  group by name


RE: How to make data from a column into multiple rows?

I think MySQL supports sub queries so look at something like

CODE

Select DISTINCT Name,

       (Select Count(*) From tbl X
        Where  X.Name = A.Name AND Status = 'Success') As Success,

       (Select Count(*) From tbl X
        Where  X.Name = A.Name AND Status = 'Fail') As Fail

From tbl A

No! No! You're not thinking ... you're only being logical.  
- Neils Bohr

RE: How to make data from a column into multiple rows?

CODE

select name
     , sum(case when status = 'success'
                then 1
                else 0 end) as success
     , sum(case when status = 'fail'
                then 1
                else 0 end) as fail
  from daTable
group
    by name

r937.com | rudy.ca

RE: How to make data from a column into multiple rows?

You can use Crosstab to do similar to what you want, using the Status as the pivot:

TRANSFORM Count(YourTable.id) AS [The Value]
SELECT YourTable.Name,  Count(YourTable.id) AS [Count status]
FROM YourTable
GROUP BY YourTable.Name
PIVOT YourTable.status;
--Jim

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close