1

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?

2
  • 2
    How do you think Sql server will know the first two rows go together without a column to designate some kind of order to the rows. Even if you inserted a credit, then a debit, then a credit, then a debit, there is no way to guarantee the rows will be selected in that order. You have to have a way to associate debits and credits. Jun 20, 2017 at 17:37
  • @ScottHodgin that was my question when this question was asked (then deleted and re-created I believe?). Also, what if there's no matching debit to a credit? Is it assumed that the phantom row is = 0?
    – Jacob H
    Jun 20, 2017 at 18:17

2 Answers 2

5

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.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.