How to show two columns as rows in SQL Server?

Question

Convert two column to rows in SQL Server

Possible duplicate of [SQL Server Pivot Table with multiple column aggregates](http://stackoverflow.com/questions/14694691/sql-server-pivot-table-with-multiple-column-aggregates) — Tab Alleman, Jun 27 '16 at 13:28

score 0 · Answer 1 · answered Jun 27 '16 at 12:26

by Simple Cross Apply

DECLARE @Table1  TABLE 
    (ID int,installdate varchar(20),uninstalldate varchar(20))
;

INSERT INTO @Table1
    (ID,installdate,uninstalldate)
VALUES
    (1,'15/06/2016','18/06/2016'),
    (2,'20/06/2016','25/06/2016')

Script :

 select COL AS [Instal OR Uninstall],VAL AS [Date] from @Table1
        CROSS APPLY
    (VALUES 
    ('installdate',installdate),
    ('uninstalldate',installdate))
    CS(COL,VAL)

score 0 · Answer 2 · answered Jun 27 '16 at 12:27

0

Simple UNPIVOT should do thing:

SELECT  [DATES], 
        [VALUES]
FROM MyTable
UNPIVOT (
    [VALUES] FOR [DATES] IN (InstallDate,UnInstallDate)
) as unpvt

Output:

DATES           VALUES
InstallDate     2016-06-15
UnInstallDate   2016-06-18
InstallDate     2016-06-20
UnInstallDate   2016-06-25

answered Jun 27 '16 at 12:27

gofr1

15,741
11
42
52

My pleasure! If answer(s) were helpful feel free to upvote/accept them :) That is the best way to say *thank you* here :) – gofr1 Jun 28 '16 at 11:20

score 0 · Answer 3 · answered Jun 27 '16 at 12:29

You can UNPIVOT the columns into rows:

DECLARE @Data TABLE (
    Id INT,
    InstallDate DATE,
    UnInstallDate DATE
)
INSERT @Data VALUES (1,'6/15/2016', '6/18/2016'),(2,'6/20/2016', '6/25/2016')

SELECT
    ActivityType,
    ActivityDate
FROM @Data 
    UNPIVOT (ActivityDate FOR ActivityType IN (InstallDate, UnInstallDate)) T

This produces the following rows:

ActivityType              ActivityDate
------------------------- ------------
InstallDate               2016-06-15
UnInstallDate             2016-06-18
InstallDate               2016-06-20
UnInstallDate             2016-06-25

How to show two columns as rows in SQL Server?

3 Answers3