必须在Selecing列中指定顺序. [英] have to specify the order in Selecing columns.
问题描述
大家好,
我创建了一个像EMP这样的表.假设它由3列组成,例如Id,Name,Salary.
包含数据的Emp表:
ID名称薪水
---------------------------------
1约翰500
2 Jaanu 1000
3公羊600
我期待着这种热情的输出
预期输出:
Id Id名称名称工资工资
-------------------------------------------------- ----------------
1 1约翰·约翰500 500
2 2 Jaanu Jaanu 1000 1000
3 3公羊公羊600600
在这种方式下,表格由40列组成,我不想用硬编码编写.
这种激情还剩有几张桌子.
是可能的.帮我.预先感谢.
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.
推荐答案
选择id,id作为id2,名称,name作为name2,salary,salary作为salary2,虽然我没有,但必须回答"没看到问题的重点.
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.
这里是:
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.
/****** 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
这篇关于必须在Selecing列中指定顺序.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!