Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need help in writing select query Expand / Collapse
Author
Message
Posted Wednesday, November 17, 2010 3:09 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 7:49 AM
Points: 8, Visits: 26
I have a table called
Employee(ID,Name,Age,Country,Manager,Status);
And the values are as follows -
(1,'Ravi',29,'IN','Anil','A')
(2,'Balaji',30,'IN','Siju','A')
(3,'Sanjeev',31,'IN','Venkat','A')

I need a select query to get results as follows -

1,'Ravi',29,'IN'
1,'Ravi',29,'Anil'
1,'Ravi',29,'A'

2,'Balaji',30,'IN'
2,'Balaji',30,'Siju'
2,'Balaji',30,'A'

3,'Sanjeev',31,'IN'
3,'Sanjeev',31,'Venkat'
3,'Sanjeev',31,'A'

Can anybody help here to write the select query.

Thanks,
Ravi.


Post #1022000
Posted Wednesday, November 17, 2010 3:26 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
declare  @Employeetable as table(ID int,Name varchar(20),
Age int,Country varchar(20),
Manager varchar(20),Status varchar(20));
insert @Employeetable
select 1,'Ravi',29,'IN','Anil','A'union
select 2,'Balaji',30,'IN','Siju','A'union
select 3,'Sanjeev',31,'IN','Venkat','A'

select ID,Name,Age,Value from
(
select * from @Employeetable)u
unpivot (Value for columns in (country,manager,status))v

Post #1022009
Posted Wednesday, November 17, 2010 4:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, January 20, 2011 7:49 AM
Points: 8, Visits: 26
Thank you very much!!
Post #1022037
Posted Wednesday, November 17, 2010 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:22 AM
Points: 7,130, Visits: 13,513
DROP TABLE #Employee
CREATE TABLE #Employee (ID INT, Name VARCHAR(10), Age INT, Country CHAR(2), Manager VARCHAR(10), Status CHAR(1));
INSERT INTO #Employee (ID, Name, Age, Country, Manager, Status)
SELECT 1,'Ravi',29,'IN','Anil','A' UNION ALL
SELECT 2,'Balaji',30,'IN','Siju','A' UNION ALL
SELECT 3,'Sanjeev',31,'IN','Venkat','A'

SELECT ID, Name, Age, NewColumn
FROM ( -- derived table is unnecessary if Seq is acceptable in output
SELECT ID, Name, Age, NewColumn = CAST(Country AS VARCHAR(10)), 1 AS Seq
FROM #Employee
UNION ALL
SELECT ID, Name, Age, NewColumn = CAST(Manager AS VARCHAR(10)), 2 AS Seq
FROM #Employee
UNION ALL
SELECT ID, Name, Age, NewColumn = CAST(Status AS VARCHAR(10)), 3 AS Seq
FROM #Employee
) d
ORDER BY ID, Seq



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1022083
Posted Thursday, November 18, 2010 12:34 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 7:56 AM
Points: 144, Visits: 355
very cool Sachin Nandanwar. For some reason i always had issue understanding the pivot. seems really simple right there. thx
Post #1023115
Posted Thursday, November 18, 2010 2:31 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 2:44 PM
Points: 1,945, Visits: 2,862
A manager is NOT an attribute of an employee. They have a RELATIONSHIP; where is the table for this relationship?

Look up the nested sets model for an organizational chart. It will work much better than you improperly designed schema.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1023175
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse