October 4, 2005 at 12:38 am
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.
October 4, 2005 at 5:23 am
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:
October 5, 2005 at 1:39 am
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)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply