convert columns and value to rows

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

Transact-SQL (2000)

convert columns and value to rows

Author

Topic

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-07-14 : 17:14:06

Hi, is it possible to do a select that will, instead of returning one row, return a row that has the column name and value in it? Thanks in advance.

For example:
Cust_Name JOHN SMITH
Address 123 MAIN
City NEW YORK

Instead of
Cust_Name Address City
JOHN SMITH 123 MAIN NEW YORK

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-14 : 17:14:57

This is a presentation layer issue, generally speaking you shouldn't attempt to produce this from the data layer.

Jonathan
{0}

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-14 : 17:40:43

select t1, t2 from
(
select t1 = 'Cust_name', t2 = (select Cust_Name from tbl where User_id = @User_id), srt = 1
union all
select 'Address', (select Address from tbl where User_id = @User_id), 2
union all
select 'City', (select City from tbl where User_id = @User_id), 3
) a
order by srt

And because I did it the other way before I read the question properly

select t1, t2, t3
from
(
select srt = 1, t1=convert(varchar(50),'Cust Name'),t2=convert(varchar(50),'Address'),t3=convert(varchar(50),'City')
union all
select srt = 2,
t1 = (select Cust_Name from tbl t2 where t2.User_id = t1.User_id) ,
t2 = (select Address from tbl t2 where t2.User_id = t1.User_id) ,
t3 = (select City from tbl t2 where t2.User_id = t1.User_id) ,
from tbl
group by User_id
) a
order by srt

See why setbasedisthetruepath suggests it should be a presentation layer issue?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-14 : 18:26:38

I would actually do it this way, if not at the presentation layer:


SELECT
  ColDesc as ColumnHeading,
  CASE ColNo WHEN 1 THEN Column1
             WHEN 2 THEN Column2
             WHEN 3 THEN Column3
             ... etc ... 
             END as Value
FROM
 YourTable
CROSS JOIN
 (SELECT 'ColumnHeading1' as ColDesc, 1 as ColNo
  UNION ALL
  SELECT 'COlumnHeading2' as ColDesc, 2 as Colno
  UNION ALL
  SELECT 'COlumnHeading3' as ColDesc, 3 as Colno
  ... etc ...
  ) Columns

- Jeff

dgaylor
Yak Posting Veteran

54 Posts

Posted - 2003-07-14 : 18:30:08

Thanks for the suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources