मैं नीचे दिए गए नमूने से निम्नलिखित परिणाम प्राप्त करने के लिए संघर्ष कर रहा हूं Fiddle

नतीजा:

enter image description here

स्रोत तालिकाएँ:

enter image description here

बेला

अन्य कॉलम बनाने के लिए बजट और पूर्वानुमान आईडी की अधिकतम गणना ढूंढकर प्रत्येक RecordID के लिए एक पंक्ति बनाने का विचार है (यदि उस बजट या पूर्वानुमान के लिए कोई मूल्य नहीं है तो कॉलम सेल खाली छोड़ दें) आईडी मौजूद है)।

मैंने PIVOT फ़ंक्शन के साथ प्रयास किया लेकिन एक अच्छे परिणाम के करीब भी नहीं पहुंच सका।

अपडेट करें: कृपया नीचे दी गई छवि देखें जहां मैंने अपेक्षित आउटपुट को समझाने की कोशिश की:

शब्दों में: प्रत्येक के लिए एक RecordID से संबंधित बजट आईडी BDateअलग कॉलम बनाएं >, परिणाम (प्रतिशत * BAरिकॉर्ड तालिका की मात्रा) और स्थिति

मेरे उदाहरण में RecordID 55 की बजट तालिका में दो प्रविष्टियां हैं - इसलिए प्रत्येक तारीख को दिखाने के लिए 2x3 कॉलम की आवश्यकता है , परिणाम और स्थिति अपने स्वयं के कॉलम में इस रिकॉर्ड आईडी के लिए एकल पंक्ति में।

चूंकि RecordID 77 की बजट तालिका में सबसे (तीन) प्रविष्टियां हैं, इसलिए इसका उपयोग सभी पंक्तियों में 3x3 कॉलम बनाने के लिए किया जाता है।

वही पूर्वानुमान के लिए जाता है।

output

मुझे आशा है आप मेरी मदद कर सकते हैं।

धन्यवाद।

6
ratanmalko 4 जुलाई 2018, 16:18

3 जवाब

सबसे बढ़िया उत्तर

शुभ दिवस,

ध्यान दें! मेरा लक्ष्य पाठक को सीखने में मदद करना है न कि उसके गृहकार्य के समाधान के रूप में अंतिम प्रश्न देना। इसलिए मैं समाधान को दो चरणों में प्रस्तुत करूंगा, और मैं गतिशील समाधान में कई "प्रिंट" कमांड जोड़ूंगा, इसलिए पाठक के पास काम में मध्यवर्ती चरण की जांच करने का विकल्प होगा।

ओपी द्वारा प्रदान किया गया डीडीएल + डीएमएल:

DROP TABLE IF EXISTS Budget;
CREATE TABLE Budget
    (BudgetID int, RecordID int, BDate date,Percentage int, [Status] varchar(50));
INSERT INTO Budget
    (BudgetID, RecordID, BDate,Percentage,Status)
VALUES
    (1, 55, '2017-01-01', 60, 'ordered'),
    (2, 55, '2017-03-24', 40, 'ordered'),
    (3, 66, '2018-08-15', 100, 'invoiced'),
    (4, 77, '2018-12-02', 25, 'paid'),
    (5, 77, '2018-09-10', 35, 'ordered'),
    (6, 77, '2019-07-13', 40, 'ordered')
GO

DROP TABLE IF EXISTS Forecast;
CREATE TABLE Forecast
    (ForecastID int, RecordID int, FDate date, Percentage int);
INSERT INTO Forecast
    (ForecastID, RecordID, FDate,Percentage)
VALUES
    (1, 55, '2020-12-01', 100),
    (2, 77, '2023-05-17', 25),
    (3, 77, '2024-11-28', 75)
GO

DROP TABLE IF EXISTS Records;
CREATE TABLE Records
    (RecordID int, BAmount int, FAmount int, Name varchar(40), Description varchar(40) )
;
INSERT INTO Records
   (RecordID, BAmount,FAmount,Name, Description)
VALUES
    (55, 15000, 33000, 'Prod1', 'Desc1' ),
     (66, 22000, 17500, 'Prod2', 'Desc2' ),
    (77, 40000, 44000, 'Prod3', 'Desc3' )
GO

select * from Budget
select * from Forecast
select * from Records

आइए पहले एक सरल स्थैतिक समाधान दिखाएं

यह ज्ञान इस आधार पर है कि हमारे पास बजट तालिका में प्रत्येक रिकॉर्ड आईडी के लिए अधिकतम तीन पंक्तियां हैं और पूर्वानुमान तालिका में प्रत्येक रिकॉर्ड आईडी के लिए अधिकतम दो पंक्तियां हैं। यह गतिशील समाधान को समझने में मदद करेगा जो मैं आगे दिखाऊंगा

;With CteBudget as (
    select 
        b.BDate, b.BudgetID, b.Percentage, b.RecordID, b.Status
        ,RN = ROW_NUMBER() OVER (partition by b.RecordID order by b.BudgetID)
    from Budget b
),
CteForecast as (
    select 
        f.FDate, f.ForecastID, f.Percentage, f.RecordID
        ,RN = ROW_NUMBER() OVER (partition by f.RecordID order by f.ForecastID)
    from Forecast f
)
select 
    r.RecordID, r.Name, r.Description, 

    b1.BDate BDate1, (b1.Percentage * r.BAmount)/100 BResult1, b1.Status BStatus1, 
    b2.BDate BDate2, (b2.Percentage * r.BAmount)/100 BResult2, b2.Status BStatus2,
    b3.BDate BDate3, (b3.Percentage * r.BAmount)/100 BResult3, b3.Status BStatus3, 

    f1.FDate FDate1, (f1.Percentage * r.BAmount)/100 FResult1,
    f2.FDate FDate2, (f2.Percentage * r.BAmount)/100 FResult2

from Records r
left join CteBudget b1 on r.RecordID = b1.RecordID and b1.RN = 1
left join CteBudget b2 on r.RecordID = b2.RecordID and b2.RN = 2
left join CteBudget b3 on r.RecordID = b3.RecordID and b3.RN = 3
left join CteForecast f1 on r.RecordID = f1.RecordID and f1.RN = 1
left join CteForecast f2 on r.RecordID = f2.RecordID and f2.RN = 2
--where r.RecordID = 77
GO

नोट! स्थिर समाधान के लिए और बिना अनुक्रमणिका के (जैसा कि मैं अंत में जोड़ूंगा), उपरोक्त समाधान प्रदर्शन के संबंध में बहुत खराब है, लेकिन एक बार जब हम सही अनुक्रमणिका जोड़ते हैं और एक गतिशील समाधान के लिए आधार के रूप में यह विकल्प अच्छी तरह से फिट होना चाहिए।

अब हम गतिशील समाधान प्रस्तुत कर सकते हैं।

-- Get number of columns
Declare @NumBudget tinyint
Declare @NumForecast tinyint
SELECT @NumBudget = MAX(C) FROM (
    select COUNT(RecordID) C
    from Budget
    GROUP BY RecordID
) t
SELECT @NumForecast = MAX(C) FROM (
    select COUNT(RecordID) C
    from Forecast
    GROUP BY RecordID
) t
---------------------------------------------
DECLARE @SQLString1 nvarchar(MAX) = '';
DECLARE @SQLString2 nvarchar(MAX) = '';
DECLARE @loop int = 1;
WHILE @loop <= @NumBudget BEGIN 
    SET @SQLString1 = @SQLString1 + N'
    b' + CONVERT(VARCHAR(2),@loop) + '.BDate BDate' + CONVERT(VARCHAR(2),@loop) + ', (b' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 BResult' + CONVERT(VARCHAR(2),@loop) + ', b' + CONVERT(VARCHAR(2),@loop) + '.Status BStatus' + CONVERT(VARCHAR(2),@loop) + ', '

    SET @SQLString2 = @SQLString2 + N'
    left join CteBudget b' + CONVERT(VARCHAR(2),@loop) + ' on r.RecordID = b' + CONVERT(VARCHAR(2),@loop) + '.RecordID and b' + CONVERT(VARCHAR(2),@loop) + '.RN = 1'

    SET @loop = @loop + 1
END
SET @loop = 1

WHILE @loop <= @NumForecast BEGIN  
    SET @SQLString1 = @SQLString1 + N'
    f' + CONVERT(VARCHAR(2),@loop) + '.FDate FDate' + CONVERT(VARCHAR(2),@loop) + ', (f' + CONVERT(VARCHAR(2),@loop) + '.Percentage * r.BAmount)/100 FResult' + CONVERT(VARCHAR(2),@loop) + ','

    SET @SQLString2 = @SQLString2 + N'
    left join CteForecast f' + CONVERT(VARCHAR(2),@loop) + ' on r.RecordID = f' + CONVERT(VARCHAR(2),@loop) + '.RecordID and f' + CONVERT(VARCHAR(2),@loop) + '.RN = 1'

    SET @loop = @loop + 1
END
SET @SQLString1 = STUFF (@SQLString1, LEN(@SQLString1) , 1 , '')  
PRINT '/************************************************/'
PRINT @SQLString1
PRINT @SQLString2
PRINT '/************************************************/'

DECLARE @SQLString nvarchar(MAX);
SET @SQLString = N'
;With CteBudget as (
    select 
        b.BDate, b.BudgetID, b.Percentage, b.RecordID, b.Status
        ,RN = ROW_NUMBER() OVER (partition by b.RecordID order by b.BudgetID)
    from Budget b
),
CteForecast as (
    select 
        f.FDate, f.ForecastID, f.Percentage, f.RecordID
        ,RN = ROW_NUMBER() OVER (partition by f.RecordID order by f.ForecastID)
    from Forecast f
)
select 
    r.RecordID, r.Name, r.Description, 
'
+ @SQLString1
+ N'
from Records r'
+ @SQLString2

print @SQLString

EXECUTE sp_executesql @SQLString
GO

महत्वपूर्ण! जरूरी नहीं कि यह समाधान सबसे अच्छा प्रदर्शन देने वाला हो, बल्कि ऐसा समाधान हो जो अनुसरण करने और समझने में सबसे आसान हो। उत्पादन पर एक बार जब हमारे पास वास्तविक डीडीएल + डीएमएल होगा और सर्वर के पास आंकड़े होंगे, तो हम प्रदर्शन में सुधार करने में सक्षम होंगे और हमारे विशिष्ट मामले के लिए सबसे अच्छा समाधान चुना जाएगा।

इंडेक्स

ध्यान दें! उपरोक्त समाधान से डेटा की बहुत अधिक छंटाई हो सकती है, और यहां सही अनुक्रमणिका का होना अत्यंत महत्वपूर्ण है! कई अलग-अलग विकल्पों का परीक्षण करना और सर्वश्रेष्ठ का चयन करना महत्वपूर्ण है।

फ़ोरम के लिए (या जिसे आप स्टैकओवरफ़्लो कहते हैं, जो मेरी राय में एक चर्चा मंच इंटरफ़ेस नहीं बल्कि प्रश्नोत्तर इंटरफ़ेस है), मैं क्लस्टर इंडेक्स बनाने के लिए प्रश्न जोड़ता हूं जो मुझे लगता है कि आपके पास उत्पादन में है, और एक वैकल्पिक गैर-अनुक्रमित सूचकांक जो आपको परीक्षण करना चाहिए (मैंने अन्य विकल्पों का परीक्षण नहीं किया है और यह मेरे दिमाग में पहली बार आया है, इसलिए वास्तविक डीडीएल + डीएमएल के साथ सही इंडेक्स की जांच करने की अनुशंसा की जाती है)।

-- CLUSTERED INDEX
CREATE CLUSTERED INDEX IX_Budget_BudgetID
    ON dbo.Budget (BudgetID);   
GO 
CREATE CLUSTERED INDEX IX_Forecast_ForecastID
    ON dbo.Forecast (ForecastID);   
GO 
CREATE CLUSTERED INDEX IX_Records_RecordID  
    ON dbo.Records (RecordID);   
GO 

-- NONCLUSTERED INDEX
CREATE NONCLUSTERED INDEX NX_Budget_RecordID_BudgetID
    ON dbo.Budget (RecordID,BudgetID);   
GO 
CREATE NONCLUSTERED INDEX NX_Forecast_RecordID_ForecastID
    ON dbo.Forecast (RecordID,ForecastID);   
GO 
CREATE NONCLUSTERED INDEX NX_Records_RecordID_RecordID  
    ON dbo.Records (RecordID);   
GO 
6
Ronen Ariely 10 जुलाई 2018, 01:51

मुझे लगता है कि मैं करूँगा:

select Max(BudgetId) as BudgetID, RecordID
into #MBudget
from Budget
group by RecordID

select B.* 
into #MaxB
from #MBudget M
inner join Budget B
on M.BudgetID = B.BudgetID
and M.RecordID = B.RecordID

--The above will then only have the "maximum" BudgetID data from the table.

-- You need to then do the same with the Forecast table

select Max(ForecastId) as ForecastID, RecordID
into #MForecast
from Forecast
group by RecordID

select F.* 
into #MaxF
from #MForecast M
inner join Forecast F
on M.ForecastID = F.ForecastID
and M.RecordID = F.RecordID

-- Join them together on the RecordID

select *  -- you will need to pick the required fields
from #MaxF F
inner join #MaxB B 
on F.RecordID = B.RecordID
inner join Record R
on F.RecordID = R.RecordID

मुझे लगता है कि यह आपको वहां ले जाएगा, मैं बेला का बहुत बड़ा प्रशंसक नहीं हूं और मेरे दिमाग में भी ऐसा ही हुआ।

मैं लोगों के साथ थोड़ा सहमत हूं, आप स्पष्ट हो सकते थे लेकिन आशा है कि यह आपको वहां ले जाएगा जहां आपको होना चाहिए।

3
AB_87 5 जुलाई 2018, 10:44

अंत में, हम कई जॉइन के रूप में डायनामिक क्वेरी बनाते हैं।

इनपुट तालिकाएं:

CREATE TABLE #Budget (BudgetID int, RecordID int, BDate date,Percentage int, [Status] varchar(50));
INSERT INTO #Budget(BudgetID, RecordID, BDate,Percentage,Status)
VALUES(1, 55, '2017-01-01', 60, 'ordered'),(2, 55, '2017-03-24', 40, 'ordered'),(3, 66, '2018-08-15', 100, 'invoiced'),(4, 77, '2018-12-02', 25, 'paid'),(5, 77, '2018-09-10', 35, 'ordered'),(6, 77, '2019-07-13', 40, 'ordered')

CREATE TABLE #Forecast(ForecastID int, RecordID int, FDate date, Percentage int)
INSERT INTO #Forecast(ForecastID, RecordID, FDate,Percentage)
VALUES(4, 77, '2018-07-18', 24),(1, 55, '2020-12-01', 100),(2, 77, '2023-05-17', 25),(3, 77, '2024-11-28', 75)

CREATE TABLE #Records(RecordID int, BAmount int, FAmount int, Name varchar(40), Description varchar(40) )
INSERT INTO #Records(RecordID, BAmount,FAmount,Name, Description)
VALUES(55, 15000, 33000, 'Prod1', 'Desc1' ),(66, 22000, 17500, 'Prod2', 'Desc2' ),(77, 40000, 44000, 'Prod3', 'Desc3' )

अंतिम शामिल होने की क्वेरी

 select * from (select r1.RecordID, f1.FDate fdate1, b1.BDate bdate1
 , ROW_NUMBER() over(partition by r1.recordid order by (select NULL)) rn from #Records r1
 join #Forecast f1
 on r1.RecordID = f1.RecordID
 and r1.RecordID = 77
 join #Budget b1
 on r1.RecordID = b1.RecordID
)t1 join (select r2.RecordID, f2.FDate fdate2, b2.BDate bdate2
 , ROW_NUMBER() over(partition by r2.recordid order by (select NULL)) rn from #Records r2
 join #Forecast f2
 on r2.RecordID = f2.RecordID
 and r2.RecordID = 77
 join #Budget b2
 on r2.RecordID = b2.RecordID
)t2 on t1.RecordID = t2.RecordID and t1.rn < t2.rn join (select r3.RecordID, f3.FDate fdate3, b3.BDate bdate3
 , ROW_NUMBER() over(partition by r3.recordid order by (select NULL)) rn from #Records r3
 join #Forecast f3
 on r3.RecordID = f3.RecordID
 and r3.RecordID = 77
 join #Budget b3
 on r3.RecordID = b3.RecordID
)t3 on t2.RecordID = t3.RecordID and t2.rn < t3.rn join (select r4.RecordID, f4.FDate fdate4, b4.BDate bdate4
 , ROW_NUMBER() over(partition by r4.recordid order by (select NULL)) rn from #Records r4
 join #Forecast f4
 on r4.RecordID = f4.RecordID
 and r4.RecordID = 77
 join #Budget b4
 on r4.RecordID = b4.RecordID
)t4 on t3.RecordID = t4.RecordID and t3.rn < t4.rn join (select r5.RecordID, f5.FDate fdate5, b5.BDate bdate5
 , ROW_NUMBER() over(partition by r5.recordid order by (select NULL)) rn from #Records r5
 join #Forecast f5
 on r5.RecordID = f5.RecordID
 and r5.RecordID = 77
 join #Budget b5
 on r5.RecordID = b5.RecordID
)t5 on t4.RecordID = t5.RecordID and t4.rn < t5.rn join (select r6.RecordID, f6.FDate fdate6, b6.BDate bdate6
 , ROW_NUMBER() over(partition by r6.recordid order by (select NULL)) rn from #Records r6
 join #Forecast f6
 on r6.RecordID = f6.RecordID
 and r6.RecordID = 77
 join #Budget b6
 on r6.RecordID = b6.RecordID
)t6 on t5.RecordID = t6.RecordID and t5.rn < t6.rn join (select r7.RecordID, f7.FDate fdate7, b7.BDate bdate7
 , ROW_NUMBER() over(partition by r7.recordid order by (select NULL)) rn from #Records r7
 join #Forecast f7
 on r7.RecordID = f7.RecordID
 and r7.RecordID = 77
 join #Budget b7
 on r7.RecordID = b7.RecordID
)t7 on t6.RecordID = t7.RecordID and t6.rn < t7.rn join (select r8.RecordID, f8.FDate fdate8, b8.BDate bdate8
 , ROW_NUMBER() over(partition by r8.recordid order by (select NULL)) rn from #Records r8
 join #Forecast f8
 on r8.RecordID = f8.RecordID
 and r8.RecordID = 77
 join #Budget b8
 on r8.RecordID = b8.RecordID
)t8 on t7.RecordID = t8.RecordID and t7.rn < t8.rn join (select r9.RecordID, f9.FDate fdate9, b9.BDate bdate9
 , ROW_NUMBER() over(partition by r9.recordid order by (select NULL)) rn from #Records r9
 join #Forecast f9
 on r9.RecordID = f9.RecordID
 and r9.RecordID = 77
 join #Budget b9
 on r9.RecordID = b9.RecordID
)t9 on t8.RecordID = t9.RecordID and t8.rn < t9.rn

सबसे पहले ओपी को आईपी टेबल और ऊपर की क्वेरी से सत्यापित करें। यदि उत्तर स्वीकार कर लिया जाता है, तो आप निम्नलिखित एसपी का प्रयोग करेंगे।

एसपी 1

exec rownumber

create procedure rownumber as                                           --1st sp.
declare @r int = 1, @select nvarchar(100) = 'select * from ', @count int, @finalquery nvarchar(max) = N'', @out nvarchar(max)
begin
 set @count = (select Count(*) from (select r.RecordID, f1.FDate fdate1, b1.BDate bdate1
  , ROW_NUMBER() over(partition by r.recordid order by (select NULL)) rn from #Records r
  join #Forecast f1
  on r.RecordID = f1.RecordID
  and r.RecordID = 77
  join #Budget b1
  on r.RecordID = b1.RecordID
  )t
 )
 if @count > 2
 begin
  while @count >= @r
  begin
   exec multyJoin @r, @out output
   set @finalquery += @out
   set @r = @r + 1
  end
 end
 else begin
  select * from (
   select r.RecordID, f1.FDate fdate1, b1.BDate bdate1
   , ROW_NUMBER() over(partition by r.recordid order by (select NULL)) rn from #Records r
   join #Forecast f1
   on r.RecordID = f1.RecordID
   and r.RecordID = 77
   join #Budget b1
   on r.RecordID = b1.RecordID
  )t
 end
 set @finalquery = stuff(@finalquery,1, 6,@select)
 set @finalquery = REPLACE(@finalquery, 'on t0.RecordID = t1.RecordID and t0.rn < t1.rn ','')
 print @finalquery
 exec sp_executesql @finalquery
end

एसपी 2

create procedure multyJoin (@r int, @join varchar(max) output) as       --2nd sp. which is called form below sp
declare @cond nvarchar(100), @rvc varchar(3) = convert(int, @r)     ----row number of current in varchar
, @rvp varchar(3) = convert(int, @r-1)      ----row number of previous in varchar
begin

 set @join = ' join (select r'+@rvc+'.RecordID, f'+@rvc+'.FDate fdate'+@rvc+', b'+@rvc+'.BDate bdate'+@rvc+  ---Here add your columns as like as 'Percentage'+@rvc+' * '+@rvc+'BAmount'
  ', ROW_NUMBER() over(partition by r'+@rvc+'.recordid order by (select NULL)) rn from #Records r'+@rvc+'
  join #Forecast f'+@rvc+'
  on r'+@rvc+'.RecordID = f'+@rvc+'.RecordID
  and r'+@rvc+'.RecordID = 77
  join #Budget b'+@rvc+'
  on r'+@rvc+'.RecordID = b'+@rvc+'.RecordID
 )t'+@rvc+
 ' on t'+@rvp+'.RecordID = t'+@rvc+'.RecordID and t'+@rvp+'.rn < t'+@rvc+'.rn'  --Here multiple join has been created. Each table contains one row
end
3
Pugal 19 जुलाई 2018, 14:14