add columns names to the result of my select from text

Question

I want to produce such output from my table. In fact I want create a function or procedure that get the name of the table and its columns (it also can be retrieved from sys.tables) and create such an output as a single string: imagine [tablename] has 3 row:

Tablename (code="1",column1="Column1value",column2="Column2value",column3="Column3value")
Tablename (code="2",column1="Column1value",column2="Column2value",column3="Column3value")
Tablename (code="3",column1="Column1value",column2="Column2value",column3="Column3value")

@MarkByers - I believe it is MS-SQL (T-SQL) as he refers to 'systables' — Geoffrey, Aug 05 '12 at 10:58
There doesn't seem to be anything wrong with wanting to do such a thing. What kind of problem are you having with it? You see, this site is about questions and answers. What is *your* question? — Andriy M, Aug 05 '12 at 11:11
@Geoffrey: That would have been my guess too, but I wasn't sure if there might be other databases that also use systables, such as DB2 for example. In any case, I think it's always best to specify the database and version if it's anything that depends on database specific features. — Mark Byers, Aug 05 '12 at 11:13
@AndriyM i want to know how can i do that?!!! you know for each table there are different columns in name and number,so that SP should handle that.in other hand the name of columns are dynamcly diffrenet for each table — eshirvana, Aug 05 '12 at 11:30
All right, but what *particularly* are you having difficulties with? Is it the output generation part? Is it the dynamic part? What have you tried and what is the stumbling block at the moment? Usually people aren't very keen on answering broad questions, so please try to be more specific about your present problem. — Andriy M, Aug 05 '12 at 17:38
@AndriyM i have problem with the whole query that should be written.i have no idea that i should start from where !!! — eshirvana, Aug 06 '12 at 08:37

score 0 · Answer 1 · answered Aug 05 '12 at 17:45

We can get column names of the passed table name from sys table, and then to print table values for each row, we can use ROW_NUMBER and print the string by appending column values for each rows and get the final output. There would be to For each loop nested. Outer foreach loop will use ROW_NYMBER and will be executed for all rows in the table and inner loop will print column values in one string as required.

AnandPhadke · Accepted Answer · 2012-08-06T11:19:17.330

Try this Procedure to get the results in the required format.

CREATE Procedure GetTableValues(@tblname varchar(10))
As
BEGIN
DECLARE @idmin int,@idmax int,@colCnt int,@i int
DECLARE @outtbl TABLE(col1 varchar(1000))
DECLARE @tblval TABLE(col1 varchar(1000))
DECLARE @str varchar(1000),@colname varchar(100),@colvalue sql_variant
SET @str=''
SET @i=1
select @colCnt=COUNT(*) from sys.columns where object_id=object_id(@tblname)

IF EXISTS(select * from sys.tables where name='temp')
DROP TABLE temp
EXEC('select ROW_NUMBER() over (order by (select 0)) as rno,* into temp from '+@tblname)

select @idmin=MIN(rno),@idmax=MAX(rno) from temp

while(@idmin <=@idmax)
begin
while(@i <=@colCnt)
begin
select @colname=name from sys.columns where column_id=@i and object_id=object_id(@tblname)
insert into @tblval 
exec('select '+@colname+' from temp where rno='+@idmin)
select @colvalue=col1 from @tblval  
SET @str=@str+','+@colname+'="'+cast(@colvalue as varchar)+'"' 
SET @i=@i+1
end
insert into @outtbl 
select @tblname+'('+STUFF(@str,1,1,'')+')'
SET @idmin=@idmin+1
SET @i=1
SET @str=''
end
select * from @outtbl 
END


exec GetTableValues 'test7'

add columns names to the result of my select from text

2 Answers2