Click here to Skip to main content
15,303,572 members
Please Sign up or sign in to vote.
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
Updated 28-May-12 23:37pm
v2

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[^]
   
v3
Comments
Sandeep Mewara 29-May-12 14:14pm
   
My 5!
Maciej Los 29-May-12 14:35pm
   
Thank you, Sandeep ;)
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
   
Comments
Maciej Los 30-May-12 2:59am
   
Why did you copy my answer?
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]
   
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
   
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900