تبدیل سطرهای خاص از یک فیلد به ستون

واقعا ممنونم

مثلا این شکلی بشه 

من می خواهم همش مثل سطر اولی که انجام شده باشه اما با چهار ستون جدید حداقل یعنی یک ستون loginویک ستون logoutویک ستون login2و یک ستون logout2

سلام ببخشید عکس گرفتم من می خوام سطرهای ستون time1 در عکس در تاریخ های مشابه در ستون های جدا نشان داده بشه مثلا برای روز 98/08/01 هر چند سطر در ستون time1 که که تاریخشون همین باشه در ستونهای جدا نمایش داده بشه مثلا در همین تاریخ برای همین کارمند دو سطر در ستون time1 وجود دارد می خوام ستون جدیدی بنام input1 مقدار سطر اول که 07:10:11 میباشد بگیرد و ستون جدیدی بنام input2 مقدار سطر دومی که 15:10:48 در خود جای دهید و اگر سطر بعدی همین وجود داشت در ستون جدیدی بنام input3 جای بگیرد و......

سلام ببخشید عکس گرفتم من می خوام سطرهای ستون time1 در عکس در تاریخ های مشابه در ستون های جدا نشان داده بشه مثلا برای روز 98/08/01 هر چند سطر در ستون time1 که که تاریخشون همین باشه در ستونهای جدا نمایش داده بشه مثلا در همین تاریخ برای همین کارمند دو سطر در ستون time1 وجود دارد می خوام ستون جدیدی بنام input1 مقدار سطر اول که 07:10:11 میباشد بگیرد و ستون جدیدی بنام input2 مقدار سطر دومی که 15:10:48 در خود جای دهید و اگر سطر بعدی همین وجود داشت در ستون جدیدی بنام input3 جای بگیرد و......

سلام.. بهترین گزینه همون  نوشتن cursor هست که نمونه شو برات فرستادم.. اول باید تو دستور select به هرسطر rank بدی و بعد سطر به سطر تو می تونی با شرط ها مدیریت کنی و خروجی رو تو جدول موقتی ات ذخیره کنی.. تست کن مشکلی بود بگو کمکت می کنم.. 

سلام مهندس جان خیلی خیلی ممنون شما لطف کردید . مهندس جان برای شیف ها که امروز میان ولی فردا خروج می کنند اشتباه میشه.

سلام .. از نسخه 2005 به بالا پشتیبانی میشه .. می تونی رو یه نسخه بالاتر تست کنی اگه به مشکل برخوردی جزییات خطا و ساختار جداول و بهم بده که بتونم کمکت کنم .

خیلی خیلی ممنون مهندس جان،حالا چکار کنم که کارمندهای که ساعت 19 میان و ساعت 7 صبح فردا میرن آنها هم داخل یه سطر بیان؟

سلام دوست عزیز .. می خوایی منطق کد اینطور باشه که همیشه اولین ساعت ورودی (شروع از اولین رکورد جدول) و بعدی خروجی باشه بدون در نظر گرفتن تاریخ ؟ 

جهت اطلاع :  تو بیشتر سیستم های کنترل ورود و خروج ما برای هر رکورد یه flag داریم که مشخص می کنه که ورودی ثبت شده یا خروجی (خود سیستم کنترل ورود-خروج)

سلام دوست عزیز .. باید از قابلیت pivot استفاده کنی که هم تو sqlserver نسخه 2005 به بالا فعال هست هم اوراکل(11g) .. به لینک زیر یه نگاهی بنداز و اگه مشکلی بود در خدمتم ..

https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server

سلام دوست عزیز .. شرمنده بابت تاخیر و این چند وقت گرفتار ضبط آموزش ها هستم ..  من یه کد ساده برای نمونه درست کردم برات (Table valued function) که اول باید create کنی و بعد می تونی select بزنی و هرجایی که خواستی ازش استفاده کنی(Start , end  ورودی رو براش شرطی نذاشتم و می تونی مثل ورودی اول ازش استفاده کنی ) :

SELECT *
FROM fn_test(80, '98/08/01', '98/08/02')

Create FUNCTION [dbo].[fn_test] (
	@psnID_input INT
	,@startDate NCHAR(10)
	,@endDate NCHAR(10)
	)
RETURNS @T1 TABLE (
	psnID INT
	,FirsName NVARCHAR(50)
	,LastName NVARCHAR(50)
	,shamsDate NCHAR(10)
	,login1 NCHAR(10)
	,LogOut1 NCHAR(10)
	,login2 NCHAR(10)
	,LogOut2 NCHAR(10)
	)
AS
BEGIN
	DECLARE @psnID INT
	DECLARE @FirstName NVARCHAR(50)
	DECLARE @LastName NVARCHAR(50)
	DECLARE @Login1 NCHAR(10)
	DECLARE @shamsDate NCHAR(10)
	DECLARE @logOut1 NCHAR(10)
	DECLARE @Login2 NCHAR(10)
	DECLARE @logOut2 NCHAR(10)

	DECLARE PrdCur CURSOR
	FOR
	WITH ranked
	AS (
		SELECT psnID
			,FirsName
			,LastName
			,shamsDate
			,time1
			,RN = ROW_NUMBER() OVER (
				PARTITION BY psnID
				,shamsDate ORDER BY psnID
				)
		FROM test
		WHERE psnID = @psnID_input
		)
	SELECT psnID
		,FirsName
		,LastName
		,shamsDate
		,[Login] = [1]
		,[LogOut] = [2]
		,[Login1] = [3]
		,[LogOut2] = [4]
	FROM ranked
	PIVOT(MIN(time1) FOR RN IN (
				[1]
				,[2]
				,[3]
				,[4]
				)) AS p

	OPEN PrdCur

	FETCH NEXT
	FROM PrdCur
	INTO @psnID
		,@firstname
		,@LastName
		,@shamsDate
		,@Login1
		,@logOut1
		,@Login2
		,@logOut2

	WHILE @@fetch_Status <> - 1
	BEGIN
		------------------------------------------------------------
		INSERT INTO @T1 (
			psnID
			,FirsName
			,LastName
			,shamsDate
			,login1
			,LogOut1
			,login2
			,LogOut2
			)
		VALUES (
			@psnID
			,@FirstName
			,@LastName
			,@shamsDate
			,@login1
			,@LogOut1
			,@login2
			,@LogOut2
			)

		FETCH NEXT
		FROM PrdCur
		INTO @psnID
			,@firstname
			,@LastName
			,@shamsDate
			,@Login1
			,@logOut1
			,@Login2
			,@logOut2
	END

	CLOSE PrdCur

	DEALLOCATE PrdCur

	RETURN
END

سلام مهندس جان همون روز کار ها رو هم داخل تعداد بالا یعنی بیش از 10 نفر ورود و خروج رو در بعضی روزها اشتباه میده البته برای تک به تک گرفتم درسته ولی وقتی کلی می گیرم اشتباه میشه چکار کنم.

سلام .. پوزش بابت تاخیر و من پیغام آخرت و ندیدم .. ببین از این template می تونی استفاده کنی .. سرعتش تو محیط production خیلی خوب هست و من خودم بعضی توابع application رو آوردم تو پایگاه داده سرعت بازیابی داده ها حداقل 50 درصد بهتر شد (البته همه چی به طراحی پایگاه داده ، application و معماری شما بستگی داره) .. 

یک tabled-valued function باید تعریف کنی و متغیر هایی ورودی رو مشخص می کنی و بعد یه table تعریف می کنی (Memory table)  که اینجا اسمش @T1 هست .. بعد cursor تعریف میشهو می تونی روی سطر به سطر نتیجه یه کوئری یا select جدول شرط بزاری و جدول موقتی رو پر کنی ... یه نگاهی به این کد که آپلود کردم بنداز و کارت و راه می اندازه ..

لینک دانلود

سلام .. خواهش می کنم .. یه تغییر دیگه  تو script باید بدی و rand و عوض کنی : 

1. دادهای جدید اضافه کردم

2. تغییر کوچیک sql statment

 نتیجه :

Query :

WITH ranked AS
  (
  select psnID, FirsName, LastName , shamsDate , time1 , RN = ROW_NUMBER() OVER (PARTITION BY psnID, shamsDate ORDER BY psnID)
  from test
  )
SELECT
  FirsName,
  LastName,
  shamsDate,
  [Login] = [1],
  [LogOut] = [2],
    [Login1] = [3],
  [LogOut2] = [4]
FROM
  ranked
  PIVOT (MIN(time1) FOR RN IN ([1], [2] , [3] , [4])) AS p
;

سلام مهندس جان میشه روش دوم برام انجام بدهید و یا توضیح مختصر بدهی؟

سلام دوست عزیز  .. من منطق کاری رو یک بار دیگه  بررسی کردم .. همینطور که خودت دیدی تو این کوئری با استفاده از rank میشه به هر ستون یه عدد داد و اعداد پشت سر هم رو تو یک ستون با استفاده از pivot نشون داد .. 

دو

دو تا راهکار می شه انجام داد که به نتیجه مورد نظر برسی :

1. میشه نتایج این کوئری رو تو یک view دخیره کرد و دوباره با یک view دیگه join زد که توصیه نمی کنم چون تعداد رکوردها که بالا بره ، وحشتناک مشکل performance پیدا می کنی و کوئریت خیلی کند میشه 

2. از یه cursor استفاده کن و رکورد به رکورد شرط ها تو اعمال کن و نتیجه رو تو یه جدول temp برگردون .. تو بیشتر شرکت ها از این متود استفاده می کنن برای درست کردن time sheet و محاسبه کارکرد و اضافه کار کارمندها .

اره مهندس ممنون میشم رو همون جدول تستی که ساختی برام پیاده سازی کنی که من رو کل استفاده کنم چون هر چه میزنم جواب نمیده

سلام .. خواهش می کنم .. فکر کنم دیگه باید این و تو procedure یا view مدیریت کنی .. بخاطر اینکه تاریخ روزشون عوض میشه و گروه بندی نمیوتونه رکورد رو تو یک سطر pivot کنه ... حالا اگه خواستی ساختار ابتدایی کد ها رو می تونیم با هم جداگانه چک کنیم و ببینیم بهینه ترین روش برای پایگاه داده شما چی هست .

سلام دوست عزیز .. من این مراحل و انجام دادم و فکر کنم نتیجه همون باشه که شما دنبالش هستی :

1. یک جدول test ساختم و 2 تا سطر بهش اضافه کردم

2 . با استفاده از pivot و تابع rank اطلاعات سطرهای مرتبط و تو ستون جدا نمایش دادم 

WITH ranked AS
  (
  select psnID, FirsName, LastName , shamsDate , time1 , RN = ROW_NUMBER() OVER (PARTITION BY psnID ORDER BY psnID)
  from test
  )
SELECT
  FirsName,
  LastName,
  shamsDate,
  [Login] = [1],
  [LogOut] = [2]
FROM
  ranked
  PIVOT (MIN(time1) FOR RN IN ([1], [2])) AS p
;

نتیجه :

نسخه SQL Server من 2017 هست .

اره مهندس ممنون میشم رو همون جدول تستی که ساختی برام پیاده سازی کنی که من رو کل استفاده کنم چون هر چه میزنم جواب نمیده

اوکی ممنون مهندس جان

ببخشید میشه برام توضیح بدهید چون هنوز انجام ندادم و گیج شدم ،ممنون میشم

سلام هر چه می کنم خطا میده،نکنه بخاطر اسکیوال مه که 2005؟

سلام وقت بخیر و خسته نباشید مهندس جان ممنونم از اینکه دنبال حل مشکل من بودید و پیگیری کردید، این که گفتید انجام دادم اما فقط برای یک سطر درست در آمد و بقیه سطرها رو انجام نداد ولی با چند تغییر کوچک فهمیدیم سطر های بعدی انجام میده ولی در ستونهای جدید تری نتیجه رو گذاشتم ،بعدشم احتمال هست یک نفر در روز از دو بار بیشتر بشه یعنی 3 تا 4 بار ورود و خروج کنه.

ممنون میشم باید چکار کنم؟

سلام .. پوزش بابت تاخیر و من پیغام آخرت و ندیدم .. ببین از این template می تونی استفاده کنی .. سرعتش تو محیط production خیلی خوب هست و من خودم بعضی توابع application رو آوردم تو پایگاه داده سرعت بازیابی داده ها حداقل 50 درصد بهتر شد (البته همه چی به طراحی پایگاه داده ، application و معماری شما بستگی داره) .. 

یک tabled-valued function باید تعریف کنی و متغیر هایی ورودی رو مشخص می کنی و بعد یه table تعریف می کنی (Memory table)  که اینجا اسمش @T1 هست .. بعد cursor تعریف میشهو می تونی روی سطر به سطر نتیجه یه کوئری یا select جدول شرط بزاری و جدول موقتی رو پر کنی ... یه نگاهی به این کد که آپلود کردم بنداز و کارت و راه می اندازه ..

لینک دانلود

ممنون میشم امروز نمی تونید البته شرمنده 

سلام خواهش می کنم .. من فقط یه template ساده برات درست کرم و توی این (function(cursor میشه کلی شرط گذاشت .. اجازه بده من یه نمونه برات درست کنم و بفرستم که مشکل کامل حل بشه .

نه خواهش میکنم شما لطف دارید چشم چک میکنم 

سلام مهندس جان چکار کردی برام ؟

سلام دوست عزیز .. سعی می کنم یه نمونه تستی برات درست کنم و به عنوان نمونه تا چند روز آینده برات ارسال کنم 

سلام حق با شماست مهندس جان، اولین ساعت ورود و دومین خروج و سومین ورود و چهارمین خروج باشه عالیه میشه البته تاریخ ورود و خروج  در تاریخ ورود بخورد. یعنی اگه کارمندی ساعت 19 در روز 21 دیماه ورود کرد و سات 7 صبح فرداش خروج کرد در 21دیماه ثبت بشه.

سلام .. اگه این کارو انجام بدیم اگر هر کسی تعداد بیشتر ورود و خروج داشته باشه (بیش از 2 سطر) تو یک روز بازم به مشکل بر می خوریم .. اجازه بده من چک می کنم دوباره و سعی می کنم سریع بهتون اطلاع بدم.

البته می خوام تاریخ تکرار نشه یعنی اگه دو سطر و یا بیشتر از یک تاریخ برای یک کارمند وجو داشت فقط یک سطر بشه و سطرهای تکراری البته با تایم های متفاوت در ستونهای جدید در همین سطر نمایش داده بشن