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, April 14, 2015 10:30 PM
Points: 314, Visits: 2,537
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: 2 days ago @ 9:56 AM
Points: 7,151, Visits: 14,996
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: Friday, May 15, 2015 6:12 AM
Points: 183, Visits: 469
very cool Sachin Nandanwar. For some reason i always had issue understanding the pivot. seems really simple right there. thx
Post #1023115
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse