Rows into columns

• Rajesh Kumar Singh

  SSC-Addicted

  Points: 454

  More actions

  October 4, 2005 at 12:38 am

  #91453

  The  Structure of table A is :

  Iref _ no    Firstname   Middlename   Salary

  1              Rajesh       Kumar           5000

  2              John           ranzo          7000

  The output should be as follows:

  1            2

  Rajesh    John

  Kumar    Aranzo

  5000      7000

  What would be the the effiecient way of converting rows into columns when the no of columns in Table A is fixed.

• Tyson L. Price

  Mr or Mrs. 500

  Points: 589

  More actions

  October 4, 2005 at 5:23 am

  #594779

  I view a couple of different sites each morning. Your question looked similar to this one. I didn't look very closly but maybe it is worh checking out:

  http://www.tek-tips.com/viewthread.cfm?qid=1131480&page=1

• DKeeling

  Old Hand

  Points: 385

  More actions

  October 5, 2005 at 1:39 am

  #595072

  I do this all the time, however as a programmer I link the tables to M$ Access 97/2000 and write a procedure to perform the conversion. - Far better than spending days working out how to do it in a declaratrive language(ie SQL).

  But you should be able to perform this in T-sql the only real problem is referring to the columns.

   

  To get you started use the following SQL to get the columns names.

   

  SELECT     TOP 100 PERCENT CONVERT(sysname, DB_NAME()) AS TABLE_QUALIFIER, CONVERT(sysname, USER_NAME(o.uid)) AS TABLE_OWNER,

                        CONVERT(sysname, o.name) AS TABLE_NAME, CONVERT(sysname, c.name) AS COLUMN_NAME, CONVERT(int,

                            (SELECT     COUNT(*)

                              FROM          syscolumns sc

                              WHERE      sc.id = c.id AND sc.number = c.number AND sc.colid <= c.colid)) AS ORDINAL_POSITION

  FROM         dbo.sysobjects o INNER JOIN

                        dbo.syscolumns c ON o.id = c.id INNER JOIN

                        master.dbo.spt_datatype_info d INNER JOIN

                        dbo.systypes t ON d.ss_dtype = t.xtype ON c.length = ISNULL(d.fixlen, c.length) AND ISNULL(COLUMNPROPERTY(c.id, c.name, 'IsIdentity'), 0)

                        = ISNULL(d.AUTO_INCREMENT, 0) AND c.xusertype = t.xusertype LEFT OUTER JOIN

                        dbo.syscomments m ON c.cdefault = m.id AND m.colid = 1

  ORDER BY CONVERT(sysname, USER_NAME(o.uid)), CONVERT(sysname, o.name)