CODEWITHSUNDEEP
sqlsql-servert-sql
phoenix
phoenix

Reputation: 43

Summary table generation in SQL Server

I have a table like this:

enter image description here

I want to see data in a summary view like this:

enter image description here

I need help for a T-SQL script. Thank you.

Sorry for my little English.

Upvotes: 0

Views: 1742

Answers (1)

Paurian
Paurian

Reputation: 1402

This works:

[Setup]

CREATE TABLE #PaymentTable ( Id INT IDENTITY, AccountGroupId INT, AccountId INT, Payment INT )
INSERT INTO #PaymentTable ( AccountGroupId, AccountId, Payment ) 
SELECT 1, 1, 5 UNION ALL SELECT 1, 1, 5 UNION ALL 
SELECT 1, 2, 5 UNION ALL SELECT 2, 4, 5 UNION ALL
SELECT 2, 3, 5 UNION ALL SELECT 2, 3, 5 UNION ALL
SELECT 2, 4, 5

CREATE TABLE #Group ( AccountGroupId INT, GroupName VARCHAR(100) )
INSERT INTO #Group ( AccountGroupId, GroupName )
SELECT 1, 'Group 1' UNION Select 2, 'Group 2'

CREATE TABLE #Account ( AccountId INT, AccountName VARCHAR(100) )
INSERT INTO #Account ( AccountId, AccountName )
SELECT 1, 'John' UNION Select 2, 'Edvard' UNION
SELECT 3, 'David' UNION SELECT 4, 'Jimi'

[Query]

SELECT
  [Group],
  Account,
  TotalPayment
FROM
  (
    SELECT
      #Group.AccountGroupId AS GroupId,
      GroupName AS [Group],
      '' AS Account,
      SUM( Payment ) AS TotalPayment,
      0 AS InnerOrder
    FROM
      #PaymentTable,
      #Group
    WHERE
      #Group.AccountGroupId = #PaymentTable.AccountGroupId
    GROUP BY
      #Group.AccountGroupId,
      #Group.GroupName
    UNION
    SELECT
      AccountGroupId AS GroupId,
      '' AS [Group],
      AccountName AS Account,
      SUM( Payment ) AS TotalPayment,
      1 AS InnerOrder
    FROM
      #PaymentTable,
      #Account
    WHERE
      #Account.AccountId = #PaymentTable.AccountId
    GROUP BY
      AccountGroupId,
      AccountName
  ) AS inner_query
ORDER BY
  GroupId,
  InnerOrder,
  Account

Upvotes: 2

Related Questions