CODEWITHSUNDEEP
sqlt-sqlsql-server-2008rows
Dustin Oakes
Dustin Oakes

Reputation: 3

Converting Rows to Columns in SQL SERVER 2008

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.

Upvotes: 0

Views: 8921

Answers (2)

Helena
Helena

Reputation: 633

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.

Upvotes: 4

Thyamine
Thyamine

Reputation: 1258

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.

Upvotes: 1

Related Questions