How to get Rows to Columns (SQL Server)?

694 Asked by ankur_3579 in SQL Server , Asked on Jul 12, 2021

I have a table that has two columns, one with the type of posting and another column with the value. Source table: +-----+-------+ |Type | Value | +-----+-------+ | C | 381.22| | D | 25.49 | | C | 25.49 | | D | 25.48 | | C | 705.56| | D | 80.00 | +-----+-------+ I need to create a view that separates types C and D, and list their values. I need it to look like this: +------+-----+ |Credit|Debit| +------+-----+ |381.22|25.49| |25.49 |25.48| |705.56|80.00| +------+-----+ I tried to create two separate virtual columns with SELECTS, just to list the values for the respective types. SELECT (SELECT [value] FROM [table] WHERE [type] IN ('D')) AS Debit, (SELECT [value] FROM [table] WHERE [type] IN ('C')) AS Credit And the error occurs because both SELECTS return more than one result: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. Also try to use the PIVOT statement, but still unsuccessful. Could you give me a suggestion to solve this problem?

How do I get SQL server rows to columns?

Answered by Anusha Acharya

There are a couple ways that you can get sql server rows to columns. You can use PIVOT or you can also use an aggregate function along with a CASE expression, but in order to return multiple rows for each Type you'll need to create a unique value that you can GROUP BY.

I'd recommend using a windowing function like row_number to first generate a unique value for each row in your table partitioned by the TYPE. You'd write this similar to:

  select [Type], [Value], rn = row_number() over(partition by [Type] order by (select 1)) from yourtable;

This creates a unique value for each row in your table by Type aka Credit orDebit`. This is needed when you aggregate your data so you will return more than one row.

Once you've created this row number, you can convert your data into columns by using either the PIVOT function or CASE with aggregate.

select Credit = max(case when [Type] = 'C' then [Value] end), Debit = max(case when [Type] = 'D' then [Value] end) from ( select [Type], [Value], rn = row_number() over(partition by [Type] order by (select 1)) from yourtable ) d group by rn;

Or:

select Credit = C, Debit = D from ( select [Type], [Value], rn = row_number() over(partition by [Type] order by (select 1)) from yourtable ) d pivot ( max(value) for [Type] in (C, D) ) p;

Here is a demo. Both of these return the result:

| Credit | Debit | |--------|-------| | 381.22 | 80 | | 25.49 | 25.48 | | 705.56 | 25.49 |

If you need the Credit or Debit data to appear specifically in certain rows, than you would need to provide some additional data to tie each Credit or Debit together. These versions just convert the rows into columns of data.

How to get Rows to Columns (SQL Server)?

Your Answer