Need to get he output in below format uisng Pivot or Unpivot

Transact-SQL
1

Hi All,
I have an requirment where I want to convert column in to below 2 format .
Query to populate the table with data

CREATE TABLE bil
(
Due_Date date,
ID BIGINT,
Payment int,
CODE NVARCHAR(10)
)

INSERT INTO bil
SELECT '01-10-2021', '56536' , 63 ,'CIS' UNION ALL
SELECT '01-10-2021', '56536' , 6 ,'PTD' UNION ALL
SELECT '01-10-2021', '56536' , 87 ,'ELM' UNION ALL
SELECT '01-11-2021', '56536' , 63 ,'CIS' UNION ALL
SELECT '01-11-2021', '56536' , 6 ,'PTD' UNION ALL
SELECT '01-11-2021', '56536' , 87 ,'ELM' UNION ALL
SELECT '01-10-2021', '56896' , 63 ,'CIS' UNION ALL
SELECT '01-10-2021', '56896' , 6 ,'PTD' UNION ALL
SELECT '01-10-2021', '56896' , 87 ,'ELM' UNION ALL
SELECT '01-11-2021', '56896' , 63 ,'CIS' UNION ALL
SELECT '01-11-2021', '56896' , 6 ,'PTD' UNION ALL
SELECT '01-11-2021', '56896' , 87 ,'ELM'

pivot
pivot979×520 49.8 KB

Please do let me know the logic which help to get the required result output set uisng sql

Thanks in advance,

2

here's a start. What have you tried for the second one?

drop table if exists #bil 
go

CREATE TABLE #bil
(
Due_Date date,
ID BIGINT,
Payment int,
CODE NVARCHAR(10)
)

INSERT INTO #bil
SELECT '01-10-2021', '56536' , 63 ,'CIS' UNION ALL
SELECT '01-10-2021', '56536' , 6 ,'PTD' UNION ALL
SELECT '01-10-2021', '56536' , 87 ,'ELM' UNION ALL
SELECT '01-11-2021', '56536' , 163 ,'CIS' UNION ALL
SELECT '01-11-2021', '56536' , 16 ,'PTD' UNION ALL
SELECT '01-11-2021', '56536' , 187 ,'ELM' UNION ALL
SELECT '01-10-2021', '56896' , 63 ,'CIS' UNION ALL
SELECT '01-10-2021', '56896' , 6 ,'PTD' UNION ALL
SELECT '01-10-2021', '56896' , 87 ,'ELM' UNION ALL
SELECT '01-11-2021', '56896' , 163 ,'CIS' UNION ALL
SELECT '01-11-2021', '56896' , 16 ,'PTD' UNION ALL
SELECT '01-11-2021', '56896' , 187 ,'ELM'

--PIVOT  
DECLARE @Colslist VARCHAR(MAX)  
DECLARE @Cols TABLE (Head VARCHAR(MAX))  
  
INSERT @Cols (Head)  
SELECT DISTINCT due_Date  
FROM #bil
  
SELECT @ColsList = COALESCE(@ColsList + ',[', '[') + Head + ']'  
FROM @Cols t  
 
EXEC ('SELECT *  
FROM   
(  
        SELECT a.code, a.payment, a.due_date
        FROM #bil a 
) t  
PIVOT (sum(payment) FOR due_Date IN (' + @ColsList + ')) PVT')
3

hi

in your Output 2 that you put .... There is 25896

do not see it in your Sample Data