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
252 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 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
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

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

728 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