combining multiple rows into one row in sql server

2
votes

I have a table like this.

+----+-------+---------+----------+---------+----------+---------+----------+
| id | Month | Debit_A | Credit_A | Debit_B | Credit_B | Debit_C | Credit_C |
+----+-------+---------+----------+---------+----------+---------+----------+
| 1  |  Jan  | 100.50  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 2  |  Jan  |         |  100.50  |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 3  |  Jan  | 150.25  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 4  |  Jan  |         |          |         |          | 300.00  |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 5  |  Jan  |         |          |         |          |         |  300.00  |
+----+-------+---------+----------+---------+----------+---------+----------+
| 6  |  Feb  |         |          |  79.80  |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 7  |  Feb  |         |          |         |   79.80  |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 8  |  Feb  |         |          | 200.00  |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 9  |  Feb  |         |          |         |  200.00  |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 10 |  Mar  |         |          |         |          | 1500.00 |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 11 |  Mar  |         |          |         |          |         | 1500.00  |
+----+-------+---------+----------+---------+----------+---------+----------+
| 12 |  Apr  | 100.00  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 13 |  Apr  |         |  50.00   |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 14 |  May  |         |          |  50.75  |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 15 |  May  |         |          |         |  50.70   |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 16 |  Jun  |         |          |         |          |  75.50  |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 17 |  Jun  |         |          |         |          |         |  75.50   |
+----+-------+---------+----------+---------+----------+---------+----------+
| 18 |  Jun  |         |          |         |          |  75.50  |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 19 |  Jun  |         |          |         |          |         |  75.50   |
+----+-------+---------+----------+---------+----------+---------+----------+
| 20 |  Jul  |  89.50  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+

What I want is combine the rows that have the same Month value and same Debit/Credit value to a new table. This will combine the Debit and Credit rows into one row. Example:

+----+-------+---------+----------+---------+----------+---------+----------+
| id | Month | Debit_A | Credit_A | Debit_B | Credit_B | Debit_C | Credit_C |
+----+-------+---------+----------+---------+----------+---------+----------+
| 1  |  Jan  | 100.50  |  100.50  |         |          | 300.00  |  300.00  |
+----+-------+---------+----------+---------+----------+---------+----------+
| 2  |  Jan  | 150.25  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 3  |  Feb  |         |          |  79.80  |   79.80  |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 4  |  Feb  |         |          | 200.00  |  200.00  |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 5  |  Mar  |         |          |         |          | 1500.00 | 1500.00  |
+----+-------+---------+----------+---------+----------+---------+----------+
| 6  |  Apr  | 100.00  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 7  |  Apr  |         |  50.00   |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 8  |  May  |         |          |  50.75  |  50.70   |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+
| 9  |  Jun  |         |          |         |          |  75.50  |  75.50   |
+----+-------+---------+----------+---------+----------+---------+----------+
| 10 |  Jun  |         |          |         |          |  75.50  |  75.50   |
+----+-------+---------+----------+---------+----------+---------+----------+
| 11 |  Jul  |  89.50  |          |         |          |         |          |
+----+-------+---------+----------+---------+----------+---------+----------+

How do I do this in SQL Server 2008 or SQL Server 2012?

3
sql-serversql-server-2008sql-server-2012
I have to say that table structure doesn't seem right unless you've got a very specific situation. Is there any reason you're not normalising the data? With a better table design, this would be a lot simpler. - Basic
I agree with @Basic, unless I got a solid reason, I won't put myself in that position. But I know that sometimes the requirements are just the same and I was forced to do something real close to what you want. - CodingMate
I do hope this is homework and not a real system.... - Mitch Wheat
I am working on existing data so I have no choice but to use this table structure. - ghost
If you have to, I used the temp tables for that couple of times, and I even made that code in a stored procedure and calling it from a SSIS in a scheduled task (Which was a nightmare and really breaking all the logic of the SQL, but I was forced too) - CodingMate

3 Answers

0
votes

If I were you, I have two solutions for making this happen:

  1. By using temp tables (Either physical table or temp tables {#Table or ##Table}) and making multiple updating the row for setting the values of those fields.
  2. By using cursors (I usually don't prefer the cursors as it affects the performance of the database and to be honest, my skills with the cursors are really poor)

For the Temp tables

  1. I would make an initial insert and setting the Debit values by 0 in the beggining
  2. Make an update for each Debit Type

UPDATE:

SELECT id, Month, 0 AS Debit_A, 0 AS Credit_A, 0 AS Debit_B, 0 AS Credit_B, 0 AS Debit_C, 0 AS Credit_C
INTO #TempTable
FROM YourTable
GROUP BY Month

UPDATE T
SET Debit_A = Value
FROM #TempTable T
INNER JOIN DebitATable D
ON T.Month = D.Month

I'd use something like that (sorry as it is a little bit generic as I don't know the structure of the database)

You can use GROUP or SUM for the values you're getting in either the Debit or the Credit values

0
votes
-- 1st we import the raw account activity data
IF OBJECT_ID('dbo.RawActivity','U') IS NOT NULL
    DROP TABLE dbo.RawActivity;
GO

-- for the sake of mnemonics--and precedence when ordering--transactions
-- for account A, B, C and so on are defined as 0 for debits and 1 for
-- credits, and month short names are replaced with their number
CREATE TABLE dbo.RawActivity(id int,
                             M int,
                             A0 money,
                             A1 money,
                             B0 money,
                             B1 money,
                             C0 money,
                             C1 money);
GO
INSERT dbo.RawActivity
VALUES
(1, 1, 100.50, NULL, NULL, NULL, NULL, NULL),
(2, 1, NULL, 100.50, NULL, NULL, NULL, NULL),
(3, 1, 150.25, NULL, NULL, NULL, NULL, NULL),
(4, 1, NULL, NULL, NULL, NULL, 300.00, NULL),
(5, 1, NULL, NULL, NULL, NULL, NULL, 300.00),
(6, 2, NULL, NULL, 79.80, NULL, NULL, NULL),
(7, 2, NULL, NULL, NULL, 79.80, NULL, NULL),
(8, 2, NULL, NULL, 200.00, NULL, NULL, NULL),
(9, 2, NULL, NULL, NULL, 200.00, NULL, NULL),
(10 ,3, NULL, NULL, NULL, NULL, 1500.00, NULL),
(11, 3, NULL, NULL, NULL, NULL, NULL, 1500.00),
(12, 4, 100.00, NULL, NULL, NULL, NULL, NULL),
(13, 4, NULL, 50.00, NULL, NULL, NULL, NULL),
(14, 5, NULL, NULL, 50.75, NULL, NULL, NULL),
(15, 5, NULL, NULL, NULL, 50.70, NULL, NULL),
(16, 6, NULL, NULL, NULL, NULL, 75.50, NULL),
(17, 6, NULL, NULL, NULL, NULL, NULL, 75.50),
(18, 6, NULL, NULL, NULL, NULL, 75.50, NULL),
(19, 6, NULL, NULL, NULL, NULL, NULL, 75.50),
(20, 7, 89.50, NULL, NULL, NULL, NULL, NULL);
GO

-- 2nd we are going to flatten the raw data into a spagetti of transactions
-- with type T and parent transaction
IF OBJECT_ID('dbo.Activity','U') IS NOT NULL
    DROP TABLE dbo.Activity;
GO

CREATE TABLE dbo.Activity(id int, M int, amount money, T char(2), pid int);
GO
INSERT INTO dbo.Activity
SELECT  id,
        M,
        -- amount: the 1st non null
        ISNULL(A0, ISNULL(A1, ISNULL(B0, ISNULL(B1, ISNULL(C0, C1))))),
        -- type
        CASE
            WHEN A0 IS NOT NULL THEN 'A0'
            WHEN A1 IS NOT NULL THEN 'A1'
            WHEN B0 IS NOT NULL THEN 'B0'
            WHEN B1 IS NOT NULL THEN 'B1'
            WHEN C0 IS NOT NULL THEN 'C0'
            WHEN C1 IS NOT NULL THEN 'C1'
        END,
        -- parent id
        NULL
FROM dbo.RawActivity;

-- 3rd it gets interesting; we update the parent id with the 1st transaction
-- of each month, with help from FIRST_VALUE...
UPDATE dbo.Activity SET pid = P.pid
FROM
(
    SELECT  P.id,
            FIRST_VALUE(A.id) OVER(PARTITION BY A.M ORDER BY A.M, A.id) AS pid
    FROM    dbo.Activity AS P INNER JOIN
            dbo.Activity AS A ON P.id = A.id
) AS P
WHERE dbo.Activity.id = P.id;

-- we update same month and type transactions parent so that they are not
-- added together...
UPDATE  A
SET     A.pid = A.id
FROM    dbo.Activity AS A JOIN
        dbo.Activity AS B ON A.pid = B.id AND A.T = B.T AND A.M = B.M;

-- and we look for (type, parent) duplicates to update the parent; this
-- catches subsequent transactions of the same type. The naming of
-- transaction types pays off here because of the partitioning, and
-- the new parent will be the transaction right above the duplicate
-- TODO: test with more than two consecutive transactions of the same
-- type (there is none in your data)
WITH Duplicates AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY T, pid ORDER BY id) AS r FROM dbo.Activity
)
UPDATE Duplicates SET pid = id - 1 WHERE r > 1;

-- finally, we can pivot the whole thing to get back to the desired output;
-- the outer SELECT statement is just for beautification.
SELECT  ROW_NUMBER() OVER(ORDER BY M) AS id,
        FORMAT(DATEFROMPARTS(YEAR(GETDATE()), M, 1), 'MMM') AS [Month],
        A0 AS Debit_A,
        A1 AS Credit_A,
        B0 AS Debit_B,
        B1 AS Credit_B,
        C0 AS Debit_C,
        C1 AS Credit_C
FROM
(
    SELECT  M,
            SUM([A0]) AS A0,
            SUM([A1]) AS A1,
            SUM([B0]) AS B0,
            SUM([B1]) AS B1,
            SUM([C0]) AS C0,
            SUM([C1]) AS C1
    FROM
    (
        SELECT id, M, amount, T, pid FROM dbo.Activity
    ) AS A
    PIVOT
    (
        SUM(amount)
        FOR T IN ([A0], [A1], [B0], [B1], [C0], [C1])
    ) AS PVT
    GROUP BY M, pid
) AS Result;
-1
votes

Shouldn't Jan be only one row then with Debit_A = 250.75? In Standard SQL I guess you can use this, not sure if it fits with sql-server:

select sum(debit_a), sum(debit_b), .. from table name group by month;