convert rows to columns order by another column in sql server

Question

I'm trying to convert this table

ID  TestID  Elapsed ActionID
===================================
1   1         16    a1
2   1         17    a2
3   1         13    a3  
4   1         14    a4
5   2         19    a1
6   2         21    a2
7   2         11    a3
8   2         22    a4

To this

TestID  a1  a2  a3  a4
======================================
1       16  17  13  14
2       19  21  11  22

is this possible?

score 1 · Answer 1 · edited May 23 '17 at 10:34

If you are using SQL Server 2005 (or above), here's the query, with proof of concept. Enjoy:

--Proof of concept structure and data creation
create table #t (ID int, TestID int, Elapsed int, ActionID varchar(10))

insert into #t  (ID, TestID, Elapsed, ActionID) values
(1,   1,         16,    'a1'),
(2,   1,         17,    'a2'),
(3,   1,         13,    'a3'),
(4,   1,         14,    'a4'),
(5,   2,         19,    'a1'),
(6,   2,         21,    'a2'),
(7,   2,         11,    'a3'),
(8,   2,         22,    'a4');
--end of structure and data creating

--actual query starts here
DECLARE @cols VARCHAR(1000)
DECLARE @sqlquery VARCHAR(2000)

SELECT  @cols = STUFF(( SELECT distinct  ',' + QuoteName([ActionID])
                        FROM #t  FOR XML PATH('') ), 1, 1, '') 

SET @sqlquery = 'SELECT * FROM
      (SELECT TestID, Elapsed, ActionID
       FROM #t  ) base
       PIVOT (SUM(Elapsed) FOR [ActionID]
       IN (' + @cols + ')) AS finalpivot'

--Depending on your approach, you might want to use MAX instead of SUM. 
--That will depend on your business rules

EXECUTE ( @sqlquery )
--query ends here


--proof of concept cleanup
drop table #t;

This will work no matter how many different values you have in ActionID. It dynamically assembles a query with PIVOT. The only way you can do PIVOT with dynamic columns is by assembling the the query dynamically, which can be done in SQL Server.

Other examples:

score 1 · Accepted Answer · answered Nov 23 '11 at 19:42

Yes, if there is only one action id for each testid

There is the pivot operator that Ajoe mentioned, but I think the traditional syntax is easier to understand (if not immediately obvious).

You group rows by testid, so you will get one row of results per each testid. What you select is the "max" in each group where the acitionid is a certain one. Or the min, or the average, or the sum - this is predicated on there being only one item in each group.

 SELECT testid,
   MAX(CASE WHEN actionid = 'a1' THEN elapsed ELSE null END) AS a1,
   MAX(CASE WHEN actionid = 'a2' THEN elapsed ELSE null END) AS a2,
   MAX(CASE WHEN actionid = 'a3' THEN elapsed ELSE null END) AS a3,
   MAX(CASE WHEN actionid = 'a4' THEN elapsed ELSE null END) AS a4
FROM results
GROUP BY testid

convert rows to columns order by another column in sql server

2 Answers2