How to transform rows into column?

Question

I have a table like this and there are only two feature for all user in this table

+-------+---------+-----------+----------+
| User  | Feature | StartDate | EndDate  |
+-------+---------+-----------+----------+
| Peter |   F1    | 2015/1/1  | 2015/2/1 |
| Peter |   F2    | 2015/3/1  | 2015/4/1 |
| John  |   F1    | 2015/5/1  | 2015/6/1 |
| John  |   F2    | 2015/7/1  | 2015/8/1 |
+-------+---------+-----------+----------+

I want to transform to

+-------+--------------+------------+--------------+------------+
| User  | F1_StartDate | F1_EndDate | F2_StartDate | F2_EndDate |
+-------+--------------+------------+--------------+------------+
| Peter |   2015/1/1   |  2015/2/1  |   2015/3/1   |  2015/4/1  |
| John  |   2015/5/1   |  2015/6/1  |   2015/7/1   |  2015/8/1  |
+-------+--------------+------------+--------------+------------+

Tag dbms product used, since you'll probably need product specific functionality here. — jarlh, Oct 07 '15 at 07:49
It is called a transposition and is often a bad practice. However here is a general solution http://stackoverflow.com/questions/13372276/simple-way-to-transpose-columns-and-rows-in-sql. The trick is only to make many selection on same table and join it — pdem, Oct 07 '15 at 07:54
@pdem how can it be bad practice to present the data in a pivot ? — t-clausen.dk, Oct 07 '15 at 08:24

score 1 · Accepted Answer · answered Oct 07 '15 at 07:55

1

If you are using SQL Server 2005 or up by any chance, PIVOT is what you are looking for.

answered Oct 07 '15 at 07:55

Michel de Ruiter

7,131
5
49
74

Yes, firstly I use PIVOT to transform it as a ResultSet like this [Name], [F1], [F2], then using F1 F2 as a key to join the original table – user1043963 Oct 07 '15 at 08:25

score 0 · Answer 2 · answered Oct 07 '15 at 07:52

The best general way to perform this sort of operation is a simple group by statement. This should work across all major ODBMS:

select user,
       max(case when feature='F1' then StartDate else null end) F1_StartDate,
       max(case when feature='F1' then EndDate else null end) F1_EndDate,
       max(case when feature='F2' then StartDate else null end) F2_StartDate,
       max(case when feature='F2' then EndDate else null end) F2_EndDate
    from table 
    group by user

Note: as mentioned in the comments, this is often bad practice, as depending on your needs, it can make the data harder to work with. However, there are cases where it makes sense, when you have a small, limited number of values.

t-clausen.dk · Answer 3 · 2015-10-07T08:42:48.743

Use UNPIVOT & PIVOT like this:

Test data:

DECLARE @t table
  (User1 varchar(20),Feature char(2),StartDate date,EndDate date)
INSERT @t values
('Pete','F1','2015/1/1 ','2015/2/1'),
('Pete','F2','2015/3/1 ','2015/4/1'),
('John','F1','2015/5/1 ','2015/6/1'),
('John','F2','2015/7/1 ','2015/8/1')

Query:

;WITH CTE AS
(
  SELECT User1, date1, Feature + '_' +  Seq cat
  FROM @t as p  
  UNPIVOT      
  (date1 FOR Seq IN           
  ([StartDate], [EndDate]) ) AS unpvt  
)
SELECT * FROM CTE
PIVOT
(MIN(date1)
FOR cat
IN ([F1_StartDate],[F1_EndDate],[F2_StartDate],[F2_EndDate])
) as p

Result:

User1  F1_StartDate  F1_EndDate  F2_StartDate  F2_EndDate
John   2015-05-01    2015-06-01  2015-07-01    2015-08-01
Pete   2015-01-01    2015-02-01  2015-03-01    2015-04-01

score 0 · Answer 4 · answered Oct 07 '15 at 08:38

This is a bit of a hack with a CTE:

;WITH CTE AS (
SELECT [User], [Feature] + '_StartDate' AS [Type], StartDate AS [Date]
FROM Table1
UNION ALL
SELECT [User], [Feature] + '_EndDate' AS [Type], EndDate AS [Date]
FROM Table1)
SELECT * FROM CTE
PIVOT(MAX([Date]) FOR [Type] IN ([F1_StartDate],[F2_StartDate], [F1_EndDate], [F2_EndDate])) PIV

How to transform rows into column?

4 Answers4