Select columns from another select as rows for each column

Question

Ok, I know that might be difficult to understand, feel free to edit it so it make more sense. Basically I want to rotate my results. I would like to select * from (select col1, col2 from table) tmp_table, but have all the columns from tmp_table result as rows. Here is the select that will be tmp_table:

select
txt_description_1   label_1,
txt_description_2   label_2,
txt_description_3   label_3,
txt_description_4   label_4,
txt_description_5   label_5,
txt_description_6   label_6,
txt_description_7   label_7,
txt_description_8   label_8,
txt_description_9   label_9,
txt_description_10  label_10,
txt_description_11  label_11,
txt_description_12  label_12,
txt_description_13  label_13,
txt_description_14  label_14,
txt_description_15  label_15,
txt_description_16  label_16,
txt_description_17  label_17,
txt_description_18  label_18,
txt_description_19  label_19,
txt_description_20  label_20,
txt_description_21  label_21,
txt_description_22  label_22,
txt_description_23  label_23,
txt_description_24  label_24,
txt_description_25  label_25,
txt_description_26  label_26,
txt_description_27  label_27,
txt_description_28  label_28,
txt_description_29  label_29,
txt_description_30  label_30,
txt_description_31  label_31,
txt_description_32  label_32,
txt_description_33  label_33,
txt_description_34  label_34,
txt_description_35  label_35,
txt_description_36  label_36,
txt_description_37  label_37,
txt_description_38  label_38,
txt_description_39  label_39,
txt_description_40  label_40,
txt_description_41  label_41,
txt_description_42  label_42,
txt_description_43  label_43,
txt_description_44  label_44,
txt_description_45  label_45,
txt_description_46  label_46,
txt_description_47  label_47,
txt_description_48  label_48,
txt_description_49  label_49,
txt_description_50  label_50,
txt_description_51  label_51,
txt_description_52  label_52,
txt_description_53  label_53,
txt_description_54  label_54,
txt_description_55  label_55,
txt_description_56  label_56,
txt_description_57  label_57,
txt_description_58  label_58,
txt_description_59  label_59,
txt_description_60  label_60,
txt_info2   label_info2
from ngkbm_template_data_sets_
where practice_id = '0001'
and txt_data_set = @dataSet
and chk_label_values = 1

So lets say that output looks like this:

Protocol    Test    Dx Code Dx  Test Code   Interval    Start Age   Stop Age    Gender  NULL    NULL    Test Code (Medicare)    Test Code (Medicare, Hish Risk) Interval (High Risk)    Start Age (High Risk)   Stop Age (High Risk)    NULL    NULL    Seq # for Series    Pre-Requisite Exam  Pre-Requisite Exam Seq #    Class   NULL    Other ID    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    Set Info / Reference:

I would like to have it display like this:

Protocol
Test
Dx Code
Dx
Test Code
INTERVAL
Start Age
Stop Age
Gender
NULL
NULL

I'm also doing this for SSRS 2005, so those restriction apply. Also, the select will only return one row, so handling multiple rows isn't an issue.

Bogdan Sahlean · Accepted Answer

You could use CROSS APPLY:

DECLARE @ngkbm_template_data_sets_ TABLE(
    id                  INT IDENTITY(1,1) PRIMARY KEY,
    txt_description_1   VARCHAR(50) NULL, -- or NVARCHAR,etc.
    txt_description_2   VARCHAR(50) NULL,
    txt_description_3   VARCHAR(50) NULL,
    txt_description_4   VARCHAR(50) NULL
);

INSERT  @ngkbm_template_data_sets_(txt_description_1,txt_description_2,txt_description_3,txt_description_4)
VALUES  ('Protocol','Test','Dx',NULL);

SELECT  x.id,y.*
FROM    @ngkbm_template_data_sets_ x
CROSS APPLY(
    SELECT  x.txt_description_1 AS Value, 'txt_description_1' AS ColumnName
    UNION ALL
    SELECT  x.txt_description_2 AS Value, 'txt_description_2' AS ColumnName
    UNION ALL
    SELECT  x.txt_description_3 AS Value, 'txt_description_3' AS ColumnName
    UNION ALL
    SELECT  x.txt_description_4 AS Value, 'txt_description_4' AS ColumnName         
)y;

Results:

id Value    ColumnName
-- -------- -----------------
1  Protocol txt_description_1
1  Test     txt_description_2
1  Dx       txt_description_3
1  NULL     txt_description_4

Edit 1: In following test, both solutions (CROSS APPLY + UNION ALL, UNION ALL only) have comparable performance from the point of view of CPU time and elapsed time

CPU time = 156 ms,  elapsed time = 2452 ms.
vs.
CPU time = 172 ms,  elapsed time = 2344 ms.

but from the point of view of LIO(logical reads) the results show a huge difference:

Table 'SalesOrderHeader'. Scan count 1, logical reads 686
vs.
Table 'SalesOrderHeader'. Scan count 6, logical reads 2881.

Actual execution plan for every solutions show the cause of this difference: enter image description here The CROSS APPLY + UNION ALL solution uses only one Clustered Index Scan but UNION ALL only solution uses four Clustered Index Scan operators and two Index Scan operators. Also, the estimated overall cost of every query is shows that SQL Servers "thinks" that CROSS APPLY + UNION ALL (39%) solution is a little bit better than UNION ALL only (61%). For this test I used AdventureWorks2008 R2 database.

SET NOCOUNT ON;
SET STATISTICS IO,TIME ON;
GO
PRINT 'CROSS APPLY';
SELECT  h.SalesOrderID,x.*
FROM    Sales.SalesOrderHeader h
CROSS APPLY(
    SELECT h.SalesOrderNumber AS Value, 'SalesOrderNumber' AS RowType
    UNION ALL
    SELECT h.PurchaseOrderNumber, 'PurchaseOrderNumber'
    UNION ALL
    SELECT h.AccountNumber , 'AccountNumber'
    UNION ALL
    SELECT h.CreditCardApprovalCode , 'CreditCardApprovalCode'
    UNION ALL
    SELECT h.Comment , 'Comment'    
    UNION ALL
    SELECT CONVERT(VARCHAR(36),h.rowguid) , 'rowguid'
)x
GO
PRINT 'UNION ALL only';
SELECT h.SalesOrderID,h.SalesOrderNumber AS Value, 'SalesOrderNumber' AS RowType FROM   Sales.SalesOrderHeader h
UNION ALL
SELECT h.SalesOrderID,h.PurchaseOrderNumber, 'PurchaseOrderNumber' FROM Sales.SalesOrderHeader h
UNION ALL
SELECT h.SalesOrderID,h.AccountNumber , 'AccountNumber' FROM    Sales.SalesOrderHeader h
UNION ALL
SELECT h.SalesOrderID,h.CreditCardApprovalCode , 'CreditCardApprovalCode' FROM  Sales.SalesOrderHeader h
UNION ALL
SELECT h.SalesOrderID,h.Comment , 'Comment' FROM    Sales.SalesOrderHeader h    
UNION ALL
SELECT h.SalesOrderID,CONVERT(VARCHAR(36),h.rowguid) , 'rowguid' FROM   Sales.SalesOrderHeader h
GO

SET NOCOUNT OFF;
SET STATISTICS IO,TIME OFF;
GO
/*
-- Output
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

CROSS APPLY + UNION ALL

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'SalesOrderHeader'. Scan count 1, logical reads 686, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 156 ms,  elapsed time = 2452 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

UNION ALL only

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Table 'SalesOrderHeader'. Scan count 6, logical reads 2881, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 172 ms,  elapsed time = 2344 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
*/

Select columns from another select as rows for each column

Answers (2)

Related Questions