Wrox Programmer Forums
|
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 17th, 2007, 08:54 AM
Authorized User
 
Join Date: Dec 2004
Posts: 18
Thanks: 0
Thanked 0 Times in 0 Posts
Default first 10 columns in table

Hi,

I have a table with more than 50 columns, but i want to select first 20 columns alone.

anybody help me to solve this without specifying the columns name in the select stmt?.

Vijay.

 
Old July 17th, 2007, 12:25 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Yep... but ya gotta tell me why you want to do this, please...

... obviously, you'll need to change the constant for the table name variable and the object of the USE statement... :D

Code:
USE PUBS
GO

DECLARE @TableName VARCHAR(8000)
    SET @TableName = 'dbo.Authors'

DECLARE @SQL1 VARCHAR(8000)
DECLARE @SQL2 VARCHAR(8000)
DECLARE @SQL3 VARCHAR(8000)

 SELECT @SQL1 = 'SELECT '


 SELECT TOP 20 
        @SQL2 = ISNULL(@SQL2+',','') + Name
   FROM dbo.SysColumns 
  WHERE ID = OBJECT_ID(@TableName)
  ORDER BY ColID

 SELECT @SQL3 = ' FROM '+@TableName

  PRINT @SQL1+@SQL2+@SQL3

   EXEC (@SQL1+@SQL2+@SQL3)




--Jeff Moden
 
Old July 17th, 2007, 12:26 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Also, I just noticed, you asked for 20 in a post titled for 10... you may have to make another tweek.

--Jeff Moden
 
Old July 18th, 2007, 08:38 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 106
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can also use paging.......

Declare @PageIndex int
Declare @PageSize int

Set @PageIndex = 1
Set @PageSize =10



WITH [MyTable ORDERED BY ROWID] AS
(
Select ROW_NUMBER() OVER (ORDER BY Table1.ModifiedDate DESC,Table1.CreatedDate DESC ) AS ROWID,
                 Table1.Table1ID,Table1.Name,Table1.TableDescriptio n
            From Table1


          )
        SELECT * FROM [MyTable ORDERED BY ROWID]
            WHERE ROWID between (@PageIndex - 1) * @PageSize + 1 and @PageIndex*@PageSize




Bijgupt
 
Old July 18th, 2007, 09:08 PM
Friend of Wrox
 
Join Date: Oct 2006
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

Ummmm... paging works fine for getting the first (or any) 10 rows... but the question was for getting the first 10 COLUMNS.

--Jeff Moden





Similar Threads
Thread Thread Starter Forum Replies Last Post
Fixed table columns darkhalf HTML Code Clinic 1 August 27th, 2008 02:05 AM
Splitting columns and inserting to a Table praveennk84 SQL Server 2000 3 December 1st, 2006 08:16 AM
Sort Table Columns by Table Header Attribute omrieliav XSLT 4 June 7th, 2006 01:05 AM
Duplicating Columns on same table yuvalk SQL Language 2 May 11th, 2004 06:22 AM
merging 2 columns in the same table comet SQL Language 3 July 17th, 2003 08:13 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.