470,641 Members | 1,693 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,641 developers. It's quick & easy.

Query to tranform rows to column

Hi

Your help for the following query will be highly apprecaited. I've
wasted alot of time on it. Data definition is at the bottom.

Thanks

-----------------------------------
Business need: User selects answers for few questions. Answers are
presented in the form of radio buttons.

Questions Table:

===================

QuestionID QuestionName

1 1.Rate your organization

2 10.Opportunity in your group

3 117.Effectiveness

----------------------------------------

Answers Table:

==============

AnswerId AnswerName QuestionID

1 1.Best 1

2 2.Average 1

3 3.Wrose 1

4 1.Hardly Any 2

5 2.not too much 2

6 3.Average 2

7 4.Great Deal 2

8 1.Strong 3

9 2.Minor 3

10 3.Nothing 3

--------------------------------------------------------



Lets say User ABC answered below:

For Question 1, she chose '2.Average'

For Question 2: she left blank

FOr Quesiton 3: she chose '2.Minor'

And for user XYZ:

For Question 1, she let blank

For Question 2: she left blank

FOr Quesiton 3: she chose '3.Nothing'

Then the Results table will look like this:

Results Table:

===============

Resultsid QuestionID AnswerId User

1 1 2 ABC

2 3 9 ABC

3 3 10 XYZ
---------------------------------------------------



Desired Output:

===============

User 1 10 117
--- -- --- ----

ABC 2 2

XYZ 3

Explanation:

--> Show ALL distinct users on rows

--> show ALL distinct QuestionName on the columns BUT only display the
number before '.'

--> Show the chosen answer (answername) for each user but only display
the number before '.'

-------------------------------------------------------------------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Questions]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)

drop table [dbo].[Questions]

GO

CREATE TABLE [dbo].[Questions] (

[QuestionId] [int] IDENTITY (1, 1) NOT NULL ,

[QuestionName] [nvarchar] (1000) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL

) ON [PRIMARY]

GO

-------------------------------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Answers]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

drop table [dbo].[Answers]

GO

CREATE TABLE [dbo].[Answers] (

[AnswerId] [int] IDENTITY (1, 1) NOT NULL ,

[AnswerName] [nvarchar] (150) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,

[QuestionId] [int] NULL

) ON [PRIMARY]

GO

----------------------------------

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Results]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)

drop table [dbo].[Results]

GO

CREATE TABLE [dbo].[Results] (

[ResultId] [int] IDENTITY (1, 1) NOT NULL ,

[QuestionId] [int] NULL ,

[AnswerId] [int] NULL ,

[UserId] [nchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL

) ON [PRIMARY]

GO

---------------------------------------------

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'1.Best' ,

1

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'2.Average' ,

1

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'3.Wrose' ,

1

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'1.Hardly Any' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'2.not too much' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'3.Average' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'4.Great Deal' ,

2

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'1.Strong' ,

3

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'2.Minor' ,

3

)

go

INSERT Answers (

AnswerName ,

QuestionId

)

VALUES (

'3.Nothing' ,

3

)

go

-----------------------------

INSERT Questions (

QuestionName

)

VALUES (

'1.Rate your organization'

)

go



INSERT Questions (

QuestionName

)

VALUES (

'10.Opportunity in your group'
)

go

INSERT Questions (

QuestionName

)

VALUES (

'117.Effectiveness'

)

go

------------------------------------

INSERT Results (

QuestionId ,

AnswerId ,

UserId

)

VALUES (

1 ,

2 ,

'ABC'

)

go

INSERT Results (

QuestionId ,

AnswerId ,

UserId

)

VALUES (

3 ,

9 ,

'ABC'

)

go

INSERT Results (

QuestionId ,

AnswerId ,

UserId

)

VALUES (

3 ,

10 ,

'XYZ'

)

go

Jul 23 '05 #1
2 1458
I could think of two ways of doing it:

select users.userid,
(select answerid from results where results.userid =
users.userid and questionid = 1) as answer1,
(select answerid from results where results.userid =
users.userid and questionid = 2) as answer2,
....
from (select distinct userid from results) as users
order by users.userid

or

select users.userid,
answer1.answerid as answer1,
answer2.answerid as answer2,
....
from (select distinct userid from results) as users
left outer join results answer1
on users.userid = answer1.userid
and answer1.answerid = 1
left outer join results answer2
on users.userid = answer2.userid
and answer1.answerid = 2
....
order by users.userid

--
David Rowland
Do you know what people are doing on your database server?
Find out with dbmonitor!
http://dbmonitor.tripod.com

Jul 23 '05 #2
[posted and mailed, please reply in news]

(mu*****@hotmail.com) writes:
Your help for the following query will be highly apprecaited. I've
wasted alot of time on it. Data definition is at the bottom.
...
Desired Output:
===============

User 1 10 117
--- -- --- ----
ABC 2 2
XYZ 3

SQL is not really well suited for type query, as the columns depend
on the data. You don't say so, but I assume that there can be
any number of questions. The usual recommendation for this sort of
crosstab queries is RAC, see http://www.rac4sql.net.

But since you provided scripts and tables, and everything else I usually
nag about, I felt compelled to provide a solution. This uses dynamic SQL.
For more information on dynamic SQL, see
http://www.sommarskog.se/dynamic_sql.html.
DECLARE @selectlist varchar(8000),
@selfjoin varchar(8000),
@alias varchar(10),
@elias varchar(10),
@idstr varchar(10),
@qnostr varchar(10)

-- Map id:s to the numbers before the dot.
CREATE TABLE #idmap(id int NOT NULL PRIMARY KEY,
qno smallint NOT NULL UNIQUE)

-- Help table to get all distinct users that appear.
CREATE TABLE #users (userid nchar(40) NOT NULL PRIMARY KEY)

-- Maps answers ids to numbers before the dot.
CREATE TABLE #answermap(id int NOT NULL PRIMARY KEY,
ansno smallint NOT NULL)

-- Fill Idmap
INSERT #idmap(id, qno)
SELECT QuestionId,
convert(smallint,
substring(QuestionName, 1, charindex('.', QuestionName) - 1))
FROM Questions

-- Fill answermap
INSERT #answermap(id, ansno)
SELECT AnswerId,
convert(smallint,
substring(AnswerName, 1, charindex('.', AnswerName) - 1))
FROM Answers

-- And get all users.
INSERT #users (userid)
SELECT DISTINCT UserId FROM Results

-- We now start to build a dynamic SQL statement. We build in two parts:
-- the SELECT list and the FROM clause.
SELECT @selectlist = 'SELECT u.userid ',
@selfjoin = 'FROM #users u '

-- We have to iterate of the #idmap table to build it. For the query
-- we need the id as a string, and the question number must be in [],
-- since it will be used as an identifier.
DECLARE build_cur INSENSITIVE CURSOR FOR
SELECT ltrim(str(id)), '[' + ltrim(str(qno)) + ']'
FROM #idmap
ORDER BY qno
OPEN build_cur

WHILE 1 = 1
BEGIN
FETCH build_cur INTO @idstr, @qnostr
IF @@fetch_status <> 0
BREAK

-- We will need two alias for the Results and #answermap table,
-- which will appear once for each question.
SELECT @alias = 'a' + @idstr, @elias = 'e' + @idstr

-- Add this question's answers to the SELECT list.
SELECT @selectlist = @selectlist + ', ' + @qnostr + ' = ' +
@elias + '.ansno '

-- And augment the FROM clause with this question. We left-join to
-- Results, since answer may be NULL. We need to look up answer in
-- the answer-map to get the real id.
SELECT @selfjoin = @selfjoin + char(10) +
'LEFT JOIN (Results ' + @alias + ' JOIN #answermap ' + @elias +
' ON ' + @alias + '.AnswerId = ' + @elias + '.id) ' +
' ON u.userid = ' + @alias + '.UserId AND ' +
@alias + '.QuestionId = ' + @idstr
END
DEALLOCATE build_cur

-- Debug prints that show the actual query
PRINT @selectlist
PRINT @selfjoin

-- Run the beast!
EXEC (@selectlist + @selfjoin + ' ORDER BY u.userid')
go
DROP TABLE #users, #idmap, #answermap

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Lauren Quantrell | last post: by
3 posts views Thread by Matthew Kramer | last post: by
6 posts views Thread by Rory Campbell-Lange | last post: by
2 posts views Thread by Nenad Markovic | last post: by
1 post views Thread by Xeth Waxman | last post: by
???
1 post views Thread by Stoney L | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.