datatype to store column name - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

datatype to store column name

Author

Topic

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-23 : 20:24:21

Hi brothers
I want to know the datatype which is used to store the column name of table. Because I want to use that column name in my Update statement
Plz help me and suggest me to do..

sample
Declare @colname ?????
Set @colname=(SELECT c.name FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name='Acomp' and c.name=1)

Update Acomp
Set @colname=5
Where empid=101

my column will be like numbers eg.,[1],[2],[3]...
wherevr my input values and colname mayches I want to update the value
this is the requirement

Plz let me know is that possible...?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-23 : 20:36:31

you can use varchar(256) or sysname

KH
[spoiler]Time is always against us[/spoiler]

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-23 : 20:41:57

Hi Khtan ,
thanks for reply
I done this.....It is not worked for me

DECLARE @temp sysname
SET @temp=(SELECT c.name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE t.name='Acomp' and c.name=1)

update AComp
set @temp=5
where empid=101

I thnk it taking @temp as some temp variable not as column...
help me out man...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-23 : 23:08:31

the problem is this. You can do this, you will need to use Dynamic SQL

update AComp
set @temp=5
where empid=101

Read this The Curse and Blessings of Dynamic SQL

Why do you need to do this ?

KH
[spoiler]Time is always against us[/spoiler]

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-23 : 23:33:02

The thing is...
I have column..of dates from 1 to 31 and I am have timebooking by employees in rows eg.,

1-Jul-2010 8 hrs
2-Jul-2010 9 hrs.......
Here am tryna to change from row to column.
So here my column and my dates ie., 1,2,3 are identical
I'll take these as reference and will update timebooking values
to that table...
understood bro...!!!
If possible can you provide more links on dynamic SQL...its my kind request

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-23 : 23:47:44

can you post the table structure and sample data with the required output ?

KH
[spoiler]Time is always against us[/spoiler]

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-24 : 00:17:52

The below table has the timebooked by employees data's in rowwise
Emplid Date bookedhrs
100 1-Jul-2010 8
100 2-Jul-2010 9
100 3-Jul-2010 7

and another table structure will be like this
Emplid [1] [2] [3] [4] [5] [6]......[31]

I want to update the bookedhrs data's from first table to second tabl with respect to date..Using datepart I will get day..with this day am going to compare the column name of second table and gonna insert booked hrs to second table.
let me know If u can't understand my wordings...!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-24 : 00:51:48

[code]
declare @timebooked table
(
[Empid] int,
[Date] datetime,
[BookedHrs] int
)

insert into @timebooked
select 100, '2010-07-01', 8 union all
select 100, '2010-07-02', 9 union all
select 100, '2010-07-03', 7

select [Empid],
YearMth = dateadd(month, datediff(month, 0, [Date]), 0),
[1] = sum(case when datepart(day, [Date]) = 1 then [BookedHrs] else 0 end),
[2] = sum(case when datepart(day, [Date]) = 2 then [BookedHrs] else 0 end),
[3] = sum(case when datepart(day, [Date]) = 3 then [BookedHrs] else 0 end),
[4] = sum(case when datepart(day, [Date]) = 4 then [BookedHrs] else 0 end),
[5] = sum(case when datepart(day, [Date]) = 5 then [BookedHrs] else 0 end),
-- . . . .
[31] = sum(case when datepart(day, [Date]) = 31 then [BookedHrs] else 0 end)
from @timebooked
group by [Empid], dateadd(month, datediff(month, 0, [Date]), 0)
[/code]

KH
[spoiler]Time is always against us[/spoiler]

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-24 : 01:50:33

Great Khtan..Thanks a lot for ur effort
According to you...I had modified my Update command as
UPDATE main
SET [1]=(SELECT SUM(BookedHrs) FROM @timebooked WHERE DATEPART(DAY,[DATE])=1 GROUP BY Empid,[Date]),
[2]=(SELECT SUM(BookedHrs) FROM @timebooked WHERE DATEPART(DAY,[DATE])=2 GROUP BY Empid,[Date]),
[3]=(SELECT SUM(BookedHrs) FROM @timebooked WHERE DATEPART(DAY,[DATE])=3 GROUP BY Empid,[Date])
FROM AComp main JOIN @timebooked book ON main.empid=book.Empid

Is that only way we want to manually do so
SET [4]=
[5]=
[6]=
.
.
[31]=

any other shortcuts?...can't we do keep inside while loop
and increment date by comparing the column of table from Sys.columns for the table Acomp which has column 1,2,3....31 and dates of Timebooked
Plz guide me khtan..!!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-24 : 03:37:20

[code]
update m
set [1] = t.[1],
[2] = t.[2],
. . .
from main m
inner join
(
select [Empid],
YearMth = dateadd(month, datediff(month, 0, [Date]), 0),
[1] = sum(case when datepart(day, [Date]) = 1 then [BookedHrs] else 0 end),
[2] = sum(case when datepart(day, [Date]) = 2 then [BookedHrs] else 0 end),
[3] = sum(case when datepart(day, [Date]) = 3 then [BookedHrs] else 0 end),
[4] = sum(case when datepart(day, [Date]) = 4 then [BookedHrs] else 0 end),
[5] = sum(case when datepart(day, [Date]) = 5 then [BookedHrs] else 0 end),
-- . . . .
[31] = sum(case when datepart(day, [Date]) = 31 then [BookedHrs] else 0 end)
from timebooked
group by [Empid], dateadd(month, datediff(month, 0, [Date]), 0)
)t on m.Empid = t.Empid
and m.[date] = t.[YearMth]
[/code]

KH
[spoiler]Time is always against us[/spoiler]

karthick.amace
Starting Member

23 Posts

Posted - 2010-07-24 : 05:08:33

Thanks a lot man...we will end by this...
Appreciate your response..Keep it up and do this service lifelong

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-07-27 : 16:05:48