Need help on SQL - Row Become Header

Transact-SQL
1

This is my table and data

CREATE TABLE [dbo].[test_Ledger](
    [idx] [int] IDENTITY(1,1) NOT NULL,
    [cmpy_code] [nvarchar](10) NULL,
    [acct_code] [nvarchar](30) NULL,
    [jour_code] [nvarchar](10) NULL,
    [jour_num] [int] NULL,
    [jour_seq_num] [int] NULL,
    [debit_amt] [decimal](14, 2) NULL,
    [credit_amt] [decimal](14, 2) NULL,
 CONSTRAINT [test_Ledger_UQ1] UNIQUE NONCLUSTERED 
(
    [cmpy_code] ASC,
    [jour_code] ASC,
    [jour_num] ASC,
    [jour_seq_num] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[test_Ledger] ON
INSERT [dbo].[test_Ledger] ([idx], [cmpy_code], [acct_code], [jour_code], [jour_num], [jour_seq_num], [debit_amt], [credit_amt]) VALUES (1, N'SA', N'01-89061-0124001-1', N'PP', 44290, 64, CAST(0.00 AS Decimal(14, 2)), CAST(1575.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Ledger] ([idx], [cmpy_code], [acct_code], [jour_code], [jour_num], [jour_seq_num], [debit_amt], [credit_amt]) VALUES (2, N'SA', N'01-89031-0124001-1', N'PP', 44290, 71, CAST(0.00 AS Decimal(14, 2)), CAST(400.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Ledger] ([idx], [cmpy_code], [acct_code], [jour_code], [jour_num], [jour_seq_num], [debit_amt], [credit_amt]) VALUES (3, N'SA', N'01-89059-0124001-1', N'PP', 44290, 63, CAST(0.00 AS Decimal(14, 2)), CAST(90.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Ledger] ([idx], [cmpy_code], [acct_code], [jour_code], [jour_num], [jour_seq_num], [debit_amt], [credit_amt]) VALUES (4, N'SA', N'01-91528-0124001-1', N'PP', 44290, 3, CAST(14114.00 AS Decimal(14, 2)), CAST(0.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Ledger] ([idx], [cmpy_code], [acct_code], [jour_code], [jour_num], [jour_seq_num], [debit_amt], [credit_amt]) VALUES (5, N'AA', N'02-72214-417000', N'PP', 26516, 22, CAST(0.00 AS Decimal(14, 2)), CAST(225.00 AS Decimal(14, 2)))
INSERT [dbo].[test_Ledger] ([idx], [cmpy_code], [acct_code], [jour_code], [jour_num], [jour_seq_num], [debit_amt], [credit_amt]) VALUES (6, N'AA', N'02-91836-900000', N'AP', 26515, 408, CAST(0.00 AS Decimal(14, 2)), CAST(36.00 AS Decimal(14, 2)))
SET IDENTITY_INSERT [dbo].[test_Ledger] OFF

*My cmpy_code only SA and AA

I want to query to make it my SA and AA become a Header. The format as following,

acct_code | jour_code | jour_num | jour_seq_num | SA_Debit_Amt | SA_Credit_Amt | AA_Debit_Amt | AA_Credit_Amt

Please help

2

Like this:

SELECT  ACCT_CODE, JOUR_CODE, JOUR_NUM
,SUM(CASE WHEN CMPY_CODE = 'SA'
THEN DEBIT_AMT END
) AS SA_DEBIT
,SUM(CASE WHEN CMPY_CODE = 'AA'
THEN DEBIT_AMT END
) AS AA_DEBIT
,SUM(CASE WHEN CMPY_CODE = 'SA'
THEN CREDIT_AMT END
) AS SA_CREDIT
,SUM(CASE WHEN CMPY_CODE = 'AA'
THEN CREDIT_AMT END
) AS AA_CREDIT
FROM [DBO].[TEST_LEDGER]
GROUP BY   ACCT_CODE, JOUR_CODE, JOUR_NUM

How to PIVOT multiple columns?

1 Like
3

Thanks Sir