MS SQL Query to Display actual data as columns

Question

2.00/5 (1 vote)

See more:

Hello,

Please assist me with this issue.

I have a table that displays the data as is shown below:

Name ID Amount Date
Peter A $10 12/02/2013
Peter B $15 12/02/2013
Peter C $12 12/02/2013
Peter A $12 12/02/2013
Mary B $18 12/02/2013
Mary C $12 12/02/2013
James A $5 12/02/2013

What I want to get from the above is as shown below. So help me with the query please.

Name A B C
Peter $22 $15 $12
Mary $18 $12
James $5

Please assist me with the query for the above.

Thanks in advance.

Posted 4-Nov-13 10:58am

ekipongi

Add a Solution

Comments

ZurdoDev 4-Nov-13 17:05pm

Describe in English how you want it.

ekipongi 4-Nov-13 17:12pm

I want a column of distinct name, and the actual data in the ID which are A, B and C to be columns and the Amounts to be the data for the ID columns. For clearity, in my initial table as is shown above there are 4 columns, Name, ID (which contains A, B & C), Amount and the Date. I just want to display distinct names with 3 columns as A,B & C. the amount should be under associated ID as is shown in the 2nd table above.

joshrduncan2012 4-Nov-13 17:13pm

Help you with what? What have you tried so far to accomplish this? Where are you stuck?

ekipongi 4-Nov-13 19:48pm

For the benefit of others below is the answer. After a bit of reading on Pivot in ms sql got the answer that I wanted.

select *
from
(
select Name, ID, Amount
from Table1
) src
pivot
(
sum(Amount)
for ID in ([A], [B], [C])
) piv;

prabhatsp 27-Nov-13 5:02am

I have the sample problem ,bt i dont know the static column name ,any suggestions pls............!

ekipongi 27-Nov-13 16:33pm

What do you mean by static column name? Explain further by providing dummy data and what you'd want to achieve.

prabhatsp 5-Dec-13 1:30am

static column names are [a],[b],[c], Anyway i got the answer.Thank u for ur reply

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Answer 1 · 2013-11-04T20:41:00

Solution 1

Use PIVOT - UNPIVOT

Using PIVOT and UNPIVOT[^]

Pivoting data in SQL Server[^](Alternate ways)

Posted 4-Nov-13 20:41pm

thatraja