CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2008
angfreak
angfreak

Reputation: 1003

Dynamically create column in Table

I have Create a table within My procedure. I have start date and end date value. I want create columns with this date range like if start date is 15 july 2013 and end date is 18 july 2013 then there are 4 columns(july1,july2,july3,july4) are created.

How can I achieve this. I have used sql server 2008.. please help me.....

Sir from front end i have fromdate and Todate fields then on submit I have called a Sp from my C# code, Now I want to create a table within my Proc. table must have no. of columns as with no. of days exists between given dates.

Upvotes: 2

Views: 773

Answers (2)

Joe Taras
Joe Taras

Reputation: 15399

Try this:

DECLARE @dtmin date
DECLARE @dtmax date
DECLARE @dynsql nvarchar(3000)
DECLARE @colname varchar(20)
SET @dynsql = N'CREATE TABLE trial('
SET @dtmin = {d '2013-07-15'}
SET @dtmax = {d '2013-07-18'}
DECLARE @currdate date
SET @currdate = @dtmin

WHILE (@currdate <= @dtmax)
BEGIN
    SET @colname = DATENAME(month, @currdate) + CONVERT(varchar, DATEPART(day, @currdate))

    SET @dynsql = @dynsql + @colname + ' varchar(10)'

    IF (@currdate < @dtmax)
    BEGIN
        SET @dynsql = @dynsql + ', '
    END
    ELSE
    BEGIN
        SET @dynsql = @dynsql + ')'
    END

    SET @currdate = DATEADD(day, 1, @currdate)
END

EXEC sp_executesql @dynsql

P.S.: I don't know the reason of your request, but generally is not correct create table in this way because the information you want to put into columns, should be put in the rows.

Example: If I want to store sales day by day, the correct table, I named SALES, is: SALES (id varchar(36), dt datetime, amount decimal(19,2)) instead of SALES (ID VARCHAR(36), dt1 decimal(19,2), dt2 decimal(19,2)... and so on)

Because if you want to change your period you must rewrite your table, if you want query your table you must write several queries for each situation. It's very hard work on this table.

Please, reconsider you choice.

Have a nice day.

Upvotes: 2

LoztInSpace
LoztInSpace

Reputation: 5697

You may have accepted the answer above, but even wanting to do this goes well against the principles behind relational databases. I'd seriously consider what you're trying to do and your approach.

Upvotes: 0

Related Questions