table
ID Name
1 jayaraj
2 Bala
3 sridhar
table 2
Id Mark
1 100
1 500
1 300
2 100
2 200
1 994
1 444
3 344
3 387
Need a query to get data like this
ID Name mark1 mark2 mark3 mark 4 mark 5
1 jayaraj 100 500 300 994 444
2 Bala 100 200
3 sridhar 344 387
Hi
I have used PIVOT to get it
Please note in Create Data
I have added row number
drop table #name
go
create table #name (
ID int ,
Name varchar(100)
)
go
insert into #name select 1, 'jayaraj'
insert into #name select 2, 'Bala'
insert into #name select 3, 'sridhar'
go
drop table #marks
go
create table #Marks (
Id int,
Mark int
)
go
insert into #Marks select 1, 100
insert into #Marks select 1, 500
insert into #Marks select 1, 300
insert into #Marks select 2, 100
insert into #Marks select 2, 200
insert into #Marks select 1, 994
insert into #Marks select 1, 444
insert into #Marks select 3, 344
insert into #Marks select 3, 387
go
drop table def
go
select ROW_NUMBER() OVER(partition by id order by id) as rn,a.ID,a.Name,b.Mark into def from #name a join #Marks b
on a.ID = b.Id
go
SELECT id,
NAME,
[1],
[2],
[3],
[4],
[5]
FROM def
PIVOT ( Max(mark)
FOR [rn] IN ([1],
[2],
[3],
[4],
[5] ) ) AS p
ORDER BY 1,
2
try a dynamic pivot
Drop table if exists #t1
Drop table if exists #t2
Create table #t1 (id int, name varchar(20))
insert into #t1 values
(1,'jayaraj'),
(2,'Bala'),
(3,'sridhar')
Create table #t2 (colno int identity(1,1), id int, Mark int)
insert into #t2 values
(1 ,100),
(1 ,500),
(1 ,300),
(2 ,100),
(2 ,200),
(1, 994),
(1 ,444),
(3 ,344),
(3 ,387)
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT distinct ', Mark' + cast(RowNum as nvarchar(10))
FROM (
select row_number() over (partition by t2.id order by Colno) RowNum
from #t2 t2
) z
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT ID, Name, ' + @cols + ' from
(
select t2.Id, Name, mark, ''Mark'' + cast(row_number() over (partition by t2.id order by Colno) as nvarchar(10)) RowNum
from #t2 t2
join #t1 t1
on t1.id = t2.id
) x
pivot
(
max(Mark)
for RowNum in (' + @cols + ')
) p
order by ID '
execute(@query)DECLARE @cols AS nvarchar(max),
@query AS nvarchar(max)
DECLARE @startdate datetime = '2018-09-07'
DECLARE @enddate datetime = '2018-09-17'
DECLARE @RQST_SYS nvarchar(max) = 'KBT';
SELECT
@cols = STUFF((SELECT
',' + QUOTENAME(CASE
WHEN d.col = 'Montly_payment' THEN col + CAST(
seq AS
varchar(
10))
ELSE col + CAST(seq AS varchar(10))
END)
FROM (SELECT DISTINCT
ROW_NUMBER()
OVER (
PARTITION BY pmt.sesn_id
ORDER BY mpa_calc_mo_pmt_amt)
seq
FROM pmt(nolock) pmt
INNER JOIN [rpmt_schd_rslt] RPT
ON pmt.sesn_id = RPT.sesn_id
AND RPT.[rqst_sys_cd] =
@RQST_SYS
AND RPT.[crea_ts] BETWEEN
@startdate AND @enddate) t
CROSS APPLY (SELECT
'Montly_payment',
1
UNION ALL
SELECT
'Montly_payment_Due_Date',
2
UNION ALL
SELECT
'Montly_payment_Type',
3
UNION ALL
SELECT
'UN_PAID_ACCURIED_INTREST_AMOUNT',
4
UNION ALL
SELECT
'OUTSTANDING_PRINCIPAL_AMOUNT',
5
UNION ALL
SELECT
'TOTAL_INTREST_AMOUNT',
6
UNION ALL
SELECT
'TOTAL_PAID_AMOUNT',
7) d (col, so)
FOR xml PATH (''), TYPE)
.value('.', 'NVARCHAR(MAX)'), 1, 1,
'')
PRINT @cols
SET @query = 'SELECT
RQST_SYS_CD,
CREA_TS
SESN_ID,
CREA_ID,
SNR_CNT,
DSCL_ANLS_CD,
CALC_RULE_CD,
DSCL_CD,
SESN_SNR_CD,
SNR_PRCG_CD,
LOAN_PGM_CD,
LOAN_PGM_PRDT_CD,
RPMT_PRD_BGN_DT,
LOAN_FRST_PMT_DUE_DT,
FRST_LVL_PMT_DT,
FNL_LVL_PMT_DT,
INT_CPTZ_FREQ_CD,
RPMT_OPTN_CD,
INTM_TERM_PMT_CNT,
RPMT_TERM_MO_CNT,
TOT_LOAN_RQST_AMT,
MPA_CALC_ORIG_FEE_AMT,
MPA_CALC_RPMT_FEE_AMT,
MPA_CALC_FIN_CHRG_AMT,
MPA_CALC_TOT_PD_AMT,
MPA_CALC_TOT_FIN_AMT,
MPA_CALC_TOT_INTM_INT_ACCR_AMT,
MPA_CALC_TOT_INTM_PD_AMT,
MPA_CALC_TOT_RPMT_LOAN_BAL_AMT,
ERR_CD,
APR_CALC_CD,
BILL_CYCL_DT,
RQST_SYS_REF_NBR,
FDR_ACCT_NBR,
CHLD_LOAN_PGM_CD,
CHLD_LOAN_PGM_PRDT_CD,
' + @cols + '
from
(
select
t.RQST_SYS_CD,
t.CREA_ID,
t.CREA_TS,
t.SNR_CNT,
t.DSCL_ANLS_CD,
t.CALC_RULE_CD,
t.DSCL_CD,
t.SESN_SNR_CD,
t.SNR_PRCG_CD,
t.LOAN_PGM_CD,
t.LOAN_PGM_PRDT_CD,
t.RPMT_PRD_BGN_DT,
t.LOAN_FRST_PMT_DUE_DT,
t.FRST_LVL_PMT_DT,
t.FNL_LVL_PMT_DT,
t.INT_CPTZ_FREQ_CD,
t.RPMT_OPTN_CD,
t.INTM_TERM_PMT_CNT,
t.RPMT_TERM_MO_CNT,
t.TOT_LOAN_RQST_AMT,
t.MPA_CALC_ORIG_FEE_AMT,
t.MPA_CALC_RPMT_FEE_AMT,
t.MPA_CALC_FIN_CHRG_AMT,
t.MPA_CALC_TOT_PD_AMT,
t.MPA_CALC_TOT_FIN_AMT,
t.MPA_CALC_TOT_INTM_INT_ACCR_AMT,
t.MPA_CALC_TOT_INTM_PD_AMT,
t.MPA_CALC_TOT_RPMT_LOAN_BAL_AMT,
t.ERR_CD,
t.APR_CALC_CD,
t.BILL_CYCL_DT,
t.RQST_SYS_REF_NBR,
t.FDR_ACCT_NBR,
t.CHLD_LOAN_PGM_CD,
t.CHLD_LOAN_PGM_PRDT_CD,
t.SESN_ID,
col = case
when c.col = ''Montly_payment'' then col+cast(seq as varchar(10))
else col+cast(seq as varchar(10))
end,
value
from
(
select
RPT.RQST_SYS_CD,
RPT.CREA_TS,
RPT.CREA_ID,
RPT.SNR_CNT,
RPT.DSCL_ANLS_CD,
RPT.CALC_RULE_CD,
RPT.DSCL_CD,
RPT.SESN_SNR_CD,
RPT.SNR_PRCG_CD,
RPT.LOAN_PGM_CD,
RPT.LOAN_PGM_PRDT_CD,
RPT.RPMT_PRD_BGN_DT,
RPT.LOAN_FRST_PMT_DUE_DT,
RPT.FRST_LVL_PMT_DT,
RPT.FNL_LVL_PMT_DT,
RPT.INT_CPTZ_FREQ_CD,
RPT.RPMT_OPTN_CD,
RPT.INTM_TERM_PMT_CNT,
RPT.RPMT_TERM_MO_CNT,
RPT.TOT_LOAN_RQST_AMT,
RPT.MPA_CALC_ORIG_FEE_AMT,
RPT.MPA_CALC_RPMT_FEE_AMT,
RPT.MPA_CALC_FIN_CHRG_AMT,
RPT.MPA_CALC_TOT_PD_AMT,
RPT.MPA_CALC_TOT_FIN_AMT,
RPT.MPA_CALC_TOT_INTM_INT_ACCR_AMT,
RPT.MPA_CALC_TOT_INTM_PD_AMT,
RPT.MPA_CALC_TOT_RPMT_LOAN_BAL_AMT,
RPT.ERR_CD,
RPT.APR_CALC_CD,
RPT.BILL_CYCL_DT,
RPT.RQST_SYS_REF_NBR,
RPT.FDR_ACCT_NBR,
RPT.CHLD_LOAN_PGM_CD,
RPT.CHLD_LOAN_PGM_PRDT_CD,
pmt.SESN_ID,
pmt.MPA_CALC_MO_PMT_AMT,
CAST( [pmt.MPA_CALC_PMT_DT] AS varchar) MPA_CALC_PMT_DT1,
pmt.MPA_CALC_PMT_TYPE_CD,
pmt.UNPD_ACCR_INT_AMT,
pmt.OUTS_PRIN_AMT,
pmt.TOT_INT_AMT,
pmt.TOT_PD_AMT,
row_number() over(partition by pmt.SESN_ID
order by MPA_CALC_MO_PMT_AMT) seq
from PMT(NOlock) pmt inner join [RPMT_SCHD_RSLT] RPT on pmt.SESN_ID = RPT.SESN_ID and RPT.RQST_SYS_CD = ''KBT'' and RPT.[CREA_TS] BETWEEN ''2018-09-07'' and ''2018-09-17''
) t
cross apply
(
select ''Montly_payment'', MPA_CALC_MO_PMT_AMT union all
select ''Montly_payment_Due_Date'', MPA_CALC_PMT_DT1 union all
select ''Montly_payment_Type '', MPA_CALC_PMT_TYPE_CD union all
select ''UN_PAID_ACCURIED_INTREST_AMOUNT'', UNPD_ACCR_INT_AMT union all
select ''OUTSTANDING_PRINCIPAL_AMOUNT'', OUTS_PRIN_AMT union all
select ''TOTAL_INTREST_AMOUNT'', TOT_INT_AMT union all
select ''TOTAL_PAID_AMOUNT'', TOT_PD_AMT
) c (col, value)
) x
pivot
(
max(value)
for col in ('
EXECUTE Sp_executesql @query;
Following is the query i generated.
Having some issues in convert the date to varchar. MPA_CALC_PMT_DT is Date.
Having issues in printing the date
CAST( [pmt.MPA_CALC_PMT_DT] AS varchar(10))drop table #temp
create table #temp
(
ID int,
Name varchar(50),
Mark1 int,
Mark2 int,
Mark3 int,
Mark4 int,
Mark5 int
)
insert into #temp
select * from(
select row_number() over(partition by a.Name order by a.ID) as rownumber,a.ID,a.Name,s.mark from names a
inner join marks s
on a.ID = s.id
) m
pivot
(
sum(mark)
for [rownumber] in ([1],[2],[3],[4],[5])
)as pvt
select * from #temp
order by ID