Row to Column Challenge - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Row to Column Challenge

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-12 : 19:32:39

[code]
CREATE TABLE [dbo].[call_results] (
[emp_number] [varchar] (13) NOT NULL ,
[phone] [varchar](15) NULL ,
[result] [varchar] (3) NULL ,
[time] datetime NULL ,
[operator_id] [varchar](15) NULL ,
[department] [varchar](15) NULL ,
[unit] [varchar](15) NULL ,
[date_of_call] datetime NULL

)

INSERT INTO call_results
SELECT 'E005','6578902456','S','2007-04-11 4:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','K','2007-04-11 5:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','SM','2007-04-11 6:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','AD','2007-04-11 7:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','RF','2007-04-11 8:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','BS','2007-04-11 9:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','TS','2007-04-11 10:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','HS','2007-04-11 11:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','US','2007-04-11 12:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','WS','2007-04-11 13:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','QS','2007-04-11 14:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION
SELECT 'E005','6578902456','HS','2007-04-11 15:13:57.000','LMT','CPP','0-49','2007-04-11 00:00:00.000' UNION

SELECT 'E006','9674577777','FT','2007-04-11 2:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','GT','2007-04-11 3:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','KL','2007-04-11 4:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','OP','2007-04-11 5:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','MP','2007-04-11 6:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','ST','2007-04-11 7:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','TS','2007-04-11 8:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','HS','2007-04-11 9:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','UU','2007-04-11 10:21:53.000','TQS','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','UV','2007-04-11 11:21:53.000','TQS','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','WE','2007-04-11 12:21:53.000','TQS','CPP','0-23','2007-04-11 00:00:00.000' UNION
SELECT 'E006','9674577777','YY','2007-04-11 13:21:53.000','VIJ','CPP','0-23','2007-04-11 00:00:00.000' UNION

SELECT 'E007','3419864521','DS','2007-04-11 18:13:57.000','M1H','CPP','0-16','2007-04-11 00:00:00.000' UNION
SELECT 'E007','3419864521','SE','2007-04-11 20:13:57.000','K4G','CPP','0-16','2007-04-11 00:00:00.000'

SELECT * FROM call_results
Iam finding very difficulty to write one single query to get the output I needed.Below shown are the results that I want to achieve (Logic):

1.Need to get the last 3 records (based on the [time] field)
2.These 3 records should be transposed from column to row and fields like department,unit and phone will have the one with the latest [time]
3.If there is only 2 or one records like in case of E007 then only those 2 will be populated and rest will be NULL values

emp_number phone result1 result2 result3 time1 time2 time3 operator_id1 operator_id2 operator_id3 unit department date_of_call1 date_of_call2 date_of_call3
E005 6578902456 HS QS WS 2007-04-11 15:13:57.000 2007-04-11 14:13:57.000 2007-04-11 13:13:57.000 LMT LMT LMT CPP 0-49 2007-04-11 00:00:00.000 2007-04-11 00:00:00.000 2007-04-11 00:00:00.000
E006 9674577777 YY WE UV 2007-04-11 13:21:53.000 2007-04-11 12:21:53.000 2007-04-11 11:21:53.000 VIJ TQS TQS CPP 0-23 2007-04-11 00:00:00.000 2007-04-11 00:00:00.000 2007-04-11 00:00:00.000
E007 3419864521 SE DS NULL 2007-04-11 20:13:57.000 2007-04-11 18:13:57.000 NULL K4G M1H NULL CPP 0-16 2007-04-11 00:00:00.000 2007-04-11 00:00:00.000 2007-04-11 00:00:00.000

[/code]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 21:02:07

[code]
alter table call_results add seq int

update r
set seq = (select count(*) from call_results x where x.emp_number = r.emp_number and x.phone = r.phone and x.[time] >= r.[time])
from call_results r

select emp_number, phone,
result1 = max(case when seq = 1 then result end),
result2 = max(case when seq = 2 then result end),
result3 = max(case when seq = 3 then result end),
time1 = max(case when seq = 1 then [time] end),
time2 = max(case when seq = 2 then [time] end),
time3 = max(case when seq = 3 then [time] end),
operator_id1 = max(case when seq = 1 then [operator_id] end),
operator_id2 = max(case when seq = 2 then [operator_id] end),
operator_id3 = max(case when seq = 3 then [operator_id] end)
from call_results
group by emp_number, phone
order by emp_number, phone
[/code]

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-12 : 21:13:42

This will work..Great thought and query !!!!

Thanks a lot But I have to alter the table and I can't alter the table since this table is populated by another vendor

Is there anyother way ??

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-12 : 21:16:38

Is it possible to have one single select without the update statement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 21:18:04

what is the Primary Key of the table ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 21:19:36

quote:
Originally posted by sqllearner

This will work..Great thought and query !!!!

Thanks a lot But I have to alter the table and I can't alter the table since this table is populated by another vendor

Is there anyother way ??

Or insert the data into a temp table and run the query from the temp table.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-12 : 22:07:01

The [emp_number] and [time] will be unique.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 22:09:22

[code]
declare @seq table
(
emp_number varchar(13),
phone varchar(15),
[time] datetime,
seq int
)

insert into @seq (emp_number, phone, [time])
select emp_number, phone, [time]
from call_results r
where r.[time] in (select top 3 [time] from call_results x where x.emp_number = r.emp_number and x.phone = r.phone order by [time] desc)

update r
set seq = (select count(*) from call_results x where x.emp_number = r.emp_number and x.phone = r.phone and x.[time] >= r.[time])
from @seq r

select r.emp_number, r.phone,
result1 = max(case when s.seq = 1 then r.result end),
result2 = max(case when s.seq = 2 then r.result end),
result3 = max(case when s.seq = 3 then r.result end),
time1 = max(case when s.seq = 1 then r.[time] end),
time2 = max(case when s.seq = 2 then r.[time] end),
time3 = max(case when s.seq = 3 then r.[time] end),
operator_id1 = max(case when s.seq = 1 then r.[operator_id] end),
operator_id2 = max(case when s.seq = 2 then r.[operator_id] end),
operator_id3 = max(case when s.seq = 3 then r.[operator_id] end)
from call_results r inner join @seq s
on r.emp_number = s.emp_number
and r.phone = s.phone
and r.[time] = s.[time]
group by r.emp_number, r.phone
order by r.emp_number, r.phone
[/code]

You are not using SQL Server 2005 are you ? It will be much easier in SQL 2005

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-12 : 22:28:52

Iam using this query in ETL for processing the records.
Here insertion into @seq will take more time since we are dealing with millions of records and update of the @seq will also consume time.So some how if I can use a single query thats the ideal solution

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-12 : 22:33:51

quote:
Originally posted by sqllearner

Iam using this query in ETL for processing the records.
Here insertion into @seq will take more time since we are dealing with millions of records and update of the @seq will also consume time.So some how if I can use a single query thats the ideal solution

Upgrade to 2005 ? or wait for Peter to comes in.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-13 : 02:05:49

This should work fast enough. Try it and report back.

-- Prepare sample data (this section is only here to emulate your environment)
DECLARE	@Results TABLE
	(
		Emp_Number VARCHAR(13),
		Phone VARCHAR(15),
		Result VARCHAR(3),
		[Time] DATETIME,
		Operator_ID VARCHAR(15),
		Department VARCHAR(15),
		Unit VARCHAR(15),
		Date_Of_Call DATETIME
	)

INSERT	@Results
SELECT	'E005', '6578902456', 'S', '2007-04-11 04:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'K', '2007-04-11 05:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'SM', '2007-04-11 06:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'AD', '2007-04-11 07:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'RF', '2007-04-11 08:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'BS', '2007-04-11 09:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'TS', '2007-04-11 10:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'HS', '2007-04-11 11:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'US', '2007-04-11 12:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'WS', '2007-04-11 13:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'QS', '2007-04-11 14:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E005', '6578902456', 'HS', '2007-04-11 15:13:57', 'LMT', 'CPP', '0-49', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'FT', '2007-04-11 02:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'GT', '2007-04-11 03:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'KL', '2007-04-11 04:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'OP', '2007-04-11 05:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'MP', '2007-04-11 06:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'ST', '2007-04-11 07:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'TS', '2007-04-11 08:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'HS', '2007-04-11 09:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'UU', '2007-04-11 10:21:53', 'TQS', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'UV', '2007-04-11 11:21:53', 'TQS', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'WE', '2007-04-11 12:21:53', 'TQS', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E006', '9674577777', 'YY', '2007-04-11 13:21:53', 'VIJ', 'CPP', '0-23', '2007-04-11' UNION ALL
SELECT	'E007', '3419864521', 'DS', '2007-04-11 18:13:57', 'M1H', 'CPP', '0-16', '2007-04-11' UNION ALL
SELECT	'E007', '3419864521', 'SE', '2007-04-11 20:13:57', 'K4G', 'CPP', '0-16', '2007-04-11'

-- Stage the data (From here is the code you should use in your environment)
DECLARE	@Stage TABLE
	(
		RecID INT IDENTITY(1, 1) PRIMARY KEY,
		Emp_Number VARCHAR(13),
		[Time] DATETIME,
		Seq INT
	)

INSERT		@Stage
		(
			Emp_Number,
			[Time]
		)
SELECT		Emp_Number,
		[Time]
FROM		@Results
ORDER BY	Emp_Number,
		[Time] DESC

UPDATE		s
SET		s.Seq = s.RecID - x.RecID
FROM		@Stage AS s
INNER JOIN	(
			SELECT		Emp_Number,
					MIN(RecID) AS RecID
			FROM		@Stage
			GROUP BY	Emp_Number
		) AS x ON x.Emp_Number = s.Emp_Number

DELETE
FROM	@Stage
WHERE	Seq > 2

-- Show the expected output
SELECT		s.Emp_number,
		r.phone, 
		MAX(CASE WHEN s.Seq = 0 THEN r.Result END) AS Result1,
		MAX(CASE WHEN s.Seq = 1 THEN r.Result END) AS Result2,
		MAX(CASE WHEN s.Seq = 2 THEN r.Result END) AS Result3,
		MAX(CASE WHEN s.Seq = 0 THEN r.[Time] END) AS Time1,
		MAX(CASE WHEN s.Seq = 1 THEN r.[Time] END) AS Time2,
		MAX(CASE WHEN s.Seq = 2 THEN r.[Time] END) AS Time3,
		MAX(CASE WHEN s.Seq = 0 THEN r.Operator_ID END) AS Operator_ID1,
		MAX(CASE WHEN s.Seq = 1 THEN r.Operator_ID END) AS Operator_ID2,
		MAX(CASE WHEN s.Seq = 2 THEN r.Operator_ID END) AS Operator_ID3,
		r.Unit,
		r.Department,
		MAX(CASE WHEN s.Seq = 0 THEN r.Date_Of_Call END) AS Date_Of_Call1,
		MAX(CASE WHEN s.Seq = 1 THEN r.Date_Of_Call END) AS Date_Of_Call2,
		MAX(CASE WHEN s.Seq = 2 THEN r.Date_Of_Call END) AS Date_Of_Call3
FROM		@Stage AS s
INNER JOIN	@Results AS r ON r.Emp_Number = s.Emp_Number AND r.[Time] = s.[Time]
GROUP BY	s.Emp_Number,
		r.Phone,
		r.Unit,
		r.Department
ORDER BY	s.Emp_Number,
		r.Phone,
		r.Unit,
		r.Department

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-04-13 : 03:47:00

If you use any reporting tools to display those data, you can also make use of it's Cross tab feature

Madhivanan

Failing to plan is Planning to fail

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-16 : 13:03:06

How can I get the last 3 records of each emp_number from the call_results table

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 13:07:20

Define "last three", please.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 13:10:40

After over 600 posts, and you still haven't learned to examine the code?

-- Show the new expected output
SELECT		*
FROM		@Stage

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-16 : 13:26:02

Sorry :
Below shown is the resultset Iam trying to get :


emp_number 	phone 		result 		time 			operator_id 	department 	unit 	date_of_call

'E005'		'6578902456'	'WS'	'2007-04-11 13:13:57.000'	'LMT'		'CPP'		'0-49'	'2007-04-11 00:00:00.000' 
'E005'		'6578902456'	'QS'	'2007-04-11 14:13:57.000'	'LMT'		'CPP'		'0-49'	'2007-04-11 00:00:00.000' 
'E005'		'6578902456'	'HS'	'2007-04-11 15:13:57.000'	'LMT'		'CPP'		'0-49'	'2007-04-11 00:00:00.000' 

'E006'		'9674577777'	'UV'	'2007-04-11 11:21:53.000'	'TQS'		'CPP'		'0-23'	'2007-04-11 00:00:00.000' 
'E006'		'9674577777'	'WE'	'2007-04-11 12:21:53.000'	'TQS'		'CPP'		'0-23'	'2007-04-11 00:00:00.000' 
'E006'		'9674577777'	'YY'	'2007-04-11 13:21:53.000'	'VIJ'		'CPP'		'0-23'	'2007-04-11 00:00:00.000' 
'E007'		'3419864521'	'DS'	'2007-04-11 18:13:57.000'	'M1H'		'CPP'		'0-16'	'2007-04-11 00:00:00.000' 
'E007'		'3419864521'	'SE'	'2007-04-11 20:13:57.000'	'K4G'		'CPP'		'0-16'	'2007-04-11 00:00:00.000'

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 13:32:47

Blimey! You still don't get it?

-- Show the third expected output
SELECT		r.*
FROM		@Stage AS s
INNER JOIN	@Results AS r ON r.Emp_Number = s.Emp_Number AND r.Time = s.Time

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-16 : 15:06:02

Its not the third expected out:
The above shown is the resultset Iam trying to get

Need to get the latest 3 records of each emp_number based on the time field.So when querying the
call_results table I get all the records.Now the query will return only lastest 3 records for each emp_number

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-16 : 15:43:36

Hope this information helps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-16 : 15:59:22

I have given you the answer already.
Why do you think I named the new suggestion "Show the expected output"?
Copy everything else before that and replace the old "Show the expected output" with the new "Show the third expected output".

Peter Larsson
Helsingborg, Sweden

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2007-04-16 : 16:36:01

The expected ouput is as shown below :


emp_number 	phone 		result 		time 			operator_id 	department 	unit 	date_of_call

E005		6578902456	WS	2007-04-11 13:13:57.000		LMT		CPP		0-49	2007-04-11 00:00:00.000 
E005		6578902456	QS	2007-04-11 14:13:57.000		LMT		CPP		0-49	2007-04-11 00:00:00.000 
E005		6578902456	HS	2007-04-11 15:13:57.000		LMT		CPP		0-49	2007-04-11 00:00:00.000 
E006		9674577777	UV	2007-04-11 11:21:53.000		TQS		CPP		0-23	2007-04-11 00:00:00.000 
E006		9674577777	WE	2007-04-11 12:21:53.000		TQS		CPP		0-23	2007-04-11 00:00:00.000 
E006		9674577777	YY	2007-04-11 13:21:53.000		VIJ		CPP		0-23	2007-04-11 00:00:00.000 
E007		3419864521	DS	2007-04-11 18:13:57.000		M1H		CPP		0-16	2007-04-11 00:00:00.000 
E007		3419864521	SE	2007-04-11 20:13:57.000		K4G		CPP		0-16	2007-04-11 00:00:00.000

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources