Get pairs of column name - value

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 2005 Forums

Transact-SQL (2005)

Get pairs of column name - value

Author

Topic

alto93
Starting Member

2 Posts

Posted - 2010-03-09 : 15:36:14

I need to query table for one record only but get the results as lines of key-value when the key is the column name and the value is he value in that column.
e.g. I have a table like this:
------------------
id | c1 | c2 | c3 |
------------------
1 | aa | bb | cc |
------------------
2 | dd | ee | ff |
------------------

the query:
SELECT <....> FROM Table where id=1

should bring:
c1, aa
c2, bb,
c3, cc,

Any ideas hot to do it?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-03-09 : 16:47:03

I cannot imagine why you would want to do that but...

[CODE]select 'c1', c1
from Table
where id = 1

UNION ALL

select 'c2', c2
from Table
where id = 1

UNION ALL

select 'c3', c3
from Table
where id = 1[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-09 : 16:56:02

Look for UNPIVOT to do that:
http://msdn.microsoft.com/en-us/library/ms177410(SQL.90).aspx

No, you're never too old to Yak'n'Roll if you're too young to die.

alto93
Starting Member

2 Posts

Posted - 2010-03-09 : 17:25:47

quote:
Originally posted by Bustaz Kool

I cannot imagine why you would want to do that but...

[CODE]select 'c1', c1
from Table
where id = 1

UNION ALL

select 'c2', c2
from Table
where id = 1

UNION ALL

select 'c3', c3
from Table
where id = 1[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Thanks, it can be work but the problem is that I have a lot of views with a lot of columns so it will take me ages to create the queries, is there a way to do it automatically?

The reason for that is that I have to dump the vies data to a structure for specific program that need specific input.

haroon2k9
Constraint Violating Yak Guru

328 Posts

Posted - 2010-03-09 : 23:23:01

quote:
Originally posted by alto93

quote:
Originally posted by Bustaz Kool

I cannot imagine why you would want to do that but...

[CODE]select 'c1', c1
from Table
where id = 1

UNION ALL

select 'c2', c2
from Table
where id = 1

UNION ALL

select 'c3', c3
from Table
where id = 1[/CODE]

=======================================
Few things are harder to put up with than the annoyance of a good example. (Mark Twain)

Thanks, it can be work but the problem is that I have a lot of views with a lot of columns so it will take me ages to create the queries, is there a way to do it automatically?

The reason for that is that I have to dump the vies data to a structure for specific program that need specific input.

Hi,
Good Day
you seen the link provided by webfred (Ref:Above).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources