CODEWITHSUNDEEP
sqlsql-server-2008pivot
Amarundo
Amarundo

Reputation: 2397

Convert row values into columns

I have a table:

CREATE TABLE StatusStats (
    UserID      VARCHAR(50),
    StatusCategory  VARCHAR(50),
    StatusDuration  INT
)

That contains, for each user, how much time they were (StatusCategory)

(fyi... status duration is in seconds)

Those are the only 3 values.

In order to use this data in reporting using JOINs to other tables, I need to copy that data in to this table:

CREATE TABLE StatusStatsByUser (
    UserID      VARCHAR(50),
        Productive      INT,
        NonProductive   INT,
        Unknown         INT
)

That will contain in each column the value that was in the StatusDuration column for the corresponding value in the StatusCategory column.

I can't figure out how to write the SQL code to do that, using SQL Server 2008R2. Your help is very much appreciated.

Upvotes: 0

Views: 251

Answers (2)

Taryn
Taryn

Reputation: 247690

There is no need to store this data in a table, you can easily create a view that PIVOTs the data into the columns that you need:

CREATE VIEW yourPivotedView
AS 
select userid, Productive, NonProductive, Unknown
from 
(
    select userid, StatusCategory, StatusDuration
    from StatusStats 
) src
pivot
(
    sum(StatusDuration)
    for StatusCategory in (Productive, NonProductive, Unknown)
) piv

Once this is in a view, then you can join on it with your other tables:

select *
from yourtable t1
inner join yourPivotedView p
  on t1.userid = p.userid

But if you must insert this into a new table, you can use:

insert into StatusStatsByUser (userid, Productive, NonProductive, Unknown)
select userid, Productive, NonProductive, Unknown
from 
(
    select userid, StatusCategory, StatusDuration
    from StatusStats 
) src
pivot
(
    sum(StatusDuration)
    for StatusCategory in (Productive, NonProductive, Unknown)
) piv

Upvotes: 1

Jim B
Jim B

Reputation: 8574

Maybe something like this:

INSERT INTO StatusStatsByUser
SELECT  UserId ,
        SUM(CASE WHEN StatusCategory = 'Productive' THEN StatusDuration
                 ELSE 0
            END) AS Productive ,
        SUM(CASE WHEN StatusCategory = 'NonProductive' THEN StatusDuration
                 ELSE 0
            END) AS NonProductive ,
        SUM(CASE WHEN StatusCategory = 'Unknown' THEN StatusDuration
                 ELSE 0
            END) AS Unknown
FROM StatusStats
GROUP BY UserId

Upvotes: 1

Related Questions