How to pivot table?

Question

I have a table with following values.

Type New Closed Reopen
Claim 32 14 97
Line 14 37 7

How do I pivot the results in the following form?

Status Claim Line
New 32 14
Closed 14 37
Reopen 97 7

Thanks.

Answer 1

In addition, try the next example:

declare @table as table (Type varchar(10), New int, Closed int, Reopen int)

insert @table values
( 'Claim', 32, 14, 97 ),
( 'Line ', 14, 37,  7 )

select * from @table

---

select [Status], [Claim], [Line]
from @table
unpivot
(
    [val] for [Status] in( [New], [Closed], [Reopen] ) 
) as u
pivot
(
    sum(val) for [type] in( [Claim], [Line] ) 
) as p

/*
Status  Claim   Line
Closed  14      37
New     32      14
Reopen  97       7
*/

This also assumes that “Claim” and “Line” are the only (fixed) types. Otherwise a dynamic SQL can be considered.

Answer 2

You can try the following method via XQuery.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (Type VARCHAR(20), New INT, Closed INT, Reopen INT);
INSERT INTO @tbl (Type, New, Closed, Reopen) VALUES
('Claim', 32, 14, 97),
('Line', 14, 37, 7);
-- DDL and sample data population, end

SELECT * FROM @tbl;

;WITH rs AS
(
    SELECT (
        SELECT * FROM @tbl
        FOR XML PATH('r'), TYPE, ROOT('root')
    ).query('<root>
    {
            for $y in (1,2,3)
            return <r status="{local-name((/root/r[1]/*[$y + 1])[1])}" 
                claim="{/root/r[1]/*[$y + 1]}" 
                line="{/root/r[2]/*[$y + 1]}"></r>
    }
    </root>') AS xmldata
)
SELECT c.value('@status', 'VARCHAR(20)') AS [status]
    , c.value('@claim', 'INT') AS claim
    , c.value('@line', 'INT') AS line
FROM rs CROSS APPLY xmldata.nodes('/root/r') AS t(c);

Answer 3

You may find the post worth.
understanding-pivot-operator-in-sql-server-with-examples

Answer 4

If you want to use the PIVOT operator to achieve your target, try this:

DECLARE @MyTable TABLE (
    [Type] varchar(20),
    [New] int,
    [Closed] int, 
    [Reopen] int
);

INSERT INTO @MyTable VALUES
('Claim', 32, 14, 97),
('Line', 14, 37, 7);


;WITH CTE_New AS (
    SELECT 'New' AS [Status], [Claim], [Line]
    FROM (
        SELECT [Type], [New] FROM @MyTable
    ) AS src
    PIVOT (
        SUM([New])
        FOR [Type] IN ([Claim], [Line])
    ) AS pvt
),
CTE_Closed AS (
    SELECT 'Closed' AS [Status], [Claim], [Line]
    FROM (
        SELECT [Type], [Closed] FROM @MyTable
    ) AS src
    PIVOT (
        SUM([Closed])
        FOR [Type] IN ([Claim], [Line])
    ) AS pvt
),
CTE_Reopen AS (
    SELECT 'Reopen' AS [Status], [Claim], [Line]
    FROM (
        SELECT [Type], [Reopen] FROM @MyTable
    ) AS src
    PIVOT (
        SUM([Reopen])
        FOR [Type] IN ([Claim], [Line])
    ) AS pvt
)

SELECT [Status], [Claim], [Line]
FROM CTE_New
UNION ALL
SELECT [Status], [Claim], [Line]
FROM CTE_Closed
UNION ALL
SELECT [Status], [Claim], [Line]
FROM CTE_Reopen;

Answer 5

Check this approach too:

declare @table as table (Type varchar(10), New int, Closed int, Reopen int)

insert @table values
( 'Claim', 32, 14, 97 ),
( 'Line ', 14, 37,  7 )

select * from @table

---

select [Status], [Claim], [Line]
from (
    select [Type], 'New' as [Status], New as val from  @table
    union all
    select [Type], 'Closed', Closed from  @table
    union all
    select [Type], 'Reopen', Reopen from  @table
    ) as t
pivot
(
    sum(val) for [type] in( [Claim], [Line] ) 
) as p

/*
Status  Claim   Line
Closed  14      37
New     32      14
Reopen  97       7
*/