Need query in SQL server 2000

• Venkatesan Prabu

  SSCommitted

  Points: 1892

  More actions

  August 17, 2008 at 12:44 am

  #70643

  create table FirstTable(Attrib1 varchar(10), Attrib2 varchar(10),Attrib3 varchar(10))

  insert into FirstTable values('Doc1','Attrib1','val1')

  insert into FirstTable values('Doc2','Attrib2','val2')

  insert into FirstTable values('Doc3','Attrib3','val3')

  insert into FirstTable values('Doc4','Attrib4','val4')

  select * from FirstTable

  create table SecondTable(Attrib2 varchar(10),Attrib22 varchar(10))

  insert into SecondTable values('Attrib1','S1','BE')

  insert into SecondTable values('Attrib1','S2','MBA')

  insert into SecondTable values('Attrib1','S3','MS')

  insert into SecondTable values('Attrib1','S4','MBBS')

  insert into SecondTable values('Attrib2','S5','BE')

  insert into SecondTable values('Attrib2','S6','MBA')

  insert into SecondTable values('Attrib2','S7','MS')

  insert into SecondTable values('Attrib2','S8','MBBS')

  select * from SecondTable

  create table ThirTable(Attrib22 varchar(10),Attrib31 varchar(10))

  insert into ThirTable values('S1','Venkat')

  insert into ThirTable values('S1','Suba')

  insert into ThirTable values('S1','Arun')

  insert into ThirTable values('S5','Lakshmi')

  insert into ThirTable values('S5','Sri')

  insert into ThirTable values('S5','Raj')

  insert into ThirTable values('S5','Jaya')

  create table FourthTable(Attrib22 varchar(10),Attrib41 varchar(10))

  insert into FourthTable values('S2','Venkat')

  insert into FourthTable values('S2','Suba')

  insert into FourthTable values('S2','Arun')

  insert into FourthTable values('S2','Lakshmi')

  insert into FourthTable values('S6','Sri')

  insert into FourthTable values('S6','Raj')

  insert into FourthTable values('S6','Jaya')

  select * from FourthTable

  My output should be

  ------------------------------------------------------------------------

  Doc Attribute Attrib3 BE1 BE2 BE3 MBA1 MBA2 MBA3

  ------------------------------------------------------------------------

  Doc1 Attrib1 val1 Venkat Suba Arun Venkat Suba Arun

  Doc2 Attrib2 val2 Sri Raj Jaya Sri Raj Jaya

  -------------------------------------------------------------------------

  Please help me, how can i achieve this.

  FirstTable has 1 lakh record , Second table has 2 Lakh record and Third and fourth table has 2 lakh record

  Note: Need query in SQL Server 2000

  Thanks and Regards,
  Venkatesan Prabu, 😛
  My Blog:

  http://venkattechnicalblog.blogspot.com/

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  August 17, 2008 at 3:10 am

  #859155

  SQL 2000-related questions should rather be posted in the SQL 2000 forums in future please.

  FirstTable has 1 lakh record , Second table has 2 Lakh record and Third and fourth table has 2 lakh record

  What's a 'lakh'?

  Are the number of columns (number of BE and MBA columns) fixed or can they change? If fixed, what's the max number possible?

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736255

  More actions

  August 17, 2008 at 10:57 am

  #859176

  Moved to 2000.

  Also, look up cross tab reports. It seems that's what you're looking for.

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  August 17, 2008 at 3:35 pm

  #859194

  GilaMonster (8/17/2008)

  ---

  SQL 2000-related questions should rather be posted in the SQL 2000 forums in future please.

  FirstTable has 1 lakh record , Second table has 2 Lakh record and Third and fourth table has 2 lakh record

  What's a 'lakh'?

  Are the number of columns (number of BE and MBA columns) fixed or can they change? If fixed, what's the max number possible?

  A "lakh" is 100,000 rows.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004362

  More actions

  August 17, 2008 at 3:49 pm

  #859195

  Venkatesan Prabu (8/17/2008)

  ---

  create table FirstTable(Attrib1 varchar(10), Attrib2 varchar(10),Attrib3 varchar(10))

  insert into FirstTable values('Doc1','Attrib1','val1')

  insert into FirstTable values('Doc2','Attrib2','val2')

  insert into FirstTable values('Doc3','Attrib3','val3')

  insert into FirstTable values('Doc4','Attrib4','val4')

  select * from FirstTable

  create table SecondTable(Attrib2 varchar(10),Attrib22 varchar(10))

  insert into SecondTable values('Attrib1','S1','BE')

  insert into SecondTable values('Attrib1','S2','MBA')

  insert into SecondTable values('Attrib1','S3','MS')

  insert into SecondTable values('Attrib1','S4','MBBS')

  insert into SecondTable values('Attrib2','S5','BE')

  insert into SecondTable values('Attrib2','S6','MBA')

  insert into SecondTable values('Attrib2','S7','MS')

  insert into SecondTable values('Attrib2','S8','MBBS')

  select * from SecondTable

  create table ThirTable(Attrib22 varchar(10),Attrib31 varchar(10))

  insert into ThirTable values('S1','Venkat')

  insert into ThirTable values('S1','Suba')

  insert into ThirTable values('S1','Arun')

  insert into ThirTable values('S5','Lakshmi')

  insert into ThirTable values('S5','Sri')

  insert into ThirTable values('S5','Raj')

  insert into ThirTable values('S5','Jaya')

  create table FourthTable(Attrib22 varchar(10),Attrib41 varchar(10))

  insert into FourthTable values('S2','Venkat')

  insert into FourthTable values('S2','Suba')

  insert into FourthTable values('S2','Arun')

  insert into FourthTable values('S2','Lakshmi')

  insert into FourthTable values('S6','Sri')

  insert into FourthTable values('S6','Raj')

  insert into FourthTable values('S6','Jaya')

  select * from FourthTable

  My output should be

  ------------------------------------------------------------------------

  Doc Attribute Attrib3 BE1 BE2 BE3 MBA1 MBA2 MBA3

  ------------------------------------------------------------------------

  Doc1 Attrib1 val1 Venkat Suba Arun Venkat Suba Arun

  Doc2 Attrib2 val2 Sri Raj Jaya Sri Raj Jaya

  -------------------------------------------------------------------------

  Please help me, how can i achieve this.

  FirstTable has 1 lakh record , Second table has 2 Lakh record and Third and fourth table has 2 lakh record

  Note: Need query in SQL Server 2000

  Since no one tells me after I answer their question, I'll ask before I even start on this... why do you want to denormalize data this way?

  Also, is 3 the limit for the number of BE's and MBA's? If not, what do you want to do? How would you select which 3 to use if you decide to limit to 3 and there are more?

  Last, but not least, table 3 and 4 appear to hold similar information but one is for BE's and 1 is for MBA's... is there any chance you could normalize that a bit?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)