How to pivot multiple columns in multiple rows in sql?

Question

I have a table in sql with following data,

Bus_Type    Invalid_Counts    Valid_Counts    Percentage
---------------------------------------------------------
 General          721               1339            53.85
 Inquiry          243               9301             2.61
  Firm            553               1207            45.82

I want to use pivot and get the data in the following format,

   Cols          General    Inquiry    Firm    
-------------------------------------------
Invalid_Counts    721        243       553
Valid_Counts     1339       9301      1207
Percentage       53.85      2.61      45.82

This is my query for pivot,

select * from 
(select Bus_Type, Invalid_Counts, Valid_Counts, Percentage from #temp) a 
pivot(max(Invalid_Counts) for Bus_Type in (General, Inquiry, Firm)) pv

which gives me following result and it is not what I want,

Valid_Counts    Percentage    Firm    General    Inquiry
-----------------------------------------------------------
 1339            53.85         NULL     243        NULL
 9301             2.61         NULL     NULL       553
 1207            45.82         721      NULL       NULL

Any ideas will be appreciated and helpful.

Thanks.

M.Ali · Accepted Answer · 2015-01-29 20:18:42Z

Test Data

DECLARE @TABLE TABLE (Bus_Type VARCHAR(100), Invalid_Counts INT
,Valid_Counts INT,Percentage DECIMAL(10,2))
INSERT INTO @TABLE VALUES 
('General',  721   ,  1339  ,  53.85  ),
('Inquiry',  243   ,  9301  ,  2.61   ),
('Firm'   ,  553   ,  1207  ,  45.82  )

Query

;WITH CTE AS
(
SELECT Bus_Type
      ,CAST(Invalid_Counts AS DECIMAL(10,2)) AS Invalid_Counts
      ,CAST(Valid_Counts   AS DECIMAL(10,2)) AS Valid_Counts 
      ,CAST(Percentage     AS DECIMAL(10,2)) AS Percentage  
FROM @TABLE
)
SELECT * FROM CTE
 UNPIVOT ( Vals FOR Cols IN (Invalid_Counts, Valid_Counts , Percentage))up
 PIVOT (SUM(Vals)  FOR Bus_Type IN (General, Inquiry , Firm))P

Result

╔════════════════╦═════════╦═════════╦═════════╗
║      Cols      ║ General ║ Inquiry ║  Firm   ║
╠════════════════╬═════════╬═════════╬═════════╣
║ Invalid_Counts ║ 721.00  ║ 243.00  ║ 553.00  ║
║ Percentage     ║ 53.85   ║ 2.61    ║ 45.82   ║
║ Valid_Counts   ║ 1339.00 ║ 9301.00 ║ 1207.00 ║
╚════════════════╩═════════╩═════════╩═════════╝

Collectives™ on Stack Overflow

How to pivot multiple columns in multiple rows in sql?

1 Answer 1

Test Data

Query

Result

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Test Data

Query

Result

Comments

Related