I have a table like below(bolds are field names)
R_IdNameQ1Q2Q3Q4Q5
M001Mikeabcde
J001 Johnabcde
P001 Peterabcde
I want results based on above table as below. The columns (Q1 to Q5) are put as Question_id for each user like M001 and Question (a, b ,c,d,e ) as column Question exactly as below.
Could anyone help me please writing the script to achieve below from above table.
Result
R_idNameQuestion_idQuestion
M001MikeQ1a
M001MikeQ2b
M001MikeQ3c
M001MikeQ4d
M001MikeQ5e
J001JohnQ1a
J001JohnQ2b
J001JohnQ3c
J001JohnQ4d
J001JohnQ5e
........ ....... ....... ....
Thanks all
If you use sql server 2005,
Code Snippet
Create Table #data (
[R_Id] Varchar(100) ,
[Name] Varchar(100) ,
[Q1] Varchar(100) ,
[Q2] Varchar(100) ,
[Q3] Varchar(100) ,
[Q4] Varchar(100) ,
[Q5] Varchar(100)
);
Insert Into #data Values('M001','Mike','a','b','c','d','e');
Insert Into #data Values('J001','John','a','b','c','d','e');
Insert Into #data Values('P001','Peter','a','b','c','d','e');
Select
R_Id
,Name
,Question_id
,Question
From
#Data
UNPIVOT
(
Question For Question_idin
([Q1],[Q2],[Q3],[Q4],[Q5])
) UPVT
|||Assuming you are using SQL 2005, you need to use the UNPIVOT operator.
The code will be something like this:
Code Snippet
DECLARE @.MyTable table
( R_Id varchar(10),
Name varchar(20),
Q1 char(1),
Q2 char(1),
Q3 char(1),
Q4 char(1),
Q5 char(1)
)
INSERT INTO @.MyTable VALUES ( 'M001', 'Mike', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'J001', 'John', 'a', 'b', 'c', 'd', 'e' )
INSERT INTO @.MyTable VALUES ( 'P001', 'Peter', 'a', 'b', 'c', 'd', 'e' )
SELECT
R_ID,
Name,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt
- -- --
M001 Mike Q1 a
M001 Mike Q2 b
M001 Mike Q3 c
M001 Mike Q4 d
M001 Mike Q5 e
J001 John Q1 a
J001 John Q2 b
J001 John Q3 c
J001 John Q4 d
J001 John Q5 e
P001 Peter Q1 a
P001 Peter Q2 b
P001 Peter Q3 c
P001 Peter Q4 d
P001 Peter Q5 e |||
If you use sql server 2000
Code Snippet
Create Table #data (
[R_Id] Varchar(100) ,
[Name] Varchar(100) ,
[Q1] Varchar(100) ,
[Q2] Varchar(100) ,
[Q3] Varchar(100) ,
[Q4] Varchar(100) ,
[Q5] Varchar(100)
);
Insert Into #data Values('M001','Mike','a','b','c','d','e');
Insert Into #data Values('J001','John','a','b','c','d','e');
Insert Into #data Values('P001','Peter','a','b','c','d','e');
Select R_Id,Name,'Q1' Question_id,[Q1] Question From #Data
Union All
Select R_Id,Name,'Q2' Question_id,[Q2] Question From #Data
Union All
Select R_Id,Name,'Q3' Question_id,[Q3] Question From #Data
Union All
Select R_Id,Name,'Q4' Question_id,[Q4] Question From #Data
Union All
Select R_Id,Name,'Q5' Question_id,[Q5] Question From #Data
|||
Arnie, Missed column...
SELECT
R_ID,
Name,
Response,
Question
FROM @.MyTable
UNPIVOT
( Question FOR Response
IN ( Q1, Q2, Q3, Q4, Q5 )
) unPvt
No comments:
Post a Comment