select بازگشتی کاملا حرفه ای (خودتون رامحک بزنید)

[مرتضی حمزه ئی]

با سلام خدمت اساتید یک فایل دارم که اطلاعات بیمه تکمیلی پرسنل داخل اون هست که ساختار اون بصورت زیر است
([PER_NO] ,[NAMEBABY] ,[FAMILYBABY],[RELCODE] ,[BIRTHDATE])
که شماره پرسنل دراون تکرار میشه ونام ونام خانوادگی افراد بیمه تکمیلی اون با تاریخ تولد داخل فایل baby قرار میگیره فیلد relcode هم مشخص میکنه که فرد همسر یا دختر وپسر یا پدر مادر پرسنل است حالا موقعی که میخوام گزارش بسازم گزارش طبق فرمت پیوست بصورت خطی است یعنی شماره پرسنل در یک ردیف وتمام افرادتحت بیمه اون پرسنل توی اون سطر قرار میگیرن تعداد فرزند حداکثر 6 است وپدرمادروهمسر نیز همراه با تاریخ تولد اونها باید جلوی پرسنل قراربگیره سوالم اینه چطوری select بنویسم که بتونم داخل گزارش بنشونم

همه اطلاعات دریک فایل baby است ولی خروجی که میخوام بگیرم ازاطلاعات این فایل است با فرمت خاصی که درضمیمه است
وبا pivot هم نمیشه چون درگزارش به ازای هر relcode نام ونام خانوادگی بیمه شده با تاریخ تولدش راهمزمان میخوام زیرش داشته باشم وازتوابع AGGREGATE نمیتوانم استفاده کنم چون میخوام هم نام ونام خانوادگی وهم تاریخ تولد و relcode را به ازای افراد بیمه شده اون پرسنل کنارهم داشته باشم
من با یک مثال ساختار را دوباره توضیح میدم :
per_no=740325,relcode=3,namebaby=maryam,familybaby =alavi,datebaby=80/01/12
per_no=740325,relcode=3,namebaby=mahsa,familybaby= alavi,datebaby=85/01/14
per_no=740325,relcode=1,namebaby=mahnaz,familybaby =ahmady,datebaby=55/10/22
per_no=740325,relcode=2,namebaby=abas,familybaby=a lavi,datebaby=40/11/18
فرض کنید که این اطلاعات برای یک پرسنل باشد relcode=3 یعنی فرزند وrelcode=1 همسر ,relcode=2 پدر استکه افراد بیمه تکمیلی شده پرسنل 740325 است بعضی از پرسنل شاید پدرمادرشان جزو بیمه تکمیلی نباشد وبعضی ها هم شاید فرزند نداشته باشند ولی حداکثر شش فرزند است حالا میخوام اطلاعات پرسنل را بصورت خطی زیر داشته باشم فایل ضمیمه خواهش میکنم کمک کنید باید بصورت خطی بتونم رکورد رابنشونم ازهمه اساتید خواهش میکنم کمک کنید تمرین خیلی خوبی واسه حرفه ایها به خیلی ها دادم هنوزجواب نگرفتم

[A.Farzin]

... کاملا حرفه ای (خودتون رامحک بزنید) ...
این سبک انشاء مناسب نیست!! ما نفهمیدیم شما سئوال دارید یا دنبال محک کردن دیگرانید؟

... یک فایل دارم ...
منظورتان از فایل همان جدول یا Table است؟

توضیحات زیادی نوشتید ولی باز هم منظورتان را نتوانستید بیان کنید.
حدس می‌زنم که دو تا جدول دارید در یکی از آنها اطلاعات نام و فامیل افراد است و در جدول دیگری ارتباط رکوردهای این جدول اولی (رابطه پدر و فرزندی و یا زن و شوهری) مشخص شده است
شما قصد دارید برخی از رکوردهای جدول اولی را از حالت رکورد درآورده و به عنوان ستون در جلوی رکوردهایی که RELCODE = 1 است نشان دهید.
این کار را با چند SELECT تودرتو می‌توانید انجام دهید و در SELECT داخلی با کمک CASE WHEN ... از رکوردها، ستون دلخواه بسازی و در نهایت با یک Aggrigate Select روی آن و در نهایت یک SELECT دیگر از آنها به نتیجه دلخواه برسی. من این کار را به تکرار در دیتابیس‌هایم انجام داده‌ام.
این مکانیزم همان روشی است که در pivot توسط SQL‌انجام می‌شود.
حالا با این مختصر شما سعی کن نتیجه مورد نظرت را بدست آوری.

[مرتضی حمزه ئی]

با سلام خدمت شما دوست عزیز من قصد جسارت نداشتم به هیچیک از اساتید فقط قصدم این بود که دوستان جوابهاشون رابدند وسوال هست چون خودم بلد نیستم
درمورد ساختار جدولم یک جدول بیشتر نیست فقط همون Baby اطلاعات بصورت رکورد به رکورد طبق مثال برای هرپرسنل وجوددارد یک پرسنل شاید دوتارکورد درجدول baby داشته باشه شاید هم 5 تا مهم اینه که من این اطلاعات رابراساس هرشماره پرسنل بصورت خطی درکنارهمدیگه داشته اصلا خروجی که میخوام رانگاه نکنید فقط اینها را که شامل کد پرسنل ,relcode وnamebaby,familybaby,datebaby است رامیخواهم دریک رکورد داشته باشم حالا اگه یک پرسنل 4 تا تحت پوشش داشته باشه تعداد فیلدهاش دراون رکورد 16 تا واسه اطلاعات تحت پوشش ویک شماره پرسنل خود خواهد بود اگه یک تحت پوشش داشته باشه 4 تا اطلاعات relcode وnamebaby,familybaby,datebaby مریوط به تحت پوشش ویک شماره پرسنل خودش
از تمامی دوستان کمک میخوام اگر گفتم محک بزنید واسه اینه که فکر کنم تمرین خوبی باشه واسه select های تودرتو و pivot
شرمنده تمام عزیزان

[Hamid.Kad]

جناب حمزه ئی، شما دقیقاً همین سوال رو در اینجا (http://www.barnamenevis.org/forum/showthread.php?t=199124) هم مطرح کرده اید و چیزی که من متوجه شدم اینه که شما اصلاً جوابها رو نخوندید و یقول جناب فرزین شاید بیشتر به دنبال محک دیگران باشید تا جواب گرفتن.
در هر صورت شما ابتدا باید بتونید خواسته خودتون رو دقیق مطرح کنید تا جواب بگیرید. دلیلی هم که برای عدم استفاده از pivot آورده اید منطقی نیست. شما اگر از یک جدول استفاده میکنید لطفاً بفرمائید ساختار جدولتون چجوریه. چون با اینکه تاکید میکنید که یک "فایل" دارید ولی با این ساختار نمیشه همچین کاری کرد. اگر هم از دو جدول استفاده میکنید ساختار هر دو جدول رو بگذارید تا جواب بگیرید

[Hamid.Kad]

جناب حمزه ئی، شما دقیقاً همین سوال رو در اینجا (http://www.barnamenevis.org/forum/showthread.php?t=199124) هم مطرح کرده اید و چیزی که من متوجه شدم اینه که شما اصلاً جوابها رو نخوندید و بقول جناب فرزین شاید بیشتر به دنبال محک دیگران باشید تا جواب گرفتن.
در هر صورت شما ابتدا باید بتونید خواسته خودتون رو دقیق مطرح کنید تا جواب بگیرید. دلیلی هم که برای عدم استفاده از pivot آورده اید منطقی نیست. شما اگر از یک جدول استفاده میکنید لطفاً بفرمائید ساختار جدولتون چجوریه. چون با اینکه تاکید میکنید که یک "فایل" دارید ولی با این ساختار نمیشه همچین کاری کرد. اگر هم از دو جدول استفاده میکنید ساختار هر دو جدول رو بگذارید تا جواب بگیرید

[A.Farzin]

با سلام

كدهاي زير شايد آن چيزي باشد كه شما مي‌خواهيد. توجه داشته باشيد كه علت طولاني بودن اين كدها به خاطر توليد يك ستون ID براي هر يك از 6 بچه و 2 همسر است. اگر شما اين ID را در جدولتان داشته باشيد، كوئري را بسيار مختصرتر هم مي‌شد نوشت.
ابتدا به کمک کد زیر جدول و داده‌های لازم را ایجاد کنید:

-- ساخت جدول
CREATE TABLE #tblBABY
(PER_NO int,
NAMEBABY nvarchar(20),
FAMILYBABY nvarchar(30),
RELCODE int,
BIRTHDATE datetime)
GO
-- ایجاد داده‌های لازم
-- Personely 101
INSERT INTO #tblBABY VALUES (101, 'پدر', 'Ahmadi', 2, getdate())
INSERT INTO #tblBABY VALUES (101, 'زن1', 'ZanAwalAhmadi', 1, getdate())
INSERT INTO #tblBABY VALUES (101, 'زن2', 'ZamDovomAhmadi', 1, getdate())
INSERT INTO #tblBABY VALUES (101, 'بچه1', 'Ahmadi', 3, getdate())
INSERT INTO #tblBABY VALUES (101, 'بچه2', 'Ahmadi', 3, getdate())
INSERT INTO #tblBABY VALUES (101, 'بچه3', 'Ahmadi', 3, getdate())
INSERT INTO #tblBABY VALUES (101, 'بچه4', 'Ahmadi', 3, getdate())
INSERT INTO #tblBABY VALUES (101, 'بچه5', 'Ahmadi', 3, getdate())
INSERT INTO #tblBABY VALUES (101, 'بچه6', 'Ahmadi', 3, getdate())
-- Personely 102
INSERT INTO #tblBABY VALUES (102, 'پدر', 'Alavi', 2, getdate())
INSERT INTO #tblBABY VALUES (102, 'زن1', 'ZanAwalAlavi', 1, getdate())
INSERT INTO #tblBABY VALUES (102, 'زن2', 'ZamDovomAlavi', 1, getdate())
INSERT INTO #tblBABY VALUES (102, 'بچه1', 'Alavi', 3, getdate())
INSERT INTO #tblBABY VALUES (102, 'بچه2', 'Alavi', 3, getdate())
INSERT INTO #tblBABY VALUES (102, 'بچه3', 'Alavi', 3, getdate())
INSERT INTO #tblBABY VALUES (102, 'بچه4', 'Alavi', 3, getdate())
INSERT INTO #tblBABY VALUES (102, 'بچه5', 'Alavi', 3, getdate())
INSERT INTO #tblBABY VALUES (102, 'بچه6', 'Alavi', 3, getdate())
-- Personely 103
INSERT INTO #tblBABY VALUES (103, 'پدر', 'Akbari', 2, getdate())
INSERT INTO #tblBABY VALUES (103, 'زن1', 'ZanAwalAkbari', 1, getdate())
INSERT INTO #tblBABY VALUES (103, 'زن2', 'ZamDovomAkbari', 1, getdate())
INSERT INTO #tblBABY VALUES (103, 'بچه1', 'Akbari', 3, getdate())
INSERT INTO #tblBABY VALUES (103, 'بچه2', 'Akbari', 3, getdate())
INSERT INTO #tblBABY VALUES (103, 'بچه3', 'Akbari', 3, getdate())
INSERT INTO #tblBABY VALUES (103, 'بچه4', 'Akbari', 3, getdate())
INSERT INTO #tblBABY VALUES (103, 'بچه5', 'Akbari', 3, getdate())
INSERT INTO #tblBABY VALUES (103, 'بچه6', 'Akbari', 3, getdate())
-- Personely 104
INSERT INTO #tblBABY VALUES (104, 'پدر', 'Erfani', 2, getdate())
INSERT INTO #tblBABY VALUES (104, 'زن1', 'ZanAwalErfani', 1, getdate())
INSERT INTO #tblBABY VALUES (104, 'زن2', 'ZamDovomErfani', 1, getdate())
INSERT INTO #tblBABY VALUES (104, 'بچه1', 'Erfani', 3, getdate())
INSERT INTO #tblBABY VALUES (104, 'بچه2', 'Erfani', 3, getdate())
INSERT INTO #tblBABY VALUES (104, 'بچه3', 'Erfani', 3, getdate())
INSERT INTO #tblBABY VALUES (104, 'بچه4', 'Erfani', 3, getdate())
INSERT INTO #tblBABY VALUES (104, 'بچه5', 'Erfani', 3, getdate())
INSERT INTO #tblBABY VALUES (104, 'بچه6', 'Erfani', 3, getdate())

SELECT * FROM #tblBABY

کوئری مورد نظر با استفاده از CASE WHEN ...

-- جواب با استفاده از CASE WHEN ...
SELECT PER_NO,
MIN(Nam21) AS Nam21, MIN(Fam21) AS Fam21,
MIN(Nam11) AS Nam11, MIN(Fam11) AS Fam11,
MIN(Nam12) AS Nam12, MIN(Fam12) AS Fam12,
MIN(Nam31) AS Nam31, MIN(Fam31) AS Fam31,
MIN(Nam32) AS Nam32, MIN(Fam32) AS Fam32,
MIN(Nam33) AS Nam33, MIN(Fam33) AS Fam33,
MIN(Nam34) AS Nam34, MIN(Fam34) AS Fam34,
MIN(Nam35) AS Nam35, MIN(Fam35) AS Fam35,
MIN(Nam36) AS Nam36, MIN(Fam36) AS Fam36
FROM (
SELECT PER_NO,
CASE WHEN RELCODE = 2 THEN NAMEBABY ELSE NULL END AS Nam21,
CASE WHEN RELCODE = 2 THEN FAMILYBABY ELSE NULL END AS Fam21,
CASE WHEN RELCODE = 1 AND PER_RNK = 1 THEN NAMEBABY ELSE NULL END AS Nam11,
CASE WHEN RELCODE = 1 AND PER_RNK = 1 THEN FAMILYBABY ELSE NULL END AS Fam11,
CASE WHEN RELCODE = 1 AND PER_RNK = 2 THEN NAMEBABY ELSE NULL END AS Nam12,
CASE WHEN RELCODE = 1 AND PER_RNK = 2 THEN FAMILYBABY ELSE NULL END AS Fam12,
CASE WHEN RELCODE = 3 AND PER_RNK = 1 THEN NAMEBABY ELSE NULL END AS Nam31,
CASE WHEN RELCODE = 3 AND PER_RNK = 1 THEN FAMILYBABY ELSE NULL END AS Fam31,
CASE WHEN RELCODE = 3 AND PER_RNK = 2 THEN NAMEBABY ELSE NULL END AS Nam32,
CASE WHEN RELCODE = 3 AND PER_RNK = 2 THEN FAMILYBABY ELSE NULL END AS Fam32,
CASE WHEN RELCODE = 3 AND PER_RNK = 3 THEN NAMEBABY ELSE NULL END AS Nam33,
CASE WHEN RELCODE = 3 AND PER_RNK = 3 THEN FAMILYBABY ELSE NULL END AS Fam33,
CASE WHEN RELCODE = 3 AND PER_RNK = 4 THEN NAMEBABY ELSE NULL END AS Nam34,
CASE WHEN RELCODE = 3 AND PER_RNK = 4 THEN FAMILYBABY ELSE NULL END AS Fam34,
CASE WHEN RELCODE = 3 AND PER_RNK = 5 THEN NAMEBABY ELSE NULL END AS Nam35,
CASE WHEN RELCODE = 3 AND PER_RNK = 5 THEN FAMILYBABY ELSE NULL END AS Fam35,
CASE WHEN RELCODE = 3 AND PER_RNK = 6 THEN NAMEBABY ELSE NULL END AS Nam36,
CASE WHEN RELCODE = 3 AND PER_RNK = 6 THEN FAMILYBABY ELSE NULL END AS Fam36
FROM (
SELECT CAST(CAST(PER_NO AS CHAR(3)) + CAST(RELCODE AS CHAR(1)) + CAST(PER_RNK AS CHAR(1)) AS int) AS UNID,
[NAMEBABY] + [FAMILYBABY] AS NF,
tmp0.* FROM (
SELECT PER_NO, RELCODE,
0 AS PER_RNK,
NAMEBABY, FAMILYBABY, BIRTHDATE
FROM #tblBABY
WHERE RELCODE = 2
UNION ALL
SELECT PER_NO, RELCODE,
NTILE(8) OVER(PARTITION BY PER_NO ORDER BY NAMEBABY ASC, FAMILYBABY ASC) AS PER_RNK,
NAMEBABY, FAMILYBABY, BIRTHDATE
FROM #tblBABY
WHERE RELCODE = 1
UNION ALL
SELECT PER_NO, RELCODE,
NTILE(8) OVER(PARTITION BY PER_NO ORDER BY NAMEBABY ASC, FAMILYBABY ASC) AS PER_RNK,
NAMEBABY, FAMILYBABY, BIRTHDATE
FROM #tblBABY
WHERE RELCODE = 3
) tmp0
) tmp1
) tmp2
GROUP BY PER_NO

Query مورد نظر با استفاده از Pivot:

-- جواب با استفاده از PIVOT
SELECT *
FROM (
SELECT PER_NO,
CAST(CAST(RELCODE AS CHAR(1)) + CAST(PER_RNK AS CHAR(1)) AS int) AS UNID,
[NAMEBABY] + ' ' + [FAMILYBABY] AS NF
FROM (
SELECT PER_NO, RELCODE,
0 AS PER_RNK,
NAMEBABY, FAMILYBABY, BIRTHDATE
FROM #tblBABY
WHERE RELCODE = 2
UNION ALL
SELECT PER_NO, RELCODE,
NTILE(8) OVER(PARTITION BY PER_NO ORDER BY NAMEBABY ASC, FAMILYBABY ASC) AS PER_RNK,
NAMEBABY, FAMILYBABY, BIRTHDATE
FROM #tblBABY
WHERE RELCODE = 1
UNION ALL
SELECT PER_NO, RELCODE,
NTILE(8) OVER(PARTITION BY PER_NO ORDER BY NAMEBABY ASC, FAMILYBABY ASC) AS PER_RNK,
NAMEBABY, FAMILYBABY, BIRTHDATE
FROM #tblBABY
WHERE RELCODE = 3
) tmp0
) tmp1
PIVOT (MIN(NF) FOR [UNID]
IN ([21], [11], [12], [31], [32], [33], [34], [35], [36])) as pv

[benyaminrahimi]

براي جوابش حقوق هم ميدين؟

[مرتضی حمزه ئی]

با سلام خدمت تمام اساتید
ازاینکه دوستان لطف کردید جواب دادید ممنون ولی واقعا شرمنده A-FARZIN هستم خداییش کارتون درست است بااین حوصله نشسته اید ونوشتید ممنون درست شد بینهایت ممنون خداییش به هرکس داده بودم تا حالا که جواب بگیرم جواب نداده بود تا اینکه دوست عزیزمون آقای فرزین جواب داد خدمت آقای HAMID.KAD هم بعدازعرض ادب واحترام عرض میکنم که ساختار یک جدول راداده بودم وهمه اطلاعات توی یک جدول بود ولی چون فرمت خروجی را هم داده بودم شاید فکرکردید دوفایل است بازهم ازحسن نظر شما عزیزان بینهایت سپاسگزارم