?
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
Medium Priority
?
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 13

Accepted Solution

by:
sm394 earned 2000 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
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
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

PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

764 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