convert row to column
-
Hi to all expert,
i need help to convert the row into a column
here are the example:
the original table
into the following table
-
Kokfai,
You'll find that most folks like to test their answers before posting them. Instead of posting the data as a graphic, please see the link in my signature for how to post an example table and readily consumable data for future posts.
In the meantime...
[font="Courier New"] SELECT UserID, UserName,
SUM(CASE WHEN CallDestType = 'MobilLoc' THEN Amount ELSE 0 END) AS [Sum(MobilLoc)],
SUM(CASE WHEN CallDestType = 'Mobil_Out' THEN Amount ELSE 0 END) AS [Sum(Mobil_Out)],
SUM(Amount) AS Total
FROM yourtable
GROUP BY UserID, UserName
ORDER BY UserName
[/font]
Yeah... I threw in an extra column that seemed the logical thing to do. Again, this is untested code because I don't have your data. Do read the article at the link in my signature.
Also, it is customary to post the code that you've tried to solve a problem with. This keeps us from doing homework, tests, and interview questions for folks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi Jeff,
Sorry for my stupidity for not reading the rules and regulation 1st b4 posting. I'll take a look at it when i have time. As you can see this is my 1st time posting here, i'll try not to repeat my mistake in the future.
Anyway, thanx for your help i able to query out the result just as i wanted with a little changes.
i highlighted the changes 🙂
SELECT UserID, UserName, SUM(CASE WHEN CallDestType = 'MobileLoc' THEN Amount ELSE 0 END) AS [Sum(MobileLoc)],
SUM(CASE WHEN CallDestType = 'Mobile_Out' THEN Amount ELSE 0 END) AS [Sum(Mobile_Out)]
FROM extbillingdetails4
GROUP BY UserID, UserName
ORDER BY UserName
-
kokfai.lai (6/8/2009)
Hi Jeff,Sorry for my stupidity for not reading the rules and regulation 1st b4 posting. I'll take a look at it when i have time. As you can see this is my 1st time posting here, i'll try not to repeat my mistake in the future.
Anyway, thanx for your help i able to query out the result just as i wanted with a little changes.
i highlighted the changes 🙂
SELECT UserID, UserName, SUM(CASE WHEN CallDestType = 'MobileLoc' THEN Amount ELSE 0 END) AS [Sum(MobileLoc)],
SUM(CASE WHEN CallDestType = 'Mobile_Out' THEN Amount ELSE 0 END) AS [Sum(Mobile_Out)]
FROM extbillingdetails4
GROUP BY UserID, UserName
ORDER BY UserName
I appreciate your humility, but the "rules" aren't posted. As a newbie to the forum, you had no idea how to post. That's why I referred you to the article I wrote to help out new folks.
Sorry about the typo's on my part. That's why I like to test code but don't have the time to enter someone else's test data. 😉 I'm glad you figured it out. Lot's of folks would simply go, "ACK! THERE'S AN ERROR! PLEASE HELP ME FIX IT!".
You did really good for a first post. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply