CODEWITHSUNDEEP
sqlsql-serverunpivot
Sue
Sue

Reputation: 53

SQL Server and how to convert rows to columns

I want to convert the columns to rows using SQL Server. I have already tried unpivot but I don't have enough knowledge to make it, only can make one column.

This is the script for the original table:

CREATE TABLE #tb1(
    [item] [numeric](6, 0) NULL,
    [class] [char](9) NULL,
    [P_1YEAR] [numeric](5, 0) NULL,
    [P_2YEAR] [numeric](5, 0) NULL,
    [P_3YEAR] [numeric](5, 0) NULL,
    [C_YR_RET1] [numeric](8, 4) NULL,
    [C_YR_RET2] [numeric](8, 4) NULL,
    [C_YR_RET3] [numeric](7, 4) NULL,
) ON [PRIMARY]
GO
INSERT #tb1 ( [item],  [class], [P_1YEAR], [P_2YEAR], [P_3YEAR], [C_YR_RET1], [C_YR_RET2], [C_YR_RET3]) 
VALUES ( CAST(29 AS Numeric(6, 0)), N'A ', CAST(2018 AS Numeric(5, 0)), CAST(2017 AS Numeric(5, 0)), CAST(2016 AS Numeric(5, 0)),CAST(-7.0755 AS Numeric(8, 4)), CAST(6.0703 AS Numeric(8, 4)), CAST(13.3893 AS Numeric(7, 4)))
INSERT #tb1 ( [item],  [class], [P_1YEAR], [P_2YEAR], [P_3YEAR], [C_YR_RET1], [C_YR_RET2], [C_YR_RET3])
VALUES (CAST(29 AS Numeric(6, 0)), N'F ', CAST(2018 AS Numeric(5, 0)), CAST(2017 AS Numeric(5, 0)), CAST(2016 AS Numeric(5, 0)), CAST(-6.0276 AS Numeric(8, 4)), CAST(7.2750 AS Numeric(8, 4)), CAST(14.8798 AS Numeric(7, 4)))
GO

The final table should look like this: enter image description here

Thanks!

Upvotes: 0

Views: 80

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I think the simplest method, by far, is to use apply and conditional aggregation:

select item, year,
       max(case when class = 'A' then c end) as a,
       max(case when class = 'F' then c end) as f
from tb1 cross apply
     (values (p_1year, c_yr_ret1), 
             (p_2year, c_yr_ret2),
             (p_3year, c_yr_ret3)
     ) v(year, c)
group by item, year
order by item, year desc;

apply usually has better performance then using union all, because it only scans the underlying table once.

Here is a db<>fiddle.

Upvotes: 1

Sasha Pasternak
Sasha Pasternak

Reputation: 1

you can do it with pivot only if you know that the column 'class' has static values that rarely change, else you'll have to do it with building this pivot into a string and execute it with 'EXECUTE sp_executesql'.

SELECT *
FROM
(
    SELECT item, class, p_1year as year, c_yr_ret_1 as value FROM table_name
    union all
    SELECT item, class, p_2year, c_yr_ret_2 FROM table_name
    union all
    SELECT item, class, p_3year, c_yr_ret_3 FROM table_name
) table1
PIVOT
(
    SUM(value) for class in ([A], [F])
) as pivoted_table;

Upvotes: 0

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT item, [Year],
SUM(A) A,SUM(F) F
FROM
(
    SELECT item, p_1year [Year],
    CASE WHEN class = 'A' THEN c_yr_rate_1year ELSE 0 END 'A',
    CASE WHEN class = 'F' THEN c_yr_rate_1year ELSE 0 END 'F'
    FROM your_table

    UNION ALL

    SELECT item, p_2year [Year],
    CASE WHEN class = 'A' THEN c_yr_rate_2year ELSE 0 END 'A',
    CASE WHEN class = 'F' THEN c_yr_rate_2year ELSE 0 END 'F'
    FROM your_table

    UNION ALL

    SELECT item, p_3year [Year],
    CASE WHEN class = 'A' THEN c_yr_rate_3year ELSE 0 END 'A',
    CASE WHEN class = 'F' THEN c_yr_rate_3year ELSE 0 END 'F'
    FROM your_table
)A
GROUP BY item, [Year]
ORDER BY 2

Upvotes: 1

Ahmad
Ahmad

Reputation: 12737

One way to do that is,

SELECT
   ITEM,
   YEAR,
   SUM(CASE WHEN CLASS='A' THEN C_YR_RET_1YR ELSE 0 /* OR NULL */ END) AS A,
   SUM(CASE WHEN CLASS='F' THEN C_YR_RET_12YR ELSE 0 /* OR NULL*/ END) AS F
FROM
   TABLE_NAME
GROUP BY
   ITEM,
   YEAR

You might want to use null or 0 depending on if you decide to use aggregate functions on the same field later on. Zeros will make sums easier. Nulls will make Averages easier.

Upvotes: 0

Related Questions