How to use pivot sql

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I have a table with following data..

A	B	C	D
BR00002	281	2.22	12-01-2013
BR00002	277	1.65	12-02-2013
BR00002	267	1.8	12-03-2013

I Want to get the following result..

X	y	12-01-2013	12-02-2013	12-03-2013
BR00002	825	2.22	         1.65	          1.8

Please help, How to get the required..

Posted 24-Apr-14 2:14am

Member 10715787

Add a Solution

Comments

[no name] 24-Apr-14 8:24am

You already know the answer, write a pivot query.

2 solutions

Solution 2

It is solved now.
I have taken a temp table to insert SUM(B) with group by A and join the both statement..
It has solved my problem..

Thanks for you guys to support and teach me about PIVOT. :)

Posted 24-Apr-14 6:11am

Member 10715787

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)

Peter Leow · Accepted Answer · 2014-04-24T03:03:00

Solution 1

Learn to do it yourself:
Simple Way To Use Pivot In SQL Query[^]
++++++++++[round 2]++++++++++
As per request, try this:

SQL

SELECT  A as  X, Y, [12-01-2013],[12-02-2013],[12-03-2013]
FROM
(SELECT A,
    (SELECT SUM(B) from table1 t2 where t2.a=t1.a group by t2.a) as Y,
 C, D  FROM table1 t1) AS src
PIVOT
(
  SUM(C)  FOR D IN ([12-01-2013],[12-02-2013],[12-03-2013])
) as  pvt

Posted 24-Apr-14 3:03am

Peter Leow

Updated 24-Apr-14 5:08am

v2

Comments

Member 10715787 24-Apr-14 9:37am

Hi I am able to find the below result using pivot..
X 12-01-2013 12-02-2013 12-03-2013
BR00002 2.22 1.65 1.8

But i could not be able to Sum(Y) in the query.
I am using the following query to find the above result-
Select * FROM
(
SELECT A as X,SUM(C)as N,D group by X,D
)as s
PIVOT
(
SUM(C)
FOR D IN([12-01-2013],[12-02-2013],[12-03-2013])
) as pvt

Please help me to get the SUM of column(B) in same SP..
Please help..

Peter Leow 24-Apr-14 11:09am

added in solution 1.