Stored procedure to transfer the data of one row to a single column

[itarunachalam]

Hi friends,

I have a table structure like this in SQL sever 2000.

i want a stored procedure to transfer the data from "tblSurvey" table
to "tblRespondent" table.

Table - tblSurvey
----------------------------------------------------
columnHeading col1 col2 col3 col4 col5 |
----------------------------------------------------
Res1 12 11 13 111 888 |
Res2 1A 1B 1c 1d 8E |
..
..

--------------------------------------------------

I want to store this as a single column like this

Table - tblRespondent
----------------------
Respondent QuesID
----------------------
Res1 12
Res1 11
Res1 13
Res1 111
Res1 888

Res2 1A
Res2 1B
Res2 1c
Res2 Id
Res2 8E
------------------

any method is there to split the row data into column through stored
procedure
we can do this using recordset operation but it is very slow because
the table row has
more than 300 columns, I hope u understand my problem..
thanks
Arun

[Marc Gravell]

This is neither a c# nor CLI question - you should really move this to one
of the sqlserver ngs

BTW: 300 columns in a table... ouch...

Marc

[Dave Sexton]

Hi Arun,

I agree with Marc - it's probably time for some normalization, or at least
vertical partitioning.

--
Dave Sexton

<it***********@gmail.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...

Hi friends,

I have a table structure like this in SQL sever 2000.

i want a stored procedure to transfer the data from "tblSurvey" table
to "tblRespondent" table.

Table - tblSurvey
----------------------------------------------------
columnHeading col1 col2 col3 col4 col5 |
----------------------------------------------------
Res1 12 11 13 111 888 |
Res2 1A 1B 1c 1d 8E |
.
.

--------------------------------------------------

I want to store this as a single column like this

Table - tblRespondent
----------------------
Respondent QuesID
----------------------
Res1 12
Res1 11
Res1 13
Res1 111
Res1 888

Res2 1A
Res2 1B
Res2 1c
Res2 Id
Res2 8E
------------------

any method is there to split the row data into column through stored
procedure
we can do this using recordset operation but it is very slow because
the table row has
more than 300 columns, I hope u understand my problem..
thanks
Arun

[Marc Gravell]

SELECT id, 'col1', col1 FROM tblSurvey
UNION ALL SELECT id, 'col2', col2 FROM tblSurvey
/* snip */
UNION ALL SELECT id, 'col300', col300 FROM tblSurvey

..... (aarrgghh!)

Note you could output the above query as a sneaky one-off hack, such
as:
DECLARE @table sysname, @firstcol sysname
SET @table = 'tblSurvey'
SELECT @firstcol = name
FROM syscolumns
WHERE id = OBJECT_ID(@table)
AND colorder =1
SELECT CASE colorder WHEN 2 THEN '' ELSE 'UNION ALL ' END + 'SELECT ['
+ @firstcol + '], ''' + name + ''',[' + name + '] FROM [' + @table +
']'
FROM syscolumns
WHERE id = OBJECT_ID(@table)
AND colorder >= 2

Not one to run every day

;-p