having problem in creating table from another table

Hallo Abhishekagrw...,

the only possible solution is dynamic SQL. Maybe this code will work for you:

-- Create the demo table
CREATE TABLE dbo.foo
(
        Depot_Id        int     IDENTITY        PRIMARY KEY,
        Depot_Name      char(1) NOT NULL
);

-- Insert a few records in it
INSERT INTO dbo.foo (Depot_Name)
VALUES
('A'),
('B'),
('C')
GO

-- declare a variable which will be used for execution string
DECLARE @stmt   nvarchar(4000) = '';
SELECT  @stmt = @stmt + CASE WHEN DATALENGTH(@stmt) = 0
                                                         THEN Depot_Name
                                                         ELSE ', ' + Depot_Name
                                                END + ' int'
FROM    dbo.foo;

SET             @stmt   =       'CREATE TABLE dbo.foo_pivot (' + @stmt + ');'
-- Check the statement!
SELECT  @stmt;

-- Execute the statement
EXEC    sp_executeSQL @stmt;

---

Uwe Ricken



MCM SQL Server 2008

MCSE - SQL Server 2012

MCSA - SQL Server 2012



db Berater GmbH

http://www-db-berater.de

SQL Server Blog (german only)