How to use Pivot in SQL

Question

0.00/5 (No votes)

See more:

Here is my Database structure.

  source      destination         vehicle              Rate_New
-------------------------------------------------- ----------------------------
      A        B           Small                         1400
      A        B              Regular                        1750
      A        B          Luxury                         2600
      C        D          Regular                        3500
      C    D           Luxury                        5600

I want to display data like this following :

source   destination      Small                  Regular                Luxury
-------------------------------------------------- ------------------------------
   A       B            1400                   1750                   2600
   C           D            NULL                   3500                   5600

How to do this?
Thanks in advance

Posted 28-May-12 23:33pm

sahabiswarup

Updated 28-May-12 23:37pm

Maciej Los

v2

Add a Solution

5 solutions

Solution 6

i give you a simple example as i understand your query.

SQL

SELECT [source], [destination], [Small], [Regular], [Luxury]
FROM
(SELECT *
FROM YourTable) p
PIVOT
(
Sum(SUM(Rate_new)
FOR vehicle IN
( ([Small],[Regular],[Luxury] )
) AS pvt
ORDER BY source

Posted 29-May-12 19:46pm

Prosan

Comments

Maciej Los 30-May-12 2:59am

Why did you copy my answer?

Solution 2

Converting two column as row from a table[^]

Posted 28-May-12 23:45pm

S.P.Tiwari

Solution 3

The correct SQL script would be:

SQL

SELECT [source], [destination], [Small], [Regular], [Luxury]
FROM 
(SELECT [source], destination, vehicle, rate_new FROM TestTable) AS DT
PIVOT(SUM(Rate_new) FOR vehicle IN ([Small],[Regular],[Luxury])) AS PT
ORDER BY [source]

Posted 28-May-12 23:57pm

db7uk

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)

Maciej Los · Accepted Answer · 2012-05-28T23:41:00

Solution 1

Like this:

SQL

SELECT [source], [destination], [Small], [Regular], [Luxury]
FROM (SELECT * FROM YourTable) AS DT
PIVOT(SUM(Rate_new) FOR [vehicle] IN ([Small],[Regular],[Luxury])) AS PT
ORDER BY [source]

More examples: http://www.codeproject.com/search.aspx?q=author%3A+losmac+AND+pivot&x=0&y=0&sbo=qa&usfc=false[^]

Posted 28-May-12 23:41pm

Maciej Los

Updated 29-May-12 8:36am

v3

Comments

Sandeep Mewara 29-May-12 14:14pm

My 5!

Maciej Los 29-May-12 14:35pm

Thank you, Sandeep ;)

sahabiswarup · Accepted Answer · 2012-05-28T23:57:00

Solution 4

SQL

SELECT source,destination, [Small Car],[Regular Car],[Luxury Car]
FROM
    (
        SELECT source, destination, vehicle, Rate_New
        FROM seasonal_pickupdroptariff
    ) up
PIVOT (Min(Rate_New) FOR vehicle IN ([Small Car],[Regular Car],[Luxury Car])) AS pvt
order by source

Posted 28-May-12 23:57pm

sahabiswarup

Comments

Maciej Los 30-May-12 2:59am

Why did you copy my answer?
If you have found an answer, it would be good to vote each helpful answer and mark it as "solved".

sahabiswarup 8-Jun-12 6:18am

This is the final query losmac; i just modified your query and get my result.