question

lomas avatar image
lomas asked

Pivot Data in Sql Server

I have data in database looks like first table in the picture and need output looks like second table in the picture.

Background:

Barcodes can be repeat several times during the date range with multiple Transcode (Scan locations). To become a complete transaction, it must follow the series of transaction such as T2->T6->T7->T3. However any transaction could be missed. Here in my scenario, I need to find out missing T6 or T7 exactly look like a second table. I hope someone help me out with this problem.

Thanks

sql-server-2008-r2pivot
sql-1.png (33.3 KiB)
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Moved to the correct area of the site.

1 Like 1 ·

This needs moving to the General Q&A, it's been posted in the site meta board.

Also, is this not a repeat of the question that you've asked where i've suggested Pivot?

0 Likes 0 ·

1 Answer

· Write an Answer
sthatavarthi avatar image
sthatavarthi answered

Hope the below helps.

DECLARE @T TABLE
(
	Barcode CHAR(5),
	Transcode CHAR(2),
	ScanTimeStamp DATETIME
);
INSERT INTO @T
VALUES
	('A0001','T2','20180424 10:01:26'),
	('A0001','T6','20180424 11:03:27'),
	('A0001','T7','20180510 09:08:20'),
	('A0001','T3','20180510 09:08:20'),
	('A0001','T2','20180513 09:08:20'),
	('A0002','T2','20180224 10:01:26'),
	('A0002','T6','20180225 11:03:27'),
	('A0002','T7','20180310 09:08:20'),
	('A0002','T3','20180310 12:18:22'),
	('A0003','T2','20180120 10:01:26'),
	('A0003','T7','20181121 17:41:55'),
	('A0003','T3','20181122 10:01:26');
WITH Data AS
(
	SELECT *,Rnk = ROW_NUMBER() OVER (PARTITION BY Barcode,Transcode ORDER BY ScanTimeStamp)
	FROM @T
)
SELECT DISTINCT
	A.Barcode,
	[T2] = (SELECT MAX(B.ScanTimeStamp) FROM Data AS B WHERE B.Barcode = A.Barcode AND B.Rnk = A.Rnk AND B.Transcode = 'T2'),
	[T6] = (SELECT MAX(B.ScanTimeStamp) FROM Data AS B WHERE B.Barcode = A.Barcode AND B.Rnk = A.Rnk AND B.Transcode = 'T6'),
	[T7] = (SELECT MAX(B.ScanTimeStamp) FROM Data AS B WHERE B.Barcode = A.Barcode AND B.Rnk = A.Rnk AND B.Transcode = 'T7'),
	[T3] = (SELECT MAX(B.ScanTimeStamp) FROM Data AS B WHERE B.Barcode = A.Barcode AND B.Rnk = A.Rnk AND B.Transcode = 'T3')
FROM Data AS A
ORDER BY A.Barcode,[T2];
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.