Click here to Skip to main content
14,302,993 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hi Every body,

I created one table like EMP. assume that consists of 3 columns like Id,Name,Salary.

Emp Table with Data:
Id Name Salary
---------------------------------
1 John 500
2 Jaanu 1000
3 Ram 600


i am expecting the output like in this passion


Expected Output:
Id Id Name Name Salary Salary
------------------------------------------------------------------
1 1 John John 500 500
2 2 Jaanu Jaanu 1000 1000
3 3 Ram Ram 600 600

In Such a way table consists of 40 columns i dont want to write hard codedly.
there are few more tables in that passion.


Is is possible. Help me. Thanks in advance.
Posted
Comments
Ankur\m/ 1-Feb-12 0:40am
   
What do you mean by writing hard-coded? Do you mean you want to use something like select * from tableName?
Christian Graus 1-Feb-12 0:53am
   
Why on earth do you want each value twice ? You can display it that way, without getting it twice from the server, but I see no reason for you to want to actually get the DB to fill it twice. Does select id, id as id2, name, name as name2, salary, salary as salary2 not work ? I think it should, despite being pointless.
Rate this:
Please Sign up or sign in to vote.

Solution 1

select id, id as id2, name, name as name2, salary, salary as salary2 has to be the answer, although I don't see the point of the question.
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Here it is :
declare @TableName varchar(max)
set @TableName = 'TableName'
declare @selectstatement varchar(max)

SELECT @selectstatement = ISNULL(@selectstatement, '') +  syscolumns.name +','+syscolumns.name  +','
    FROM sysobjects 
    JOIN syscolumns ON sysobjects.id = syscolumns.id
    JOIN systypes ON syscolumns.xtype=systypes.xtype
   WHERE sysobjects.xtype='U' and sysobjects.name= @TableName
ORDER BY sysobjects.name,syscolumns.colid
set @selectstatement =  'select '+substring(@selectstatement, 1 , LEN(@selectstatement) -1)+' from '+ @TableName

 exec (@selectstatement)


Hope it helps.
   
Comments
naveenvenkanna 17-Feb-12 1:12am
   
Thanks alot.
perfect work amir.
Amir Mahfoozi 17-Feb-12 23:36pm
   
You're welcome. :)
Rate this:
Please Sign up or sign in to vote.

Solution 3

/****** Object:  StoredProcedure [dbo].[FindTableValueByRepeatingColumns]    Script Date: 02/01/2012 12:20:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[FindTableValueByRepeatingColumns]
(
@TableName as NVARCHAR(MAX)
)
AS
BEGIN

DECLARE @Columns as NVARCHAR(MAX)
DECLARE @SQuery as NVARCHAR(MAX)
SET @Columns=''
SET @SQuery =''

SELECT @Columns=@Columns + Name + ','  FROM
(
select SC.name,Colid from syscolumns SC inner join sysobjects SO on SC.id=SO.id
 WHERE SO.name=@TableName
Union all
select SC.name,Colid from syscolumns SC inner join sysobjects SO on SC.id=SO.id
 WHERE SO.name=@TableName
) AS tbl1 Order by colid

SET @SQuery ='SELECT ' + SUBSTRING(@Columns,1,LEN(@Columns)-1)  + ' FROM ' + @TableName

EXEC SP_EXECUTESQL @SQuery 

END
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100