asked on

How do you reorder the ORDINAL_POSITION of a table?

I have create a table that was created from a pivot sp that all works great the problem is I need to write a generic select that returns my pivot table with the first 2 columns in position 1 and 2. This is because Column1 and 2 are guarenteed,

See data below:
my fields are as follows:
Field Ordinal Position
RackInfoID      1
Library A       2
Library B      3
Not Libraried      4
Saved Library      5

My data looks like this:
RackInfoID Library A Library B Not Libraried Saved Library
ABC009407      NULL        NULL        1        NULL
ABC009408 NULL        NULL        NULL        1
ABC009409      NULL        NULL        1        NULL

So I want to Return Column in this Order:
RackInfoID      1
Not Libraried      2
Library A       3
Library B      4
Saved Library      5

chapmandew

To change the ordinal positions of the columns in the table, you'll need to drop it and recreate it. SQL Server doesn't have any internal constructs to reorder by columns. When you do it in management studio, it creates a new table for you and swaps them out.

TraciShultz

ASKER

I am usiong brut forse to change the ordinal position , I created the following SP. Which cretes a temp table and and through a cursor I am trying to add the additional fields. but I get the following Error:
"Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted."
Any idea's would be great.

ALTER PROCEDURE [dbo].[LibraryListEdit_CreateTempLibraryNewPivot]
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

      DECLARE @ColumnName char(100)
      DECLARE @SQL VARCHAR(8000)

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]

      CREATE TABLE [dbo].[TempLibraryNew](
            [RackInfoID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [Not Libraried] [int] NULL
      ) ON [PRIMARY]


      DECLARE c1 CURSOR READ_ONLY
      FOR
      SELECT TOP (100) PERCENT c.name AS ColumnName
      FROM sys.sysobjects AS o INNER JOIN
                                      sys.syscolumns AS c ON o.id = c.id
      WHERE (o.type = 'U') AND (o.name = N'TempLibrary') AND (c.name <> N'RackInfoID') AND (c.name <> 'Not Libraried')
      ORDER BY c.colorder

      OPEN c1

      FETCH NEXT FROM c1
      INTO @ColumnName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @SQL = 'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] AS int NULL'
            PRINT @SQL
      EXECUTE (@SQL)
            FETCH NEXT FROM c1
            INTO @ColumnName
      END

      CLOSE c1
      DEALLOCATE c1
END

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

TraciShultz

ASKER

Thanks so much. this really helped me out of a jam...

TraciShultz

ASKER

Here is the final solution.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:            TShultz
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[LibraryListEdit_CreateTempLibraryNewPivot]
      -- Add the parameters for the stored procedure here
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

-- Insert statements for procedure here
      DECLARE @ColumnName char(100)
      DECLARE @SQL VARCHAR(8000)

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]

      CREATE TABLE [dbo].[TempLibraryNew](
            [RackInfoID] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
            [Not Libraried] [int] NULL
      ) ON [PRIMARY]

      DECLARE c1 CURSOR READ_ONLY
      FOR
      SELECT TOP (100) PERCENT c.name AS ColumnName
      FROM sys.sysobjects AS o INNER JOIN
                                      sys.syscolumns AS c ON o.id = c.id
      WHERE (o.type = 'U') AND (o.name = N'TempLibrary') AND (c.name <> N'RackInfoID') AND (c.name <> 'Not Libraried')
      ORDER BY c.colorder

      OPEN c1

      FETCH NEXT FROM c1
      INTO @ColumnName

      WHILE @@FETCH_STATUS = 0
      BEGIN
      SET @SQL = 'ALTER TABLE [dbo].[TempLibraryNew] ADD [' +RTRIM(@ColumnName ) +'] int NULL'
            PRINT @SQL
      EXECUTE (@SQL)
            FETCH NEXT FROM c1
            INTO @ColumnName
      END

      CLOSE c1
      DEALLOCATE c1

      INSERT INTO TempLibraryNew
      SELECT dbo.TempLibrary.*
      FROM dbo.TempLibrary

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibrary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibrary]

      SELECT * INTO TempLibrary
      FROM TempLibraryNew

      if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TempLibraryNew]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
      drop table [dbo].[TempLibraryNew]


END