May 25, 2006 at 5:38 am
Hi All (SQL Server 2000)
I need to select a set of fields from a student table (about 30 fields), but I need them returned back as individual Rows eg:
Fred
Smith
Class 2b
16/06/1990
Tutor: Mr Bloggs
The only way I can see to do this is a series of unions but this will be really slow, won't in? If I have to stick with this approach, can someone help with the best way.
Thanks
CCB
May 25, 2006 at 6:58 am
Not pretty. You will have to cast all columns (hope you don't have a CLOB in there).
Since you did not provide a DDL, I will fake something:
select a.au_id,
c.COLUMN_NAME,
case c.COLUMN_NAME
when 'au_lname' then a.au_lname
when 'au_fname' then a.au_fname
when 'phone' then a.phone
when 'address' then a.address
when 'city' then a.city
when 'state' then a.state
when 'zip' then a.zip
when 'contract' then cast( a.contract as char(1) )
end as COLUMN_VALUE
from authors as a
join INFORMATION_SCHEMA.COLUMNS as c
on c.TABLE_NAME = 'authors' and c.COLUMN_NAME <> 'au_id'
You should cast all columns cleanly to varchar(8000) (or TEXT if you have a CLOB in one of the columns). You can of course sort on the PK + the ordinal of the columns.
Hope it helps.
May 25, 2006 at 7:19 am
That Worked.
thanks
CCB
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply