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
245 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
Comment Utility
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
Comment Utility
Ok, I will try. thanks.
0
 
LVL 13

Expert Comment

by:sm394
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 13

Expert Comment

by:sm394
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

763 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

13 Experts available now in Live!

Get 1:1 Help Now