Converting Rows to Columns in SQL SERVER 2008

Question

In SQL Server 2008,

I have a table for tracking the status history of actions (STATUS_HISTORY) that has three columns ([ACTION_ID],[STATUS],[STATUS_DATE]).

Each ACTION_ID can have a variable number of statuses and status dates.

I need to convert these rows into columns that preferably look something like this:

[ACTION_ID], [STATUS_1], [STATUS_2], [STATUS_3], [DATE_1], [DATE_2], [DATE_3]

Where the total number of status columns and date columns is unknown, and - of course - DATE_1 correlates to STATUS_1, etc. And I'd like for the status to be in chronological order (STATUS_1 has the earliest date, etc.)

My reason for doing this is so I can put the 10 most recent Statuses on a report in an Access ADP, along with other information for each action. Using a subreport with each status in a new row would cause the report to be far too large.

Is there a way to do this using PIVOT? I don't want to use the date or the status as a column heading.

Is it possible at all?

I have no idea where to even begin. It's making my head hurt.

Helena · Accepted Answer · 2011-03-07 12:47:18Z

4

Let us suppose for brevity that you only want 3 most recent statuses for each action_id (like in your example).

Then this query using CTE should do the job:

WITH rownrs AS
(
 SELECT
   action_id
  ,status
  ,status_date
  ,ROW_NUMBER() OVER (PARTITION BY action_id ORDER BY status_date DESC) AS rownr
 FROM
  status_history
)

SELECT
  s1.action_id AS action_id

 ,s1.status AS status_1
 ,s2.status AS status_2
 ,s3.status AS status_3

 ,s1.status_date AS date_1
 ,s2.status_date AS date_2
 ,s3.status_date AS date_3

FROM

(SELECT * FROM rownrs WHERE rownr=1) AS s1

 LEFT JOIN
(SELECT * FROM rownrs WHERE rownr=2) AS s2
 ON s1.action_id = s2.action_id

 LEFT JOIN
(SELECT * FROM rownrs WHERE rownr=3) AS s3
 ON s1.action_id = s3.action_id

NULL values will appear in the rows where the action_id has less then 3 status-es.

edited Mar 7, 2011 at 12:47

answered Mar 7, 2011 at 12:42

Helena

6331 gold badge6 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Martin Smith Over a year ago

+1 You don't need all those JOIN s though

SELECT action_id,max(case when rownr=1 then status end) AS status_1,max(case when rownr=2 then status end) AS status_2,max(case when rownr=3 then status end) AS status_3,max(case when rownr=1 then status_date end) AS date_1,max(case when rownr=2 then status_date end) AS date_2,max(case when rownr=3 then status_date end) AS date_3 FROM rownrs  WHERE rownr< = 3 group by action_id

Helena Over a year ago

Yeah, definitely. Just out of curiosity - can you explain whether and why is your solution faster?

Martin Smith Over a year ago

@Helena - It should be faster as CTEs don't get materialised which means that for every self join it has to work out the row_numbering all over again. This way it just works it out once.

Thyamine · Accepted Answer · 2011-03-07 12:34:38Z

1

I haven't had to do it with two columns, but a PIVOT sounds like what you should try. I've done this in the past with dates in a result set where I needed the date in each row be turned into the columns at the top.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

I sympathize with the headache from trying to design and visualize it, but the best thing to do is try getting it working with one of the columns and then go from there. It helps once you start playing with it.

answered Mar 7, 2011 at 12:34

Thyamine

1,2587 silver badges10 bronze badges

Collectives™ on Stack Overflow

Converting Rows to Columns in SQL SERVER 2008

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related