Solved

How do I convert multiple column values into a single list of row values in SQL Server 2000?

Posted on 2008-10-25
7
246 Views
Last Modified: 2010-04-21
I have data in a table as follows:
ID  Answer1   Answer2...Answer15
_____________________
1   test1          test2...        test15

For each record, I want to create a user defined function in SQLServer 2000 that puts the values of each individual column into LIST like:
Answer
test1
test2
test3
test4
etc.



The purpose of this is so that I can create a radiobuttonlist in asp.net vb so that I can just have a list the user can select an answer for....preferably, I would like to create an Id for each.
ID     Answer
1       test1
2       test2
3       test3
4       test4



Please help...I know there are PIVOT functions in sql server 2005, but I am NOT using 2005...I am using SQL Server 2000

Thanks so much.
0
Comment
Question by:bolenka
  • 4
  • 3
7 Comments
 
LVL 13

Accepted Solution

by:
sm394 earned 500 total points
ID: 22803844
try that
select  ID , Answer1 AS Answer
from yourTable
where  ID=1
UNION
select  ID , Answer2 AS Answer
from yourTable
where  ID=1
UNION
select  ID , Answer3 AS Answer
from yourTable
where  ID=1

.......
select  ID , Answern AS Answer
from yourTable
where  ID=1
0
 

Author Comment

by:bolenka
ID: 22803862
Ok, I will try. thanks.
0
 
LVL 13

Expert Comment

by:sm394
ID: 22803921
use that if you want to auto generate ID
------------------------------------
drop table #tmp

CREATE TABLE #tmp (ID INT IDENTITY,Answer nvarchar(255))
INSERT INTO #tmp (Answer)
select   Answer1 AS Answer
from yourTable
where  ID=1
UNION
select   Answer2 AS Answer
from yourTable
where  ID=1
UNION
select   Answer3 AS Answer
from yourTable
where  ID=1

select * from #tmp
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 13

Expert Comment

by:sm394
ID: 22803931
move the
drop table #tmp
at the end or if you going to use StoredProcedure then you don't need it
0
 

Author Closing Comment

by:bolenka
ID: 31509953
Thanks so much. I have been pulling my hair out. Its crazy something so simple can bring such relief!!!! Thanks. great job.
0
 

Author Comment

by:bolenka
ID: 22803997
actually I am using a user defined function, but how do I exclude null values from the results???
0
 
LVL 13

Expert Comment

by:sm394
ID: 22807134
i can see postings for your questions here
http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_23847320.html
have you resolved the issue
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now