Need help in writing select query

• borra2005

  SSC Enthusiast

  Points: 150

  More actions

  November 17, 2010 at 3:09 am

  #231710

  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.

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  November 17, 2010 at 3:26 am

  #1251761

  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

  --------------------------------------------------------------------------------------------------
  I am just an another naive wannabe DBA trying to learn SQL Server

• borra2005

  SSC Enthusiast

  Points: 150

  More actions

  November 17, 2010 at 4:10 am

  #1251788

  Thank you very much!!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  November 17, 2010 at 5:26 am

  #1251830

  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

• Snargables

  SSCrazy Eights

  Points: 8848

  More actions

  November 18, 2010 at 12:34 pm

  #1252705

  very cool Sachin Nandanwar. For some reason i always had issue understanding the pivot. seems really simple right there. thx