CODEWITHSUNDEEP
sql-server-2008
Puskar
Puskar

Reputation: 97

rows to column and vice-versa conversion

By running the following query I will get the result below. I want to convert this result into rows and whatever is there as row over ther those I want to convert into columns.

I want that project_level and account_level to come in rows and the remaining in rows goes to column.

SELECT S.Description,T.Project_Level,S.Account_Level FROM
(           
   SELECT DISTINCT SM.Description, COUNT(VA.FK_Status_ID) AS Project_Level,SM.Seq
                  From DeliveryPlatform_APPS.VA.DP_VA_Item_Status_Master SM
                  LEFT OUTER JOIN DeliveryPlatform_APPS.VA.DP_VA_Items VA ON SM.Status_ID = VA.FK_Status_ID 
                  AND VA.FK_DP_Entity_Type_ID = 1 AND FK_DP_Entities=671
                  GROUP BY SM.Description,SM.Seq,FK_Status_ID           
) AS T            
INNER JOIN        
(           
  SELECT DISTINCT SM.Description, COUNT(VA.FK_Status_ID) AS Account_Level,SM.Seq
                  From DeliveryPlatform_APPS.VA.DP_VA_Item_Status_Master SM
                  LEFT OUTER JOIN DeliveryPlatform_APPS.VA.DP_VA_Items VA ON SM.Status_ID = VA.FK_Status_ID 
                  AND VA.FK_DP_Entity_Type_ID = 1 AND FK_DP_Entities=671
                  GROUP BY SM.Description,SM.Seq,FK_Status_ID           
) AS S 
ON S.Description=T.Description 
--ORDER BY Seq 

 Description      Project_Level      Account_Level

 Accepted          0                 0
 Closed            0                 0
 Delivered         0                 0
 Dropped           0                 0
 Open              0                 0
 Parked            0                 0
 Shortlisted       0                 0
 Work In Progress  0                 0

Upvotes: 2

Views: 270

Answers (1)

dixpac
dixpac

Reputation: 533

You should check SQL Server PIVOT and UNPIVOT techniques to accomplish your intent:

http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Upvotes: 1

Related Questions