Rows to Columns (SQL Server)

Question

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?

Answer 1

There are a couple ways that you can get the final result that you want. 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.

Answer 2

You can use JOIN with ROW_NUMBER() to achieve this:

SELECT Credit, Dedit
FROM ( SELECT Value AS Credit, ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS RowId
    FROM TestTable 
    WHERE Type = 'C' ) AS CR
JOIN ( SELECT Value AS Dedit, ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS RowId
    FROM TestTable 
    WHERE Type = 'D' ) AS DE ON DE.RowId = CR.RowId;

Sample execution with given data:

CREATE TABLE TestTable (Type CHAR(1), Value NUMERIC(9,2));

INSERT INTO TestTable (Type, Value) VALUES
('C', 381.22),
('D', 25.49 ),
('C', 25.49 ),
('D', 25.48 ),
('C', 705.56),
('D', 80.00 );

SELECT Credit, Dedit
FROM ( SELECT Value AS Credit, ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS RowId
    FROM TestTable 
    WHERE Type = 'C' ) AS CR
JOIN ( SELECT Value AS Dedit, ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ) ) AS RowId
    FROM TestTable 
    WHERE Type = 'D' ) AS DE ON DE.RowId = CR.RowId;

Working Demo: http://rextester.com/FBDTW83211

Output:

Credit  | Dedit
-----------------
381.22  | 25.49
25.49   | 25.48
705.56  | 80.00