please help to solve this SQL Server problem

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

General SQL Server Forums

New to SQL Server Programming

please help to solve this SQL Server problem

Author

Topic

crisgomez
Starting Member

3 Posts

Posted - 2011-07-06 : 20:04:29

Please help to solve this SQL Server problem. I have a table employee along with the fields listed below (please see Employee Table in bold). I want to get the results shown below (please see Result in bold).

Employee Table:

Name
Age
Salary
Employee Type
Employed Date

Employee Data:

Name Age Salary Employee_Type Date_Hired
Employee 1 24 24,000.00 Staff January 15, 2009
Employee 2 33 32,000.00 Staff February 1, 2005
Employee 3 23 75,000.00 Consultant March 20, 2002

Result:

Employee 1 Employee 2 Employee 3
Age 24 33 23
Salary 24,000.00 32,000.00 75,000.00
Employee_Type Staff Staff Consultant
Date_Hired January 15, 2009 February 1, 2005 March 20, 2002

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-06 : 20:17:12

You really only have 3 employees?
Something like

with cte as
(
select name, type = 'age', value = convert(varchar(20),age) from Employee
union all
select name, type = 'Salary', value = convert(varchar(20),Salary) from Employee
union all
select name, type = 'Type', value = convert(varchar(20),Type) from Employee
union all
select name, type = 'Date', value = convert(varchar(20),Date) from Employee
)
select Type
, Employee1 = max(case when name = 'Employee1' then value else '' end)
, Employee2 = max(case when name = 'Employee2' then value else '' end)
, Employee3 = max(case when name = 'Employee3' then value else '' end)
from cte
group by type

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pravintml
Starting Member

2 Posts

Posted - 2011-07-07 : 00:29:00

DECLARE @name VARCHAR(2000)
DECLARE @name1 VARCHAR(200)

SET @name='Name : '
DECLARE Ename CURSOR FOR
SELECT [name] FROM Employee

OPEN Ename

FETCH next FROM Ename INTO @name1

WHILE @@FETCH_STATUS = 0

begin
SET @name=@name + ' '+ @name1

FETCH NEXT FROM Ename INTO @name1
END
PRINT @name
CLOSE Ename
DEALLOCATE Ename

DECLARE @age VARCHAR(2000)
DECLARE @age1 VARCHAR(200)

SET @age='Age : '
DECLARE EAge CURSOR FOR
SELECT [age] FROM Employee

OPEN EAge

FETCH next FROM EAge INTO @age1

WHILE @@FETCH_STATUS = 0

begin
SET @age=@age + ' '+ @age1

FETCH NEXT FROM EAge INTO @age1
END
PRINT @age
CLOSE EAge
DEALLOCATE EAge

--what ever u need u can Add

Pravin
pravintml@yahoo.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-07 : 01:06:25

Pravin, why the cursor based solution ?

And the result is just one long string

KH
[spoiler]Time is always against us[/spoiler]

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-07-07 : 04:16:26

quote:
Originally posted by pravintml

DECLARE @name VARCHAR(2000)
DECLARE @name1 VARCHAR(200)

SET @name='Name : '
DECLARE Ename CURSOR FOR
SELECT [name] FROM Employee

OPEN Ename

FETCH next FROM Ename INTO @name1

WHILE @@FETCH_STATUS = 0

begin
SET @name=@name + ' '+ @name1

FETCH NEXT FROM Ename INTO @name1
END
PRINT @name
CLOSE Ename
DEALLOCATE Ename

DECLARE @age VARCHAR(2000)
DECLARE @age1 VARCHAR(200)

SET @age='Age : '
DECLARE EAge CURSOR FOR
SELECT [age] FROM Employee

OPEN EAge

FETCH next FROM EAge INTO @age1

WHILE @@FETCH_STATUS = 0

begin
SET @age=@age + ' '+ @age1

FETCH NEXT FROM EAge INTO @age1
END
PRINT @age
CLOSE EAge
DEALLOCATE EAge

--what ever u need u can Add

Pravin
pravintml@yahoo.com

Or the equivalent
DECLARE @name VARCHAR(2000), @age VARCHAR(2000)
select
@name = coalesce(@name + ' ','Name : ') + name ,
@age = coalesce(@age + ' ','Age : ') + age
FROM Employee
print @name
print @age

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

pravintml
Starting Member

2 Posts

Posted - 2011-07-07 : 06:38:34

i think cursor is da better solution for this case...
datz y?
wt do u thnkng KH???
any posibility for this case????

Pravin
pravintml@yahoo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources