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
251 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 DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

734 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