• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 157
  • Last Modified:

View Multiple record in one row in SQL

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
AZZA-KHAMEES
Asked:
AZZA-KHAMEES
  • 3
1 Solution
 
spprivateCommented:
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
 
AZZA-KHAMEESAuthor Commented:
I not that goo in stored procedure that why i didnt understand whats writtin in this link
0
 
imitchieCommented:
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
 
imitchieCommented:
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
 
imitchieCommented:
if you have more types of actions, just add more cases to it
hope this helps
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now