record list

Last Post 16 Nov 2011 08:37 AM by dbandee. 5 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
dbandee
New Member
New Member

--
15 Nov 2011 03:48 AM
good morning
I have table like this

CustName, CustDescField  CustDescValue
Smith         Height              5'6''
Smith         Weight             160
Smith         Location           USA
Johnson     Height              5'8''
Johnson     Weight            195
Johnson     Location          Canada

I want to flip it to look like this

FieldID       Smith     Johnson
Height       5'6''        5'8''
Weight      160        195
Locaiton    USA        Canada

Number of records will change so this need to be dynamic
Has anyone done something like this?
rm
New Member
New Member

--
15 Nov 2011 04:41 AM
Does pivot work for you?
dbandee
New Member
New Member

--
15 Nov 2011 12:45 PM
no, i dont think so. pivot works of hardcoded column names

so i figured it out
i am building the table dynamically; looping trough records and making them columns
the issue i have now row size exceeds 8kb row width.

any thoughts?
gunneyk
New Member
New Member

--
15 Nov 2011 03:58 PM
I assume you have several large text type columns right? Just make them varchar(MAX) and you can exceed 8000 bytes when you need to.
dbandee
New Member
New Member

--
16 Nov 2011 05:43 AM
tried that
still getting same error
dbandee
New Member
New Member

--
16 Nov 2011 08:37 AM
all right
finally figured it out
here is what i am doing

1. Identify what my columns will be (basically records from some table)
2. loop thru them and create table on the fly (so dont matter how many records you have, i have 700 records so my new table will be 700 columns)
3. Data type for each field varchar (max)
4. Run this EXEC sp_tableoptions 'MyRaduculusTable', 'large value types out of row', '1'
5. now run your updates to these fields.

hope this helps


Acceptable Use Policy
---