results from query including column names

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)

results from query including column names

Author

Topic

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-23 : 11:48:42

We have a table :

Create table t1(
IDKEY int,
abc varchar(15),
NGH varchar(15),
ZOP Varchar(15)
)

for example I have in this table
IDKEY,abc,ngh,zop
1,55,66,77
2,33,44,55

I would like the output to look like

IDKEY,ColName,ColValue

so for this example I would get back

1, ABC, 55
2, ABC, 33
1, NGH, 66
2, NGH, 44
1, ZOP, 77
2, ZOP, 55

My only theory was to make a temp table and populate it as
insert into #temp
select IDKEY, 'NGH', NGH
go
insert into #temp
select IDKEY, 'ABC', ABC
go

and so on...

Any ideas of a way this could be automated? We've got a cursor idea... but I hate that. I think this post has been in the forums before, I'm trying to locate that post though

-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 10/23/2002 12:00:34

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-10-23 : 12:08:14

How bout...


select IDKEY, 'NGH', NGH 
union
select IDKEY, 'ABC', ABC 
union
select IDKEY, 'ZOP', ZOP
go

Jay White
{0}

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-10-23 : 12:08:37

Could do a UNION:

SELECT IDKey, 'abc' AS Colname, abc AS ColValue FROM t1
UNION ALL
SELECT IDKey, 'NGH' AS Colname, NGH AS ColValue FROM t1
UNION ALL
SELECT IDKey, 'ZOP' AS Colname, ZOP AS ColValue FROM t1

You'll need to do some dynamic SQL if the number of columns varies.

M.E.
Aged Yak Warrior

539 Posts

Posted - 2002-10-23 : 12:15:09

Okay, thats kinda what I figured... I guess I missed part of the question. We got 6 different tables all with different numbers of columns and different column names.

I've made this, but it is cursor based (stupid iterative). In @table I can put any table name and it will list the data for any table.

declare @table varchar(50), @SQL varchar(5000), @col varchar(50)
select @table = 'well'
declare seq_cur cursor
for
select column_name from information_schema.columns where table_name = @table
for read only
open seq_cur
fetch next from seq_cur into @col
while @@fetch_status = 0
begin
select @sql = 'Select UWI, ''' + @col + ''','+@col +' from ' +@table
--select @sql
exec (@sql)
fetch next from seq_cur into @col
end
close seq_cur
deallocate seq_cur

Edit : forgot the question... any setbased method for this? it is the truepath after all
-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - m.e. on 10/23/2002 12:15:51

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources