Making a T-SQL Query

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

R_ID Name Question Response
- -- --
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

Make a textbox invisible based on another value

I cannot figure out how to make a textbox or a row in a table visible based
on another boolean variable. I don't want it to be based on the user
toggling or collapsing something - I simply want it to display or hide based
on a field in the dataset. Thanks!Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to refer
to the value of another textbox.
Note: expressions that refer to report items can only refer to values of
peer report items (those in the same grouping scope) or report items in any
containing (ancestor) grouping scope.
See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Wrench" <rwrench@.icsecurity.com> wrote in message
news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> I cannot figure out how to make a textbox or a row in a table visible
based
> on another boolean variable. I don't want it to be based on the user
> toggling or collapsing something - I simply want it to display or hide
based
> on a field in the dataset. Thanks!
>|||Thanks for pointing out the ReportItems collection (that answered another
question) however, I'm still confused about Textbox visibility.
I have a boolean field in the database named OtherCharges. I want a textbox
to display only if this value is true - How can I do this? - To me, there
should be a Visible property, instead, there is a Hidden and ToggleItem
property which are used so that the user can collapse / expand data - a la
Crystal - I don't want to do that.
Regards, Richard
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:eKefMYXXEHA.3012@.tk2msftngp13.phx.gbl...
> Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to refer
> to the value of another textbox.
> Note: expressions that refer to report items can only refer to values of
> peer report items (those in the same grouping scope) or report items in
any
> containing (ancestor) grouping scope.
> See also:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> > I cannot figure out how to make a textbox or a row in a table visible
> based
> > on another boolean variable. I don't want it to be based on the user
> > toggling or collapsing something - I simply want it to display or hide
> based
> > on a field in the dataset. Thanks!
> >
> >
>|||The textbox has a Visibility.Hidden property in the VS properties window
(hit F4 if it is not visible). For the Hidden property you would write this
expression: =(Fields!OtherCharges.Value = False)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Richard Wrench" <rwrench@.icsecurity.com> wrote in message
news:uzMqAJsXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> Thanks for pointing out the ReportItems collection (that answered another
> question) however, I'm still confused about Textbox visibility.
> I have a boolean field in the database named OtherCharges. I want a
textbox
> to display only if this value is true - How can I do this? - To me, there
> should be a Visible property, instead, there is a Hidden and ToggleItem
> property which are used so that the user can collapse / expand data - a la
> Crystal - I don't want to do that.
> Regards, Richard
>
>
>
> "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> news:eKefMYXXEHA.3012@.tk2msftngp13.phx.gbl...
> > Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to
refer
> > to the value of another textbox.
> > Note: expressions that refer to report items can only refer to values of
> > peer report items (those in the same grouping scope) or report items in
> any
> > containing (ancestor) grouping scope.
> > See also:
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> > news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> > > I cannot figure out how to make a textbox or a row in a table visible
> > based
> > > on another boolean variable. I don't want it to be based on the user
> > > toggling or collapsing something - I simply want it to display or hide
> > based
> > > on a field in the dataset. Thanks!
> > >
> > >
> >
> >
>|||Perfect ! Thanks ...
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:OBvdeGtXEHA.2500@.TK2MSFTNGP09.phx.gbl...
> The textbox has a Visibility.Hidden property in the VS properties window
> (hit F4 if it is not visible). For the Hidden property you would write
this
> expression: =(Fields!OtherCharges.Value = False)
> --
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> news:uzMqAJsXEHA.3716@.TK2MSFTNGP11.phx.gbl...
> > Thanks for pointing out the ReportItems collection (that answered
another
> > question) however, I'm still confused about Textbox visibility.
> >
> > I have a boolean field in the database named OtherCharges. I want a
> textbox
> > to display only if this value is true - How can I do this? - To me,
there
> > should be a Visible property, instead, there is a Hidden and ToggleItem
> > property which are used so that the user can collapse / expand data - a
la
> > Crystal - I don't want to do that.
> >
> > Regards, Richard
> >
> >
> >
> >
> >
> >
> > "Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
> > news:eKefMYXXEHA.3012@.tk2msftngp13.phx.gbl...
> > > Use the ReportItems collection (e.g. =ReportItems!textbox1.Value) to
> refer
> > > to the value of another textbox.
> > > Note: expressions that refer to report items can only refer to values
of
> > > peer report items (those in the same grouping scope) or report items
in
> > any
> > > containing (ancestor) grouping scope.
> > > See also:
> > >
> >
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_7ilv.asp
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > >
> > > "Richard Wrench" <rwrench@.icsecurity.com> wrote in message
> > > news:%23eQA%23DXXEHA.556@.tk2msftngp13.phx.gbl...
> > > > I cannot figure out how to make a textbox or a row in a table
visible
> > > based
> > > > on another boolean variable. I don't want it to be based on the
user
> > > > toggling or collapsing something - I simply want it to display or
hide
> > > based
> > > > on a field in the dataset. Thanks!
> > > >
> > > >
> > >
> > >
> >
> >
>