必须在Selecing列中指定顺序. [英] have to specify the order in Selecing columns.

查看:89
本文介绍了必须在Selecing列中指定顺序.的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,

我创建了一个像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屋!

查看全文
相关文章
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆