SQl Queries for the following cases plz help me

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 2005 Forums

Transact-SQL (2005)

SQl Queries for the following cases plz help me

Author

Topic

ramprasad_554
Starting Member

1 Post

Posted - 2007-02-14 : 06:30:58

Pivot:--->>> Assume that you have table A that contains data regarding tips earned for each employee in the following format:
A(empid, Mon_tips, Tues_tips, Wed_tips, Thu_tips, Fri_tips) .
Write a SQL Query that will convert the data from table A into table B with the following format: B(empid, day, tips) where the column day can take the values (Mon, Tue, Wed, Thu, Fri).

Create table A (empid int, mon_tips int, tue_tips int, wed_tips int, thu_tips int, Fri_tips int);
Insert into A value (1, 10, 20, 30, 20,10);
Insert into A value (2, 10, 20, 30, 40,50);

>>> Unpivot: Reverse of Question 2 above. Assume that you have a table B write a SQL to convert the data into table A.

>>>>Print the employee id and the difference in salary from the employee with the next lower salary.Print -1 if the employee has the lowest salary in the organization.

Create table emp(eid int sal int);
Insert into emp Values(1,100000);
Insert into emp Values(2,80000);

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 09:28:51

[code]-- prepare sample data
declare @A table (empid int, mon_tips int, tue_tips int, wed_tips int, thu_tips int, Fri_tips int)
Insert into @A values (1, 10, 20, 30, 20,10)
Insert into @A values (2, 10, 20, 30, 40,50)

-- show the expected result
select empid, 'mon', mon_tips from @a union all
select empid, 'tue', tue_tips from @a union all
select empid, 'wed', wed_tips from @a union all
select empid, 'thu', thu_tips from @a union all
select empid, 'fri', fri_tips from @a[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-14 : 09:41:59

Use the new PIVOT function. Or the UNPIVOT function,
Or...

-- prepare sample data
declare @t table (empid int, weekdayname varchar(3), value int)

insert	@t
select	1, 'mon', 10 union all
select	2, 'mon', 10 union all
select	1, 'tue', 20 union all
select	2, 'tue', 20 union all
select	1, 'wed', 30 union all
select	2, 'wed', 30 union all
select	1, 'thu', 20 union all
select	2, 'thu', 40 union all
select	1, 'fri', 10 union all
select	2, 'fri', 50

-- show the expected result
select		empid,
		max(case when weekdayname = 'mon' then value end) as mon_tips,
		max(case when weekdayname = 'tue' then value end) as mon_tips,
		max(case when weekdayname = 'wed' then value end) as mon_tips,
		max(case when weekdayname = 'thu' then value end) as mon_tips,
		max(case when weekdayname = 'fri' then value end) as mon_tips
from		@t
group by	empid
order by	empid

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources