SQL Problem - 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

Old Forums

CLOSED - General SQL Server

SQL Problem

Author

Topic

bbfmh
Starting Member

4 Posts

Posted - 2002-06-13 : 05:10:50

I've a table named "Test" with 5 fields and the schema is as follows.

(1) select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Test'
COLUMN_NAME
-----------
ID
Content
Content1
Content2
Content3

(2) select * from Test
ID Content Content1 Content2 Content3
---------------------------------------------
1 1A 1B 1C 1D
2 2A 2B 2C 2D

How can I write my SQL statement so that I can obtain the following result in which I can read the fields' name from the schema???
----------------------------------------------------
ID=1, Content=1A, Content=1B, Content=1C, Content=1D
ID=2, Content=2A, Content=2B, Content=2C, Content=2D

Wanderer
Master Smack Fu Yak Hacker

1168 Posts

Posted - 2002-06-13 : 05:49:58

must you run select * from the table ? Are you always going to run dynamin SQL against an unknown table layout ?

bbfmh
Starting Member

4 Posts

Posted - 2002-06-13 : 10:50:02

Yup, I'm going to make a dynamic SQL that can read the content as well as the field name of a table so that I can get a result like
[Field Name1]=[Content1], [Field Name2]=[Content2], [Field Name3]=[Content3]..... What can I do???

bbfmh
Starting Member

4 Posts

Posted - 2002-06-13 : 11:21:51

Actually, I'm trying to write a trigger event on tables in which I'm going to store the fields changed during Insert/Update/Delete statement.

For example,
I'm going to insert a row into the following table(Person)...
PersonID Name Telephone
-----------------------------
1 John 3344567
2 Peter 9988765

3 Mary 6655432 <----- Insert into Person values ('3' ,'Mary', '6655432')

Once table "Person" trigger an Insert event, new item will be added to another table "Log" as follows
LogID Action EventInfo
----------------------------------------------------------
3 Insert PersonID=1, Name=John, Telephone=3344567
4 Insert PersonID=2, Name=Peter, Telephone=9988765

5 Insert PersonID=3, Name=Mary, Telephone=6655432 <--- Trigger event...

What can I do to make the "PersonID=3, Name=Mary, Telephone=6655432" in which the description of the EventInfo field(PersonID, Name and Telephone) is get from the table schema of table "Person" with concatenation by delimited string between fields like the ", " in the example???

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-13 : 11:56:50

Triggers have access to the inserted table and it looks like all your going for is a little data format.

Create trigger bobsyouruncle
on Person
for insert
as
begin
declare @action varchar(500)
select @action = 'PersonID='+(select personID from inserted)+',Name='+(select name from inserted)+',Telephone='+(select telephone from inserted)
insert into [log](action,eventinfo) values('insert',@action)
end

That will give you a column that looks like what you want. I don't really recommend storing data in an array like that... but for simple log purposes it shouldn't be too bad. and I would really suggest you don't use log as a table name, but I'm assuming you just said that as an example. I think this is what you wanted.. tell me if otherwise

Edit - Made it look more like what you wanted ;)
-----------------------
The best answer = just do as rob or page47 say.

Edited by - M.e. on 06/13/2002 11:59:40

bbfmh
Starting Member

4 Posts

Posted - 2002-06-14 : 10:21:45

Thanks for your response... but I would like to ask whether I can make use of the column names from the schema of table "Person", e.g. select Column_Name from Information_Schema where Table_Name='Person'
because I would like to obtain the result dynamically instead of quoting each column name ('PersonID', 'Name' and 'Telephone') in my trigger event. Is it the only way that I need to hard-code those column name in my statement???

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-14 : 11:57:52

you can, but it gets ugly fast. Borrowing some code I found on page47 and from Graz....

declare @collist varchar(8000)
select
@collist = coalesce(@collist + ',','') + column_name
from
information_schema.columns
where
table_name = 'person'

that will bring back 'personid,name,telephone' in your example.

Graz's parse array can go through each column name. You'll need to use dynamic sql to get results back such as
select @columnname from inserted

boy this is getting stranger and stranger. You'll need to execute that dynamic sql and put the value into a temp table.... more and more fun.

You will then have a variable that has the column name and a value in a temp table that will have the value for that column name.
Heh, loop repeat.

Lemme make the code for a bit

-----------------------
Take my advice, I dare ya

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-06-14 : 12:00:34

Heh, here goes.. haven't tested

Create trigger bobsyouruncle
on Person
for insert
as
begin
---Get the column list
declare @collist varchar(8000)
select
@collist = coalesce(@collist + ',','') + column_name
from
information_schema.columns
where
table_name = 'person'
--Make a temp table
create table #insertedvalue(
value varchar(500),
column_name varchar(500)
)
-- declare @action and set to default
declare @action varchar(5000)
set @action = ''
-- Created by graz@sqlteam.com (giving him da credit he deserves)
set nocount on
-- @Array is the array we wish to parse
-- @Separator is the separator charactor such as a comma
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
declare @array varchar(1000)
declare @separator varchar(1)
declare @value varchar(500)
-- For my loop to work I need an extra separator at the end. I always look to the
-- left of the separator character for each array value
set @separator = ','
set @array = @collist --setting our column listing to be the array
set @array = @array + @separator

-- Loop through the string searching for separtor characters
while patindex('%' + @separator + '%' , @array) <> 0
begin

-- patindex matches the a pattern against a string
select @separator_position = patindex('%' + @separator + '%' , @array)
select @array_value = left(@array, @separator_position - 1)

-- This is where you process the values passed.
-- Replace this select statement with your processing
-- @array_value holds the value of this element of the array
--Okay, my bit here, dynamic SQL YAY
select @value = 'select '+ @array_value+',''+@array_value+'' from inserted'
Insert into #insertedvalue exec (@value)
--Now that you have the value in the temp table build the @action var
select @action = @action + ' ' + @array_value+'=' +(select value from #insertedvalue where column_name = @array_value) +','

-- This replaces what we just processed with and empty string
select @array = stuff(@array, 1, @separator_position, '')
end
--Finally do the insert
insert into [log](action,eventinfo) values('insert',@action)
end
--Kill me now

Edit -- missed the seperator
My guess is no ones gonna bother replying to this one. Prolly take to long to fix all the errors there.
-----------------------
Take my advice, I dare ya

Edited by - M.e. on 06/14/2002 13:15:34

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources