Improve company productivity with a Business Account.Sign Up

x
?
Solved

View Multiple record in one row in SQL

Posted on 2007-11-21
5
Medium Priority
?
159 Views
Last Modified: 2010-07-27
Hi, i have view in SQL the return multiple row with same name but defrent action:

Name       Action       Total
------------------------------
user1      close            4
user1      resolved       3
user2      close            5
user2      resolved       2
user3      pending        1

i want my result to be:

Name      close      resolved       pending
----------------------------------------------------------
user1        4               3                    0
user2        5               2                    0
user3        0               0                    1

is there any easy way using SQL statment to do this insted of writting a big code to get the resultin .Net using c#.
Thank you
0
Comment
Question by:AZZA-KHAMEES
  • 3
5 Comments
 
LVL 15

Expert Comment

by:spprivate
ID: 20332889
Use this stored procedure
http://p2p.wrox.com/topic.asp?TOPIC_ID=25952
and execute the proc like this

EXEC sp_crossTab  'urTable', 'user',NULL', NULL, 'Action','Total'

HTH
0
 
LVL 1

Author Comment

by:AZZA-KHAMEES
ID: 20332945
I not that goo in stored procedure that why i didnt understand whats writtin in this link
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20332997
how many rows are there in total, and how many different types of action can you have? if there's not many action types, we can create a one-off solution
0
 
LVL 25

Expert Comment

by:imitchie
ID: 20333017
select [Name],
 sum(case when Action = 'close' then Total else 0 end) as [Close],
 sum(case when Action = 'resolved' then Total else 0 end) as [Action],
 sum(case when Action = 'pending' then Total else 0 end) as [Pending]
from act
group by [Name]
0
 
LVL 25

Accepted Solution

by:
imitchie earned 2000 total points
ID: 20333018
if you have more types of actions, just add more cases to it
hope this helps
0

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question